Nested Loop "Killer" on 8.1

["Dave North" <DNorth@xxxxxxxxxxxx>]

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  (execution time 95secs)

 

"Nested loops off" plan: 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@xxxxxxxxxxxx