subselects vs WITH in views

From: Joe Van Dyk <joe(at)tanga(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: subselects vs WITH in views
Date: 2013-02-18 23:05:05
Message-ID: CACfv+pLNN-PGU9Fx-m-jt_5NY1u5XbFtzPYE41_T5XVLiMGH4w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Grittner 2013-02-18 23:05:24 Re: Why is this a cross join?
Previous Message Tim Uckun 2013-02-18 22:19:35 Re: Why is this a cross join?