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@xxxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
