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

2013/8/7 <slapo(at)centrum(dot)sk>:
> You're right, it does... but it's quite odd, because I re-ran the
> explain-analyze statement and got the same results.
>
> Still, the query now runs for about a second as mentioned before, so it's
> almost like something's missing from the explain, but I'm certain I copied
> it all.

what is time of EXPLAIN only ?

Pavel

>
>
>
> I did this via pgadmin, but that shouldn't matter, should it?
>
>
>
> Thank you,
>
>
>
> Peter Slapansky
>
> ______________________________________________________________
>> Od: Igor Neyman <ineyman(at)perceptron(dot)com>
>> Komu: "slapo(at)centrum(dot)sk" <slapo(at)centrum(dot)sk>, Pavel Stehule
>> <pavel(dot)stehule(at)gmail(dot)com>
>> Dátum: 07.08.2013 15:47
>> Predmet: RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated
>> query on a view with another view inside of it.
>>
>
>> CC: "pgsql-performance(at)postgresql(dot)org"
>
> Your last explain analyze (with 3 settings set to 32) shows query duration
> 10ms, not 1sec.
> Am I wrong?
>
> Regards,
> Igor Neyman
>
>
>
> ______________________________________________________________
>
>
> From: pgsql-performance-owner(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of
> slapo(at)centrum(dot)sk
> Sent: Wednesday, August 07, 2013 8:43 AM
> To: Pavel Stehule
> Cc: pgsql-performance(at)postgresql(dot)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 50 ms), 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
>