Thread: Re: problem with from_collapse_limit and joined views

Markus Schulz  11/24/10 1:02 PM >>>

> if i set "from_collapse_limit" (to merge the views) and
> join_collapse_limit (to explode the explicit joins) high enough
> (approx 32), all is fine (good performance). But other queries are
> really slow in our environment (therefore it's no option to raise
> the join_collapse_limit to a higher value)
>
> With defaults (8) for both, the performance is ugly

One option would be to create a different user for running queries
which read from complex views such as this.

postgres=# create user bob;
CREATE ROLE
postgres=# alter user bob set from_collapse_limit = 40;
ALTER ROLE
postgres=# alter user bob set join_collapse_limit = 40;
ALTER ROLE

Log in as bob, and your queries should run fine.

Nothing leapt out at me as an issue in your postgresql.conf except:

max_prepared_transactions = 20

Do you actually use prepared transactions?  (Sometimes people confuse
this with prepared statements, which are a completely different
feature.)

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Markus Schulz  11/24/10 1:02 PM >>>
>> if i set "from_collapse_limit" (to merge the views) and
>> join_collapse_limit (to explode the explicit joins) high enough
>> (approx 32), all is fine (good performance). But other queries are
>> really slow in our environment (therefore it's no option to raise
>> the join_collapse_limit to a higher value)
>>
>> With defaults (8) for both, the performance is ugly

> 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.

            regards, tom lane

Am Samstag 04 Dezember 2010 schrieb Kevin Grittner:
> One option would be to create a different user for running queries
> which read from complex views such as this.
>
> postgres=# create user bob;
> CREATE ROLE
> postgres=# alter user bob set from_collapse_limit = 40;
> ALTER ROLE
> postgres=# alter user bob set join_collapse_limit = 40;
> ALTER ROLE
>
> Log in as bob, and your queries should run fine.

thanks, that was really an option for us to use a different user for
creating the reports.

> Nothing leapt out at me as an issue in your postgresql.conf except:
>
> max_prepared_transactions = 20
>
> Do you actually use prepared transactions?  (Sometimes people confuse
> this with prepared statements, which are a completely different
> feature.)

yes, they are needed for our JPA-based j2ee application.

regards
msc

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