Обсуждение: subselects vs WITH in views
My assumption was that WITH acted just like subselects, but apparently they don't? Using WITH doesn't use the expected index. (the below also at: https://gist.github.com/joevandyk/839413fac7b3bdd32cb3/raw/cec015d16bed7f4e20ab0101b58ae74a1df1cdc2/gistfile1.txt create view promotion_details1 as ( select * from (select code from promotions)_ ); create view promotion_details2 as ( with info as (select code from promotions) select * from info ); explain analyze select * from promotion_details1 where code = 'slickdeals'; explain analyze select * from promotion_details2 where code = 'slickdeals'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on promotions (cost=72.54..6435.31 rows=3014 width=32) (actual time=0.122..0.196 rows=113 loops=1) Recheck Cond: (code = 'slickdeals'::citext) -> Bitmap Index Scan on promotions_code_idx (cost=0.00..71.79 rows=3014 width=0) (actual time=0.111..0.111 rows=113 loops=1) Index Cond: (code = 'slickdeals'::citext) Total runtime: 0.236 ms (5 rows) QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- CTE Scan on info (cost=15539.25..29102.81 rows=3014 width=32) (actual time=184.303..661.816 rows=113 loops=1) Filter: (code = 'slickdeals'::citext) Rows Removed by Filter: 602712 CTE info -> Seq Scan on promotions (cost=0.00..15539.25 rows=602825 width=32) (actual time=0.018..145.272 rows=602825 loops=1) Total runtime: 697.495 ms (6 rows)
Sm9lIFZhbiBEeWsgd3JvdGU6DQo+IE15IGFzc3VtcHRpb24gd2FzIHRoYXQgV0lUSCBhY3RlZCBq dXN0IGxpa2Ugc3Vic2VsZWN0cywgYnV0IGFwcGFyZW50bHkgdGhleSBkb24ndD8gVXNpbmcgV0lU SCBkb2Vzbid0DQo+IHVzZSB0aGUgZXhwZWN0ZWQgaW5kZXguDQoNCkN1cnJlbnRseSBXSVRIIGFj dHMgYXMgYW4gIm9wdGltaXphdGlvbiBmZW5jZSIsIHRoYXQgbWVhbnMNCnRoYXQgbWVhbnMgdGhh dCB0aGUgcGxhbm5lciB3b24ndCBtb3ZlIGNvbmRpdGlvbnMgaW50byBvcg0Kb3V0IG9mIHRoZSBX SVRIIHF1ZXJ5Lg0KDQpZb3VycywNCkxhdXJlbnogQWxiZQ0K
On Tue, Feb 19, 2013 at 1:02 AM, Albe Laurenz <laurenz.albe@wien.gv.at>wrote: > Joe Van Dyk wrote: > > My assumption was that WITH acted just like subselects, but apparently > they don't? Using WITH doesn't > > use the expected index. > > Currently WITH acts as an "optimization fence", that means > that means that the planner won't move conditions into or > out of the WITH query. Where's the best place to read up on this? Thanks, Joe
On Tue, Feb 19, 2013 at 9:22 AM, Joe Van Dyk <joe@tanga.com> wrote: > On Tue, Feb 19, 2013 at 1:02 AM, Albe Laurenz <laurenz.albe@wien.gv.at> > wrote: >> >> Joe Van Dyk wrote: >> > My assumption was that WITH acted just like subselects, but apparently >> > they don't? Using WITH doesn't >> > use the expected index. >> >> Currently WITH acts as an "optimization fence", that means >> that means that the planner won't move conditions into or >> out of the WITH query. > > > Where's the best place to read up on this? Unfortunately, the mailing list archives. Rightly or wrongly, postgresql docs are exceptionally light in terms of performance aspects of various SQL mechanisms. (non-data modifying) WITH is basically formalization of technique: A extract to temp table B query that table. Not the optimization fence characteristic is an implementation detail and not future proofed but is nevertheless widely replied upon. merlin
Hi Merlin, So should I interpret this as: there is a potential gain from choosing subqueries over with WITHs ? On Tue, Feb 19, 2013 at 3:33 PM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Tue, Feb 19, 2013 at 9:22 AM, Joe Van Dyk <joe@tanga.com> wrote: > > On Tue, Feb 19, 2013 at 1:02 AM, Albe Laurenz <laurenz.albe@wien.gv.at> > > wrote: > >> > >> Joe Van Dyk wrote: > >> > My assumption was that WITH acted just like subselects, but apparently > >> > they don't? Using WITH doesn't > >> > use the expected index. > >> > >> Currently WITH acts as an "optimization fence", that means > >> that means that the planner won't move conditions into or > >> out of the WITH query. > > > > > > Where's the best place to read up on this? > > Unfortunately, the mailing list archives. Rightly or wrongly, > postgresql docs are exceptionally light in terms of performance > aspects of various SQL mechanisms. > > (non-data modifying) WITH is basically formalization of technique: A > extract to temp table B query that table. Not the optimization fence > characteristic is an implementation detail and not future proofed but > is nevertheless widely replied upon. > > merlin > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Thu, Feb 21, 2013 at 4:31 AM, Seref Arikan <serefarikan@gmail.com> wrote: > Hi Merlin, > So should I interpret this as: there is a potential gain from choosing > subqueries over with WITHs ? Well, potentially, yes. WITH is a mechanic to force iterative order of evaluation on queries. This can be a good or bad thing naturally. Subqueries can also do this, especially if you put them in the field select list -- but WITH is more general. We also have an undocumented hack that uses OFFSET 0 to force subquery evaluation. These are all very dangerous tools because they tend to be very sensitive to data inputs as you are bypassing database statistics effectively. The other end of the spectrum is to use vanilla JOINs as much as possible -- this releases the work of planning the query to the database. Upcoming 9.3 LATERAL will remove one large class of cases where we have to do this: joining against set returning functions with non-constant inputs. merlin