Обсуждение: Convincing the query planner to play nice

Timothy Kane <tim.kane@gmail.com> writes:
> I seem to be having some grief with the 9.1.9 query planner favouring an index scan + merge join, over a sequential
scan+ hash join. 

I believe the reason it's preferring the merge join plan is that it thinks
the executor will be able to terminate the merge join early as a
consequence of the range of join keys in "addresses" being only a fraction
of the range of join keys in "users".  Notice that the total estimated
cost for the merge join is just a fraction of the full estimated cost of
the indexscan on "users"; the only way that's possible is if the indexscan
on "users" doesn't have to run through all of the table.  Probably, the
range of join keys is wider than the planner thinks and so the merge join
can't terminate early.  The fix therefore is to crank the stats target for
"addresses" up high enough that you get a reasonable value in pg_statistic
for the largest address_id value (look at the last histogram entry).

> Interestingly, on another instance of this same database running on postgres 8.3.8, the query planner correctly
choosesthe sequential scan method - having more sane cost estimates for the index scan method. 

I think the 8.3 planner didn't take this effect into account.  Or maybe it
did, but by chance the upper histogram value is closer to reality on the
older database.

            regards, tom lane

Ahh, thanks Tom.
I hadn't seen your email before I posted my own followup.

I guess the clustering approach managed to work around the need to mess with the statistics target.  I did previously
increasethe target to 1000 (from 100) for that field and had no impact, but this is an aspect of tuning I'm not so
familiarwith - I didn't consider pushing it all the way to 11. 


On 11 Aug 2013, at 00:28, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Timothy Kane <tim.kane@gmail.com> writes:
>> I seem to be having some grief with the 9.1.9 query planner favouring an index scan + merge join, over a sequential
scan+ hash join. 
>
> I believe the reason it's preferring the merge join plan is that it thinks
> the executor will be able to terminate the merge join early as a
> consequence of the range of join keys in "addresses" being only a fraction
> of the range of join keys in "users".  Notice that the total estimated
> cost for the merge join is just a fraction of the full estimated cost of
> the indexscan on "users"; the only way that's possible is if the indexscan
> on "users" doesn't have to run through all of the table.  Probably, the
> range of join keys is wider than the planner thinks and so the merge join
> can't terminate early.  The fix therefore is to crank the stats target for
> "addresses" up high enough that you get a reasonable value in pg_statistic
> for the largest address_id value (look at the last histogram entry).
>
>> Interestingly, on another instance of this same database running on postgres 8.3.8, the query planner correctly
choosesthe sequential scan method - having more sane cost estimates for the index scan method. 
>
> I think the 8.3 planner didn't take this effect into account.  Or maybe it
> did, but by chance the upper histogram value is closer to reality on the
> older database.
>
>             regards, tom lane

Tim Kane <tim.kane@gmail.com> writes:
> I guess the clustering approach managed to work around the need to mess with the statistics target.  I did previously
increasethe target to 1000 (from 100) for that field and had no impact, but this is an aspect of tuning I'm not so
familiarwith - I didn't consider pushing it all the way to 11. 

Yeah, I had actually started to write an email recommending that you dial
down effective_cache_size and increase random_page_cost, before I noticed
the discrepancy in the merge join cost and realized what was really going
on.

The question now is why you had those settings like that before, and
whether changing them back in the direction of the defaults might not be
pessimizing the behavior for other queries.  If you have a lot of RAM and
mostly-cached queries, the previous settings didn't sound unreasonable.

            regards, tom lane

Yep, the effective_cache_size was specifically because we have lots of RAM to play with, and want to ensure we are
cachingwherever possible. 
The reduced random_page_cost was primarily to allow for the fact we're using relatively fast disk (indexes are SSD,
tabledata on SAS drives), though I didn't fully appreciate how the combination of these settings can influence the
preferencetowards a sequential vs index scan. 

I think i'll stop tweaking for now, and see how it performs in the next few days.  I feel like I have a much better
handleon how the planner is pulling everything together. Cheers. 

Tim


On 11 Aug 2013, at 01:38, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Tim Kane <tim.kane@gmail.com> writes:
>> I guess the clustering approach managed to work around the need to mess with the statistics target.  I did
previouslyincrease the target to 1000 (from 100) for that field and had no impact, but this is an aspect of tuning I'm
notso familiar with - I didn't consider pushing it all the way to 11. 
>
> Yeah, I had actually started to write an email recommending that you dial
> down effective_cache_size and increase random_page_cost, before I noticed
> the discrepancy in the merge join cost and realized what was really going
> on.
>
> The question now is why you had those settings like that before, and
> whether changing them back in the direction of the defaults might not be
> pessimizing the behavior for other queries.  If you have a lot of RAM and
> mostly-cached queries, the previous settings didn't sound unreasonable.
>
>             regards, tom lane

On Sat, Aug 10, 2013 at 10:32 AM, Timothy Kane <tim.kane@gmail.com> wrote:
>
> Hi all,
>
> I seem to be having some grief with the 9.1.9 query planner favouring an
> index scan + merge join, over a sequential scan + hash join.
> Logically I would have considered the index+merge to be faster, as suggested
> by the explain output - but in practice, it is in fact slower by orders of
> magnitude.
>
> In my timings below, I've tried to reduce the impact of any OS or
> shared_buffer level caching (restarting postgres, and flushing OS cache
> between queries-).


Are you sure that that is the right thing to do?  It seems unlikely
that your production server is constantly executing your query from a
cold start.  Why test it that way?

>
> I've provided my settings as shown:
>
>
> =# show seq_page_cost;
>  seq_page_cost
> ---------------
>  1
> (1 row)
>
> Time: 0.355 ms
> =# show random_page_cost;
>  random_page_cost
> ------------------
>  2.2
> (1 row)

Given that you are testing your query from a cold start (and assuming
against odds that that is the correct thing to do), 2.2 is probably a
factor of 20 too small for this setting.

Cheers,

Jeff

On Sat, Aug 10, 2013 at 5:24 PM, Tim Kane <tim.kane@gmail.com> wrote:


>
> Again, just thinking out loud here..   In a scenario where specific
> clustering isn't an option...
> I wonder if the query planner should consider the physical
> distribution/ordering of values on disk, and use that as a factor when
> applying the random_page_cost in the QEP's?

It does do that, based on the "correlation" column in pg_stats.
However, because your original random_page_cost is already very close
to seq_page_cost, this adjustment doesn't have a huge effect in your
case.  I don't know how much of an effect it would have even then,
because of the range overlap issue that Tom mentions.

Cheers,

Jeff

Thanks Jeff. These queries in particular relate to a set of data that is
rebuilt on a periodic basis. For all intents and purposes, the data is
newly populated and unlikely to reside in cache - hence the need to
perform my tests under similar conditions.

It's probably better than I adjust the random_page_cost for that
particular session, and leave things be otherwise.


Cheers.



On 13/08/2013 17:27, "Jeff Janes" <jeff.janes@gmail.com> wrote:

>On Sat, Aug 10, 2013 at 10:32 AM, Timothy Kane <tim.kane@gmail.com> wrote:
>>
>> Hi all,
>>
>> I seem to be having some grief with the 9.1.9 query planner favouring an
>> index scan + merge join, over a sequential scan + hash join.
>> Logically I would have considered the index+merge to be faster, as
>>suggested
>> by the explain output - but in practice, it is in fact slower by orders
>>of
>> magnitude.
>>
>> In my timings below, I've tried to reduce the impact of any OS or
>> shared_buffer level caching (restarting postgres, and flushing OS cache
>> between queries-).
>
>
>Are you sure that that is the right thing to do?  It seems unlikely
>that your production server is constantly executing your query from a
>cold start.  Why test it that way?
>
>>
>> I've provided my settings as shown:
>>
>>
>> =# show seq_page_cost;
>>  seq_page_cost
>> ---------------
>>  1
>> (1 row)
>>
>> Time: 0.355 ms
>> =# show random_page_cost;
>>  random_page_cost
>> ------------------
>>  2.2
>> (1 row)
>
>Given that you are testing your query from a cold start (and assuming
>against odds that that is the correct thing to do), 2.2 is probably a
>factor of 20 too small for this setting.
>
>Cheers,
>
>Jeff