Общее табличное выражение (от англ. Common Table Expressions, CTE) - временный именованный набор данных, полученный из простого запроса и определённый в области действия операций SELECT, INSERT, UPDATE, или DELETE.
4 сентября Tom Lane применил очередной замечательный патч. В этот раз он довольно весомый, т.ч. даже после принятия в нём остаются кое-какие недоделки. Понадобятся дополнительные патчи для того, чтобы реализовать полный функционал, но сам факт того, что он был принят означает его появление в 8.4.
Что же он делает?
Сначала посмотрим описание патча:
Реализует соответствующее SQL стандарту выражение WITH, включая WITH RECURSIVE.Описание действительно выглядит интересно, ссылается на SQL стандарт, но что мы реально получаем?
Имеются некоторые не реализованные аспекты: рекурсивные запросы должны использовать
UNION ALL (использование UNION должно тоже позволяться) и у нас нет выражений SEARCH и CYCLE.
Они могут быть сделаны или нет к 8.4, но даже без них это очень полезная возможность.
Так же имеется пара маленьких неопределённостей и ухищрений о которых я упоминал в
pgsql-hackers. Но давайте примем патч сейчас для того, чтобы привлечь других разработчиков.
Yoshiyuki Asaba, с огромной помощью Taysuo Ishii и Tom Lane.
Пример прямо из новой документации:
WITH regional_sales AS (Не сложно заметить, что запросы WITH не что иное как инлайновые view или лучше временные таблицы, но существующие только во время выполнения запроса.
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
Что это даёт?
Давайте проверим:
# create table orders (region int4, product int4, quantity int4, amount int4);Во первых, попробуем запрос из документации (немного изменённый):
CREATE TABLE
# insert into orders (region, product, quantity, amount)
select random() * 1000, random() * 5000, 1 + random() * 20, 1 + random() * 1000
from generate_series(1,10000);
INSERT 0 10000
# explain analyzeКак вы можете видеть я изменил определение "top_regions" - вместо 10% продаж я определил лучшие регионы как более чем в два раза превысившие средние продажи.
>> WITH regional_sales AS (
>> SELECT region, SUM(amount) AS total_sales
>> FROM orders
>> GROUP BY region
>> ), top_regions AS (
>> SELECT region
>> FROM regional_sales
>> WHERE total_sales > (SELECT 2 * avg(total_sales) FROM regional_sales)
>> )
>> SELECT region,
>> product,
>> SUM(quantity) AS product_units,
>> SUM(amount) AS product_sales
>> FROM orders
>> WHERE region IN (SELECT region FROM top_regions)
>> GROUP BY region, product;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=509.55..539.52 rows=1998 width=16) (actual time=71.882..72.254 rows=221 loops=1)
InitPlan
-> HashAggregate (cost=205.00..217.51 rows=1001 width=8) (actual time=31.532..33.234 rows=1001 loops=1)
-> Seq Scan on orders (cost=0.00..155.00 rows=10000 width=8) (actual time=0.005..13.888 rows=10000 loops=1)
-> CTE Scan on regional_sales (cost=22.54..47.56 rows=334 width=4) (actual time=39.250..39.719 rows=12 loops=1)
Filter: ((total_sales)::numeric > $1)
InitPlan
-> Aggregate (cost=22.52..22.54 rows=1 width=8) (actual time=7.666..7.667 rows=1 loops=1)
-> CTE Scan on regional_sales (cost=0.00..20.02 rows=1001 width=8) (actual time=0.002..4.786 rows=1001 loops=1)
-> Hash Join (cost=12.02..224.50 rows=1998 width=16) (actual time=40.069..71.422 rows=221 loops=1)
Hash Cond: (public.orders.region = top_regions.region)
-> Seq Scan on orders (cost=0.00..155.00 rows=10000 width=16) (actual time=0.011..16.861 rows=10000 loops=1)
-> Hash (cost=9.52..9.52 rows=200 width=4) (actual time=39.825..39.825 rows=12 loops=1)
-> HashAggregate (cost=7.51..9.52 rows=200 width=4) (actual time=39.785..39.805 rows=12 loops=1)
-> CTE Scan on top_regions (cost=0.00..6.68 rows=334 width=4) (actual time=39.254..39.762 rows=12 loops=1)
Total runtime: 72.674 ms
(16 rows)
Теперь попробуем переписать этот запрос без использования WITH:
# explain analyzeКак видно - это медленней. Причина очень проста. "Старый" запрос вынужден сканировать таблицу 3 раза.
>> SELECT region,
>> product,
>> SUM(quantity) AS product_units,
>> SUM(amount) AS product_sales
>> FROM orders
>> WHERE region IN (
>> SELECT region
>> FROM orders
>> group by region
>> having sum(amount) > 2 * (
>> select avg(sum) from (
>> Select sum(amount) from orders group by region
>> ) as x
>> )
>> )
>> GROUP BY region, product;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=710.04..740.01 rows=1998 width=16) (actual time=130.271..130.644 rows=221 loops=1)
-> Hash Join (cost=477.58..690.06 rows=1998 width=16) (actual time=85.452..129.649 rows=221 loops=1)
Hash Cond: (public.orders.region = public.orders.region)
-> Seq Scan on orders (cost=0.00..155.00 rows=10000 width=16) (actual time=0.011..16.427 rows=10000 loops=1)
-> Hash (cost=465.07..465.07 rows=1001 width=4) (actual time=85.157..85.157 rows=12 loops=1)
-> HashAggregate (cost=435.04..455.06 rows=1001 width=8) (actual time=83.615..85.127 rows=12 loops=1)
Filter: ((sum(public.orders.amount))::numeric > (2::numeric * $0))
InitPlan
-> Aggregate (cost=230.03..230.04 rows=1 width=8) (actual time=47.372..47.374 rows=1 loops=1)
-> HashAggregate (cost=205.00..217.51 rows=1001 width=8) (actual time=41.329..43.404 rows=1001 loops=1)
-> Seq Scan on orders (cost=0.00..155.00 rows=10000 width=8) (actual time=0.007..20.318 rows=10000 loops=1)
-> Seq Scan on orders (cost=0.00..155.00 rows=10000 width=8) (actual time=0.005..15.737 rows=10000 loops=1)
Total runtime: 131.050 ms
(13 rows)
Новый это делает только 2 раза.
WITH может быть использован для написания более читаемых запросов работающих с меньшим количеством данных и, соответственно, более быстрых.
Но это не всё. Также существует специальная форма WITH запросов, которые могут дать эффект, не достижимый прежде. Это WITH RECURSIVE.
Представим простейшую древовидную структуру:
create table tree (id serial primary key, parent_id int4 references tree (id));Тут создаётся "лес" (не просто "дерево", т.к. имеют место несколько корневых элементов), который имеет (с моими случайными данными):
insert into tree (parent_id) values (NULL);
insert into tree (parent_id)
select case when random() < 0.95 then floor(1 + random() * currval('tree_id_seq')) else NULL end
from generate_series(1,1000) i;
- 1001 элемент
- 56 корневых узлов
- 28 корневых узлов содержащих дочерние элементы
- самый длинный путь содержит 16 узлов: 1 -> 2 -> 3 -> 7 -> 12 -> 15 -> 39 -> 52 -> 61 -> 107 -> 123 -> 194 -> 466 -> 493 -> 810 -> 890
Традиционно, если понадобится вывести всех родителей узла 890, потребуется написать цикл делающий запросы до тех пор, пока не будет получена строка, где "parent_id IS NULL".
Но сейчас мы можем сделать следующее:
WITH RECURSIVE struct AS (Что работает действительно здорово:
SELECT t.* FROM tree t WHERE id = 890
UNION ALL
SELECT t.* FROM tree t, struct s WHERE t.id = s.parent_id
)
SELECT * FROM struct;
QUERY PLANВас может смутить наличие "Seq Scan on tree…loops=15", но не стоит беспокоиться. Это так из-за очень малого количества строк в таблице.
------------------------------------------------------------------------------------------------------------------------------------
CTE Scan on struct (cost=402.18..404.20 rows=101 width=8) (actual time=0.037..69.311 rows=16 loops=1)
InitPlan
-> Recursive Union (cost=0.00..402.18 rows=101 width=8) (actual time=0.030..69.222 rows=16 loops=1)
-> Index Scan using tree_pkey on tree t (cost=0.00..8.27 rows=1 width=8) (actual time=0.024..0.029 rows=1 loops=1)
Index Cond: (id = 890)
-> Hash Join (cost=0.33..39.19 rows=10 width=8) (actual time=2.165..4.313 rows=1 loops=16)
Hash Cond: (t.id = s.parent_id)
-> Seq Scan on tree t (cost=0.00..35.01 rows=1001 width=8) (actual time=0.005..2.434 rows=1001 loops=15)
-> Hash (cost=0.20..0.20 rows=10 width=4) (actual time=0.012..0.012 rows=1 loops=16)
-> WorkTable Scan on struct s (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.005 rows=1 loops=16)
Total runtime: 69.445 ms
(11 rows)
После добавления дополнительных 50000 строк получаем:
QUERY PLANЧто выглядит превосходно.
---------------------------------------------------------------------------------------------------------------------------------------
CTE Scan on struct (cost=840.76..842.78 rows=101 width=8) (actual time=0.039..0.672 rows=16 loops=1)
InitPlan
-> Recursive Union (cost=0.00..840.76 rows=101 width=8) (actual time=0.032..0.591 rows=16 loops=1)
-> Index Scan using tree_pkey on tree t (cost=0.00..8.27 rows=1 width=8) (actual time=0.025..0.029 rows=1 loops=1)
Index Cond: (id = 890)
-> Nested Loop (cost=0.00..83.05 rows=10 width=8) (actual time=0.018..0.027 rows=1 loops=16)
-> WorkTable Scan on struct s (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.004 rows=1 loops=16)
-> Index Scan using tree_pkey on tree t (cost=0.00..8.27 rows=1 width=8) (actual time=0.008..0.011 rows=1 loops=16)
Index Cond: (t.id = s.parent_id)
Total runtime: 0.799 ms
(10 rows)
Как я упоминал ранее, остаются вещи нуждающиеся в доработке. Но даже сейчас WITH запросы выглядят великолепно.
Posts
2 comments:
Смущает меня только одно. в последнем примере время выполнения запроса с малым количеством строк/данных в таблице во много раз больше чем если в таблице намного больше строк данных .... Оптимизатор ошибся ?
to 1: Не могу сказать ничего определённого по этому поводу. Возможно из-за загрузки сервера или состояния кэша на тот момент.
Post a Comment