On Wed, Jun 24, 2009 at 1:43 PM, Dave North<DNorth@xxxxxxxxxxxx> wrote: > Essentially, we're seeing a query plan that is taking 95 secs with a nested > loop execution plan and 1 sec with a merge join plan. We've tried > increasing the default_statistics_target to 1000 and re-analyzed but the > same query plan is returned. If we then force nested loops off (set > enable_nestloop=false), the optimizer chooses the better plan and execution > is under 1 second. > > "Default" explain plan: http://explain.depesz.com/s/a3 ; (execution time > 95secs) > > "Nested loops off" plan: http://explain.depesz.com/s/JV ;(execution time ~ > 1sec) The planner is coming up with a bad estimate for the number of rows matching this filter: Filter: ((prop_key)::text ~~ 'location_node_directory_outbox'::text) Which is coming from this condition: > AND > web_user_property_directory_outbox.prop_key like > 'location_node_directory_outbox' Why use "like" for a constant string with no % or _ characters? If you used = the planner might be able to come up with a better estimate. That said I suspect Dave's right that your best course of action would be to update to 8.3 or wait a couple weeks and update to 8.4 when it comes out. Regardless you *really* want to update your 8.1.8 install to the latest bug-fix release (currently 8.1.17). That's not an upgrade and won't need a dump/reload. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
