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

I was afraid of something worse but hoping for something better in terms of maintainability. At least now I have a good explanation. :-)
I just hope the embedded view use won't interfere too much.
 
Thanks everyone.
 
Regards,
 
Peter Slapansky
 
______________________________________________________________
> Od: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Komu: <slapo(at)centrum(dot)sk>
> Dátum: 07.08.2013 17:53
> Predmet: Re: [PERFORM] RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.
>
> CC: "Igor Neyman", "Pavel Stehule", "pgsql-performance(at)postgresql(dot)org"
<slapo(at)centrum(dot)sk> writes:
> "Total runtime: 9.313 ms" in pgAdmin
> "Total runtime: 9.363 ms" in psql.
> But timing after the query finished was 912.842 ms in psql.

Well, that's the downside of increasing join_collapse_limit and
from_collapse_limit: you might get a better plan, but it takes a lot
longer to get it because the planner is considering many more options.

If you're sufficiently desperate, you could consider rewriting the query
so that its JOIN structure matches the join order that the planner chooses
at the high collapse_limit settings.  Then you can reduce the limits back
down and it'll still find the same plan.  This tends to suck from a query
readability/maintainability standpoint though :-(.

The prepared-query approach might offer a solution too, if the good plan
isn't dependent on specific parameter values.

regards, tom lane