Re: Different plan for very similar queries

On 05/31/15 18:22, Tom Lane wrote:
> Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
>> On 05/31/15 13:00, Peter J. Holzer wrote:
>>> (There was no analyze on facttable_stat_fta4 (automatic or manual) on
>>> facttable_stat_fta4 between those two tests, so the statistics on
>>> facttable_stat_fta4 shouldn't have changed - only those for term.)
>
>> So maybe there was autoanalyze, because otherwise it really should be
>> the same in both plans ...
>
> No, because that's the inside of a nestloop with significantly different
> outer-side rowcount estimates. The first case gets a benefit from the
> expectation that it will be re-executed many times (see the impact of
> loop_count on cost_index).

Meh, I got confused by the plan a bit - I thought there's a problem in
the outer path (e.g. change of row count). But actually this is the path
scanning the 'term' table, so the change is expected there.

The fact that the index scan cost 'suddenly' grows from 386k to 2M is
confusing at first, but yeah - it's caused by the 'averaging' in
cost_index() depending on loop_count.

But I think this does not really change the problem with eliminating
inner paths solely on the basis of total cost - in fact it probably
makes it slightly worse, because the cost also depends on estimates in
the outer path (while the bitmapscan does not).

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services