On 26 July 2018 at 04:50, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > My guess is this is the root cause - the estimated number of rows is much > higher than in practice (3377106 vs. 23040), so at the end the seqscan is > considered to be slightly cheaper and wins. But the actual row count is > ~150x lower, making the bitmap index scan way faster.
Isn't the 23040 just the totalpages * 10 per `return totalpages * 10;` in bringetbitmap()? The BRIN index costing was changed quite dramatically in [1] which first appears in pg10. Previous to that patch BRIN assumed it would touch total_pages * pred_selectivity blocks in the bitmap scan. That worked well when the table happened to be perfectly ordered by the column of the BRIN index, but was pretty broken when the order was random. That lead to BRIN indexes being used when they really shouldn't have been. The patch tried to fix that by using what information it could. That was basically the correlation statistics from pg_statistic. It appeared that the patch was producing more realistic plans than unpatched, so we went with it, but it's definitely not perfect; what statistics are? It would be quite interesting to know the result of: select stakind3 from pg_Statistic where starelid = 'schema0_lab.data_table'::regclass; I also see the estimated costs of either plan are very close, so the chosen plan may well be quite susceptible to changing after different ANALYZE runs on the table. [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7e534adcdc70866e7be74d626b0ed067c890a251 -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
