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
nearlyempty, 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
numberof 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