subselects vs WITH in views
От | Joe Van Dyk |
---|---|
Тема | subselects vs WITH in views |
Дата | |
Msg-id | CACfv+pLNN-PGU9Fx-m-jt_5NY1u5XbFtzPYE41_T5XVLiMGH4w@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: subselects vs WITH in views
|
Список | 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)
В списке pgsql-general по дате отправления: