November 9, 2008

В ожидании 8.4 - Общие табличные выражения (WITH-запросы)

Перевод Waiting for 8.4 - Common Table Expressions (WITH queries) с select * from depesz;

Общее табличное выражение (от англ. Common Table Expressions, CTE) - временный именованный набор данных, полученный из простого запроса и определённый в области действия операций SELECT, INSERT, UPDATE, или DELETE.

4 сентября Tom Lane применил очередной замечательный патч. В этот раз он довольно весомый, т.ч. даже после принятия в нём остаются кое-какие недоделки. Понадобятся дополнительные патчи для того, чтобы реализовать полный функционал, но сам факт того, что он был принят означает его появление в 8.4.

Что же он делает?

Сначала посмотрим описание патча:
Реализует соответствующее SQL стандарту выражение WITH, включая WITH RECURSIVE.

Имеются некоторые не реализованные аспекты: рекурсивные запросы должны использовать
UNION ALL (использование UNION должно тоже позволяться) и у нас нет выражений SEARCH и CYCLE.

Они могут быть сделаны или нет к 8.4, но даже без них это очень полезная возможность.

Так же имеется пара маленьких неопределённостей и ухищрений о которых я упоминал в
pgsql-hackers. Но давайте примем патч сейчас для того, чтобы привлечь других разработчиков.

Yoshiyuki Asaba, с огромной помощью Taysuo Ishii и Tom Lane.
Описание действительно выглядит интересно, ссылается на SQL стандарт, но что мы реально получаем?

Пример прямо из новой документации:
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 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;
Не сложно заметить, что запросы WITH не что иное как инлайновые view или лучше временные таблицы, но существующие только во время выполнения запроса.

Что это даёт?

Давайте проверим:
# 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
>> 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)
Как вы можете видеть я изменил определение "top_regions" - вместо 10% продаж я определил лучшие регионы как более чем в два раза превысившие средние продажи.

Теперь попробуем переписать этот запрос без использования WITH:
# explain analyze
>> 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)
Как видно - это медленней. Причина очень проста. "Старый" запрос вынужден сканировать таблицу 3 раза.

Новый это делает только 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
------------------------------------------------------------------------------------------------------------------------------------
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)
Вас может смутить наличие "Seq Scan on tree…loops=15", но не стоит беспокоиться. Это так из-за очень малого количества строк в таблице.

После добавления дополнительных 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 запросы выглядят великолепно.

2 comments:

Anonymous said...

Смущает меня только одно. в последнем примере время выполнения запроса с малым количеством строк/данных в таблице во много раз больше чем если в таблице намного больше строк данных .... Оптимизатор ошибся ?

grayhemp said...

to 1: Не могу сказать ничего определённого по этому поводу. Возможно из-за загрузки сервера или состояния кэша на тот момент.

Post a Comment