On Thu, Dec 19, 2024 at 1:39 PM Greg Sabino Mullane <htamf...@gmail.com> wrote: >> >> Why wouldn't it do an index (or, really, an index only) scan in this case > > > Well, it did do an index scan (and a bitmap scan is a pretty good solution > here), but as to why no indexonly scan, there is probably not enough > assurance that it won't have to hit the heap heavily anyway. Try doing a SET > enable_bitmapscan=0; and re-run with EXPLAIN ANALYZE. If you see a large > number of "Heap Fetches", that could be why. Vacuum the table and try again > after doing SET enable_bitmapscan=1; >
The table is freshly vacuumed. If I disable bitmap scans, it will do an index only scan, which performs better. For the bitmap heap scan, it says "Heap Blocks: exact=27393," whereas for the index only scan, it's "Heap Fetches: 27701." The row estimate is not good. The query estimates 317919 rows but there are only 27701. There is some correlation here; if end_on is null, start_on is a lot more likely to be recent, so maybe extended statistics would be useful here. - Jon
