Обсуждение: Nested Loop "Killer" on 8.1
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.
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
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;
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,
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
Dave, > Is there further optimizations we can do to change the plan? > Is this perhaps addressed in a later release? Given the left joins, a later release might help; I know we did a lot to improve left join plans in 8.3. It would be worth testing if you can test an upgrade easily. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com
On Wed, Jun 24, 2009 at 1:43 PM, Dave North<DNorth@signiant.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
On 06/25/2009 04:36 PM, Greg Stark wrote:
Any reason why "like" with a constant string without % or _ is not optimized to = today?
Cheers,
mark
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
Any reason why "like" with a constant string without % or _ is not optimized to = today?
Cheers,
mark
-- Mark Mielke <mark@mielke.cc>
Greg Stark <gsstark@mit.edu> writes:
> On Wed, Jun 24, 2009 at 1:43 PM, Dave North<DNorth@signiant.com> wrote:
> 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.
Uh, it appears to me the string *does* contain _ characters; perhaps the
OP has neglected to escape those?
The planner does know enough to estimate LIKE with a fixed pattern as
being equivalent to =. I think it knew that even back in 8.1, but am
too lazy to look right now.
regards, tom lane
On Thu, Jun 25, 2009 at 10:05 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > > Uh, it appears to me the string *does* contain _ characters; perhaps the > OP has neglected to escape those? Sigh. Indeed. -- greg http://mit.edu/~gsstark/resume.pdf
Greg/Tom/Josh,
Thanks for your comments about this problem...very much
appreciated. We have resolve the issue by re-doing the query partly
based on your advice and partly just spending more time in analysis.
There's one oddball thing we turned up which I'm including below in the
full series of steps we did to optimize things around the "explain"
functionality.
1) The original query (89 rows returned) with an EXPLAIN ANALYZE takes
over 300 secs. Without the explain analyze, it runs in 45 seconds.
With nested loops disabled (and hence forcing a merge), it completes in
under 1 second.
The outstanding question here is why does the explain analyze take
(quite a bit) longer than just executing the query?
2) Removing the LEFT JOIN (89 rows returned)
- lowered query execution time to 37 secs
3) Changing the 3 occurrences of (prop_key LIKE 'string...') to =
- row estimate improved from 1 to 286
- query execution time still at 37 secs
4) Adding a DISTINCT to the IN subquery on
- records returned in subquery changes from 2194 to 112.
- ... web_user.web_user_id IN (SELECT DISTINCT web_user_id
- query execution time falls to 1 sec.
We then ran a totally unscientific test (unscientific because this was
on a different machine, different OS, etc.) just to see if there was any
difference between newer versions of Postgres and that which is bundled
with the application.
Using 8.3 on a Windows desktop
- original query executes in 7 secs
- improved query executes in 6 secs
So it seems there may well be some changes in newer versions which we
can take advantage of. More fuel to look into upgrading the embedded
database version ;)
Again, thanks all for the input.
Regards
Dave
> -----Original Message-----
> From: gsstark@gmail.com [mailto:gsstark@gmail.com] On Behalf
> Of Greg Stark
> Sent: June 25, 2009 5:30 PM
> To: Tom Lane
> Cc: Dave North; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Nested Loop "Killer" on 8.1
>
> On Thu, Jun 25, 2009 at 10:05 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> >
> > Uh, it appears to me the string *does* contain _
> characters; perhaps
> > the OP has neglected to escape those?
>
> Sigh. Indeed.
>
> --
> greg
> http://mit.edu/~gsstark/resume.pdf
>
"Dave North" <DNorth@signiant.com> writes:
> The outstanding question here is why does the explain analyze take
> (quite a bit) longer than just executing the query?
EXPLAIN ANALYZE has nontrivial measurement overhead, especially on
platforms with slow gettimeofday(). Old/cheap PC hardware, in particular,
tends to suck in this respect.
regards, tom lane