Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
> The "point_features" table is pretty dynamic and it's possible
> that the data changed between my 'explain analyze' statement in
> the first post in this thread. However since both of them
> show an estimate of "rows=948" and returned an actual of 917 I
> don't think that happened.
Yeah, I had considered the same explanation and rejected it for the same
reason. Also, the difference in estimated cost is significant (265.85
for the seqscan vs 172.17 for the bitmap scan) so it's hard to think
that a small change in stats --- so small as to not reflect in estimated
row count --- would change the estimate by that much.
[ thinks some more... ] Of course, what we have to remember is that the
planner is actually going to choose based on the ultimate join cost, not
on the subplan costs. The reason the seqscan survived initial
comparisons at all is that it has a cheaper startup cost (less time to
return the first tuple) than the bitmap scan, and this will be reflected
into a cheaper startup cost for the overall nestloop. The extra hundred
units of total cost would only reflect into the nestloop total cost ---
and there, they would be considered "down in the noise" compared to a
90k total estimate. So probably what happened is that the planner
preferred this plan on the basis that the total costs are the same to
within estimation error while the startup cost is definitely less.
In this explanation, the reason for the change in plans over time could
be a change in the statistics for the other table. Is "facets" more
dynamic than "point_features"?
regards, tom lane
