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)
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? |