Re: Convincing the query planner to play nice

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