Skip site navigation (1) Skip section navigation (2)

Nested Loop "Killer" on 8.1

From: "Dave North" <DNorth(at)signiant(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Nested Loop "Killer" on 8.1
Date: 2009-06-24 12:43:23
Message-ID: 35FABCF85D99464FB00BC5123DC2A70A08355B33@s228130hz1ew09.apptix-01.savvis.net (view raw or flat)
Thread:
Lists: pgsql-performance
Morning all,
    A colleague here tried to post this yesterday but it was stalled for
some reason.  Anyway, here's what we're seeing which hopefully someone
has some pointers for.
 
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
<http://explain.depesz.com/s/a3>   (execution time 95secs)
 
"Nested loops off" plan: http://explain.depesz.com/s/JV
<http://explain.depesz.com/s/JV>  (execution time ~ 1sec)
 
We're currently running 8.1.8 (yeah, we know it's old skool but it's
embedded as part of an application) so the real questions are:
 
Is there further optimizations we can do to change the plan?
Is this perhaps addressed in a later release?
 
Some postgresql.conf settings that might be useful:
 
effective_cache_size           511082
 shared_buffers                  30000
 work_mem                         4096
 random_page_cost                    4
 join_collapse_limit                 8
 
 
and of course, the query in question that generates the plan:
 
SELECT web_user_type,
  web_user.web_user_id as id,
  cast(web_user_property_node.prop_val as numeric) as node_id ,
  node_name,
  last_name || ', ' || first_name  as name,
  web_user_property_directory_inbox.prop_val as
directory_location_inbox,
  web_user_property_directory_outbox.prop_val as
directory_location_outbox,
  username,
  first_name,
  last_name,
  email
FROM
 web_user LEFT JOIN web_user_property as
web_user_property_directory_outbox ON web_user.web_user_id =
web_user_property_directory_outbox.web_user_id AND 
   web_user_property_directory_outbox.prop_key like
'location_node_directory_outbox',  web_user_property,  web_user_property
as web_user_property_directory_inbox,
 web_user_property as web_user_property_node,  node WHERE
web_user.web_user_id = web_user_property_directory_inbox.web_user_id AND
web_user.web_user_id = web_user_property.web_user_id  AND
web_user_property.prop_key = 'location_node_enabled' AND
web_user_property.prop_val = 'true' AND
web_user_property_directory_inbox.prop_key like
'location_node_directory_inbox' AND  web_user.web_user_id =
web_user_property_node.web_user_id AND  web_user_property_node.prop_key
like 'location_node_id' AND  web_user_property_node.prop_val =
node.node_id AND  (first_name ilike '%' OR last_name ilike '%' OR 
   last_name || ',' || first_name ilike '%') AND  node.node_id  IN (
SELECT node_id FROM node_execute 
                      WHERE acl_web_user_id = 249) AND
web_user.web_user_id IN ( SELECT web_user_id FROM web_user_read 
                             WHERE acl_web_user_id  = 249  OR 
   web_user_id IN ( SELECT member_id FROM web_user_grp_member 
                     WHERE web_user_id IN( SELECT acl_web_user_id 
                              FROM web_user_read 
                                             WHERE web_user_id IN
(SELECT web_user_id FROM web_user_grp_member  
                                                WHERE member_id =
249))))  ORDER BY name;

 
Thanks in advance
 
Dave
 
Dave North
dnorth(at)signiant(dot)com

Responses

pgsql-performance by date

Next:From: Albe LaurenzDate: 2009-06-24 13:02:57
Subject: Re: Implications of having large number of users
Previous:From: Robert HaasDate: 2009-06-24 11:30:42
Subject: Re: Implications of having large number of users

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group