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@xxxxxxxxx> wrote: >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
