Home > mailing lists

Nested Loop "Killer" on 8.1 - Mailing list pgsql-performance

From	Dave North
Subject	Nested Loop "Killer" on 8.1
Date	June 24, 2009 12:49:29
Msg-id	35FABCF85D99464FB00BC5123DC2A70A08355B33@s228130hz1ew09.apptix-01.savvis.net Whole thread Raw
Responses	Re: Nested Loop "Killer" on 8.1 Re: Nested Loop "Killer" on 8.1
List	pgsql-performance

Tree view

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@signiant.com

pgsql-performance by date:

From: Robert Haas
Date: 24 June 2009, 11:30:52
Subject: Re: Implications of having large number of users

From: "Albe Laurenz"
Date: 24 June 2009, 13:03:14
Subject: Re: Implications of having large number of users

Nested Loop "Killer" on 8.1 - Mailing list pgsql-performance

Previous

Next