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

2013/8/7 Igor Neyman <ineyman@perceptron.com>:
>
>
> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
slapo@centrum.sk
> Sent: Wednesday, August 07, 2013 8:43 AM
> To: Pavel Stehule
> Cc: pgsql-performance@postgresql.org
> Subject: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.
>
> Good day,
>
> I have included a link to the result of EXPLAIN ANALYZE. It's this one:
> https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h
>
> Here's a link to Depesz's explain (if links to the site are okay):
> http://explain.depesz.com/s/gCk
>
> I have just tried setting geqo_threshold, join_collapse_limit and from_collapse_limit to 16, but it yielded no
improvement.
> Changing those three parameters to 32 did speed up the query from about 3.3 seconds to about a second (give or take
50ms), which is a pretty good improvement, but not quite there, as I'm looking to bring it down to about 300 ms if
possible.Changing those three settings to 48 yielded no improvements over 32. 
> Is there possibly something something else to tweak there?
>
> Here's EXPLAIN ANALYZE output when the three settings have been set to 32:
> http://explain.depesz.com/s/cj2
>
> Thank you.
>
> Peter Slapansky
>
> -----
>
> Your last explain analyze (with 3 settings set to 32)  shows query duration 10ms, not 1sec.
> Am I wrong?

I afraid so 1 sec is planning time :( .. So execution is fast, but
planning is expensive and relatively slow .. maybe prepared statements
can helps in this case.

Regards

Pavel

>
> Regards,
> Igor Neyman
>