Re: Convincing the query planner to play nice

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(dot)janes(at)gmail(dot)com> wrote:

>On Sat, Aug 10, 2013 at 10:32 AM, Timothy Kane <tim(dot)kane(at)gmail(dot)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