Re: Nested Loop "Killer" on 8.1

On Wed, Jun 24, 2009 at 1:43 PM, Dave North<DNorth(at)signiant(dot)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