On Thursday, December 20, 2012, Jeff Janes wrote:
On Thursday, December 20, 2012, Richard Neill wrote:
-> Bitmap Index Scan on tbl_tracker_exit_state_idx (cost=0.00..8.36 rows=151 width=0) (actual time=7.946..7.946 rows=20277 loops=1)
This is finding 100 times more rows than it thinks it will. If that could be fixed, surely this plan would not look as good. But then, it would probably just switch to another plan that is not the one you want, either.
I guess the issue here is that the histogram postgres uses to estimate the number of rows that will be found is based on visible rows, and it is correctly estimating the number of visible rows that will be found. And that is the relevant thing to pass up to a higher join for its estimation. But for estimating the number of blocks a given index scan will access, the right thing would be the number of tuples visited, not the number of them found to be visible. So that is where this plan goes systematically wrong.
I guess the correct thing would be for postgres to keep two histograms, one of all tuples and one of all visible tuples, and to produce different selectivity estimates for different purposes. But I don't see that change getting made. It is only meaningful in cases where there is a fundamental skew in distribution between visible tuples and invisible-but-as-yet-unvacuumed tuples.
I think that that fundamental skew is the source of both the underestimation of the bitmap scan cost, and overestimation of the partial index scan (although I can't get it to overestimate that be anywhere near the amount you were seeing).
I still think your best bet is to get rid of the partial index and trade the full one on (parcel_id_code) for one on (parcel_id_code,exit_state). I think that will be much less fragile than reindexing in a cron job.
Cheers,
Jeff