Good day,
I have included a link to the result of EXPLAIN ANALYZE. It's this one:
https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h
<https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h>
Here's a link to Depesz's explain (if links to the site are okay):
http://explain.depesz.com/s/gCk <http://explain.depesz.com/s/gCk>
I have just tried setting geqo_threshold, join_collapse_limit and
from_collapse_limit to 16, but it yielded no improvement.
Changing those three parameters to 32 did speed up the query from about 3.3
seconds to about a second (give or take 50 ms), which is a pretty good
improvement, but not quite there, as I'm looking to bring it down to about 300
ms if possible. Changing those three settings to 48 yielded no improvements
over 32.
Is there possibly something something else to tweak there?
Here's EXPLAIN ANALYZE output when the three settings have been set to 32:
http://explain.depesz.com/s/cj2 <http://explain.depesz.com/s/cj2>
Thank you.
Peter Slapansky
______________________________________________________________
Od: Pavel Stehule <pavel.steh...@gmail.com>
Komu: <sl...@centrum.sk>
Dátum: 06.08.2013 21:01
Predmet: Re: [PERFORM] Sub-optimal plan for a paginated query on a view with
another view inside of it.
CC: pgsql-performance@postgresql.org
Hello
please, send result of EXPLAIN ANALYZE
please, use a http://explain.depesz.com/ <http://explain.depesz.com/> for
saving a plan
there is a more than 8 joins - so try to set geqo_threshold to 16,
join_collapse_limit to 16, and from_collapse_limit to 16.
Regards
Pavel Stehule
2013/8/2 <sl...@centrum.sk>:
Good day,
I have a performance issue when JOINing a view within another view more than
once.
The query takes over three seconds to execute, which is too long in this case.
It's not a problem if the tables are nearly empty, but that isn't the case on
the production database.
I suspect the planner thinks it's better to first put together the v_address view and
JOIN it to the parcel table later on, but the function
"fx_get_user_tree_subordinates_by_id" should be JOINed to the parcel table
first, as it reduces the number of rows to less than 200 and any following JOINs would be
much faster.
I have also ran vacuum, reindex and analyze on the whole database, but it seems
to have had to effect.
Is there any way to nudge the planner toward that way of execution?
This is the query:
https://app.box.com/s/jzxiuuxoyj28q4q8rzxr
<https://app.box.com/s/jzxiuuxoyj28q4q8rzxr>
This is the query plan:
https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h
<https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h> (plain text)
https://app.box.com/s/jzxiuuxoyj28q4q8rzxr
<https://app.box.com/s/jzxiuuxoyj28q4q8rzxr> (graphical output)
These are the views:
https://app.box.com/s/uibzidsazwv3eeauovuk
<https://app.box.com/s/uibzidsazwv3eeauovuk> (paginated view)
https://app.box.com/s/v71vyexmdyl97m4f3m6u
<https://app.box.com/s/v71vyexmdyl97m4f3m6u> (used three times in the paginated
view).
Thank you.
Peter Slapansky
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
<http://www.postgresql.org/mailpref/pgsql-performance>
