From: Markus Schulz <msc@antzsystem.de>
To: pgsql-performance@postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: problem with from_collapse_limit and joined views
Date: Sat, 4 Dec 2010 19:46:21 +0100
Message-ID: <201012041946.21722@Mail-Followup-To> (raw)
In-Reply-To: <24137.1291481998@sss.pgh.pa.us>
References: <4CFA09E2020000250003829C@gw.wicourts.gov>
<24137.1291481998@sss.pgh.pa.us>
Am Samstag 04 Dezember 2010 schrieb Tom Lane:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
...
> > One option would be to create a different user for running queries
> > which read from complex views such as this.
>
> If you don't want to change the collapse limits, the only other
> option is to restructure this specific query so that its syntactic
> structure is closer to the ideal join order. Look at the plan you
> get in the good-performing case and re-order the join syntax to look
> like that.
no that's not working in this case.
view1 and view2 are written with explicit joins and no better join was
possible. Each view works perfect standalone.
In my above example i have rewritten view1 without explicit joins only
for testing purpose. Without explicit joins i can gather the optimal
query plan from a slightly higher from_collapse_limit (see workaround 2
from my initial posting).
If both views using explicit joins the from_collapse_limit is useless
(only join_collapse_limit usable).
The problem exists only for "view1 JOIN view2" and that pgsql don't
"see" that an element of view2 contains an index-access for reducing the
data from view1. Only if he can break the complete join of both views
into one query-plan he can "see" this. But for this i must raise the
limits.
Looks like some improvement to the geco optimizer was needed here ;)
regards
msc
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: msc@antzsystem.de, tgl@sss.pgh.pa.us
Subject: Re: problem with from_collapse_limit and joined views
In-Reply-To: <201012041946.21722@Mail-Followup-To>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox