From: slapo@centrum.sk
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Igor Neyman <ineyman@perceptron.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: [PERFORM] RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.
Date: Thu, 08 Aug 2013 08:59:42 +0200
Message-ID: <20130808085942.3D228938@centrum.sk> (raw)
In-Reply-To: <6240.1375890794@sss.pgh.pa.us>
References: <20130802154316.59167BB5@centrum.sk>
<CAFj8pRAc-sk8QigQJJn+9MgLJmxU7pjH90Eqd47tAF_1HsGOXQ@mail.gmail.com>
<20130807144239.654353D3@centrum.sk>
<A76B25F2823E954C9E45E32FA49D70EC1BBF38D9@mail.corp.perceptron.com>
<20130807164250.26C21834@centrum.sk>
<A76B25F2823E954C9E45E32FA49D70EC1BBF393A@mail.corp.perceptron.com>
<20130807173349.E2F843A7@centrum.sk>
<6240.1375890794@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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@sss.pgh.pa.us>
> Komu: <slapo@centrum.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@postgresql.org"
<slapo@centrum.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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: slapo@centrum.sk, tgl@sss.pgh.pa.us, ineyman@perceptron.com, pavel.stehule@gmail.com
Subject: Re: Re: [PERFORM] RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.
In-Reply-To: <20130808085942.3D228938@centrum.sk>
* 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