FreemanZAVИ всё же хотелось бы уточнить. Например если есть таблица table1 с сотнями миллионов записей и с уникальным индексом field_1, то в запросе
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
with
t1
as
(
select field_1 ,
case when field_2 between 10 and 20 then field_3 else field_4 end f2,
case when field_3 between 128 and 345 then field_7 else field_8 end f3 /*и ещё масса выражений*/
from table1 t
)
select * from t1 where field_1 = 1
union all
select * from t1 where field_1 = 2
union all
select * from t1 where field_1 = 3
PG будет материализовать все сотни миллионов записей?
P.S. Вопрос чисто технический. Без иронии и попыток умаления достоинств PG.
ответ для 9.1 таков ( у меня по случаю завалялась тестовая таблица на лям записей, но не такая как у вас):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
select count(*) FROM test_table;
--1000000
EXPLAIN
SELECT foo.* FROM test_table foo WHERE high_cardinality =1000;
/*
"Bitmap Heap Scan on test_table foo (cost=6.11..359.75 rows=98 width=16)"
" Recheck Cond: (high_cardinality = 1000)"
" -> Bitmap Index Scan on test_table_high_cardinality_key (cost=0.00..6.08 rows=98 width=0)"
" Index Cond: (high_cardinality = 1000)"
*/
EXPLAIN
WITH foo AS (SELECT * FROM test_table)
SELECT foo.* FROM foo WHERE high_cardinality =1000;
/*
"CTE Scan on foo (cost=15406.00..37906.00 rows=5000 width=16)"
" Filter: (high_cardinality = 1000)"
" CTE foo"
" -> Seq Scan on test_table (cost=0.00..15406.00 rows=1000000 width=16)"
*/
EXPLAIN ANALYZE
SELECT foo.* FROM test_table foo WHERE high_cardinality =1000;
/*
"Bitmap Heap Scan on test_table foo (cost=6.11..359.75 rows=98 width=16) (actual time=38.859..41.468 rows=98 loops=1)"
" Recheck Cond: (high_cardinality = 1000)"
" -> Bitmap Index Scan on test_table_high_cardinality_key (cost=0.00..6.08 rows=98 width=0) (actual time=38.798..38.798 rows=98 loops=1)"
" Index Cond: (high_cardinality = 1000)"
"Total runtime: 41.523 ms"
*/
EXPLAIN ANALYZE
WITH foo AS (SELECT * FROM test_table)
SELECT foo.* FROM foo WHERE high_cardinality =1000;
/*
"CTE Scan on foo (cost=15406.00..37906.00 rows=5000 width=16) (actual time=3.727..433.065 rows=98 loops=1)"
" Filter: (high_cardinality = 1000)"
" Rows Removed by Filter: 999902"
" CTE foo"
" -> Seq Scan on test_table (cost=0.00..15406.00 rows=1000000 width=16) (actual time=0.048..103.362 rows=1000000 loops=1)"
"Total runtime: 440.657 ms"
*/
-- видим, что PG не называет это "materialize" но прямо "CTE", при этом CTE в нем есть темповая таблица. (что видно при рассмотрения плана рекурсий). Поэтому для PG пока надо таки так
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
select * from (
select field_1 ,
case when field_2 between 10 and 20 then field_3 else field_4 end f2,
case when field_3 between 128 and 345 then field_7 else field_8 end f3 /*и ещё масса выражений*/
from table1 t
) t1 where field_1 = 1
union all
select * from (
select field_1 ,
case when field_2 between 10 and 20 then field_3 else field_4 end f2,
case when field_3 between 128 and 345 then field_7 else field_8 end f3 /*и ещё масса выражений*/
from table1 t
) t1 where field_1 = 2
union all
select * from (
select field_1 ,
case when field_2 between 10 and 20 then field_3 else field_4 end f2,
case when field_3 between 128 and 345 then field_7 else field_8 end f3 /*и ещё масса выражений*/
from table1 t
) t1 where field_1 = 3
если конечно не рассматривать сразу правильного:
1.
2.
3.
4.
5.
6.
WITH foo AS (SELECT * FROM table1 t WHERE field1 IN (1,2,3))
SELECT * from (
select field_1 ,
case when field_2 between 10 and 20 then field_3 else field_4 end f2,
case when field_3 between 128 and 345 then field_7 else field_8 end f3 /*и ещё масса выражений*/
from foo;
