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