On Sat, Aug 10, 2013 at 10:32 AM, Timothy Kane <tim.kane@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
