Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> Given how remarkably quick the single-index scan is, I also wonder if
>> that index is fully cached while we had to read some of the other index
>> from kernel or SSD.
> Unfortunately, this doesn't actually hold water for the case which I ran
> into as this was across multiple repeated invocations, where both indexes
> were fully cached. It was simply much more expensive to scan the entire
> GIST index (which wasn't small) than to fetch and filter the records
> returned from the btree index.
Well, I think the main problem in the case you are describing is a bad
estimate of how much of the GIST index needs to be examined, which is
something that needs to be fixed in gistcostestimate or operator-specific
selectivity estimates, not in choose_bitmap_and. In Seamus' example it
seems that none of the rowcount estimates are unduly far off, so I don't
think he had an estimation failure of the same kind.
> I'm just wondering how we manage to not realize that scanning through
> gigabytes of index pages is going to be more expensive than running an
> operator comparison across 100k records.
IOW, almost certainly we *don't* realize that the query will involve
scanning through gigabytes of index pages. But btree indexes are much
simpler and easier to make that estimate for than GIST indexes are.
regards, tom lane