Re: Sub-optimal plan for a paginated query on a view with another view inside of it.

Hello

please, send result of EXPLAIN ANALYZE

please, use a 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 <slapo(at)centrum(dot)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
>
> This is the query plan:
> https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h (plain text)
> https://app.box.com/s/jzxiuuxoyj28q4q8rzxr (graphical output)
>
> These are the views:
> https://app.box.com/s/uibzidsazwv3eeauovuk (paginated view)
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance