On Wed, Jun 24, 2009 at 1:43 PM, Dave North<DNorth@signiant.com> 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
