Поддерживает ли PostgreSQL заказ CTE?

В PostgreSQL общие табличные выражения (CTE) - это оптимизационные заграждения. Это означает, что CTE материализуется в память и что предикаты из другого запроса никогда не будут вдавлены в CTE.

Теперь мне интересно, будут ли другие метаданные об CTE, такие как упорядочение, разделяться на другие запросы. Возьмем следующий запрос:

WITH ordered_objects AS
(
    SELECT * FROM object ORDER BY type ASC LIMIT 10
)
SELECT MIN(type) FROM ordered_objects

Здесь MIN(type), очевидно, всегда является первой строкой ordered_objects (или NULL если ordered_objects пусты), поскольку ordered_objects уже упорядочены по type. Доступны ли эти знания о ordered_objects при оценке SELECT MIN(type) FROM ordered_objects?

Ответы

Ответ 1

Если я правильно понимаю ваш вопрос - нет, это не так. нет таких знаний. Как вы найдете в примере ниже. когда вы ограничиваете до 10 строк, выполнение выполняется очень быстро - меньше данных для обработки (в моем случае в миллион раз меньше), что означало бы, что CTE сканирует весь упорядоченный набор, игнорируя факт, что min будет в первых строках...

данные:

t=# create table object (type bigint);
CREATE TABLE
Time: 4.636 ms
t=# insert into object select generate_series(1,9999999);
INSERT 0 9999999
Time: 7769.275 ms

с пределом:

explain analyze WITH ordered_objects AS
(
    SELECT * FROM object ORDER BY type ASC LIMIT 10
)
SELECT MIN(type) FROM ordered_objects;

Время выполнения: 3150,183 мс

https://explain.depesz.com/s/5yXe

без:

explain analyze WITH ordered_objects AS
(
    SELECT * FROM object ORDER BY type ASC
)
SELECT MIN(type) FROM ordered_objects;

Время выполнения: 16032,989 мс

https://explain.depesz.com/s/1SU

Я, разумеется, разогревал данные перед испытаниями

Ответ 2

  • [в Postgres] CTE всегда вызывается один раз
  • , даже если он ссылался более одного раза
  • его результат хранится во временную таблицу (материализованную)
  • внешний запрос не знает о внутренней структуре (индексы недоступны) или порядок (не уверен в частотных оценках), он просто сканирует временные результаты
  • в приведенном ниже фрагменте CTE сканируется дважды, даже если результаты, как известно, идентичны.
\d react

EXPLAIN ANALYZE
WITH omg AS (
        SELECT topic_id
        , row_number() OVER (PARTITION by krant_id ORDER BY topic_id) AS rn
        FROM react
        WHERE krant_id = 1
        AND topic_id < 5000000
        ORDER BY topic_id ASC
        )
SELECT MIN (o2.topic_id)
FROM omg o1                   --
JOIN omg o2 ON o1.rn = o2.rn  -- exactly the same
WHERE o1.rn = 1
        ;

                    Table "public.react"
   Column   |           Type           |     Modifiers      
------------+--------------------------+--------------------
 krant_id   | integer                  | not null default 1
 topic_id   | integer                  | not null
 react_id   | integer                  | not null
 react_date | timestamp with time zone | 
 react_nick | character varying(1000)  | 
 react_body | character varying(4000)  | 
 zoek       | tsvector                 | 
Indexes:
    "react_pkey" PRIMARY KEY, btree (krant_id, topic_id, react_id)
    "react_krant_id_react_nick_react_date_topic_id_react_id_idx" UNIQUE, btree (krant_id, react_nick, react_date, topic_id, react_id)
    "react_date" btree (krant_id, topic_id, react_date)
    "react_nick" btree (krant_id, topic_id, react_nick)
    "react_zoek" gin (zoek)
Triggers:
    tr_upd_zzoek_i BEFORE INSERT ON react FOR EACH ROW EXECUTE PROCEDURE tf_upd_zzoek()
    tr_upd_zzoek_u BEFORE UPDATE ON react FOR EACH ROW WHEN (new.react_body::text <> old.react_body::text) EXECUTE PROCEDURE tf_upd_zzoek()

----------

 Aggregate  (cost=232824.29..232824.29 rows=1 width=4) (actual time=1773.643..1773.645 rows=1 loops=1)
   CTE omg
     ->  WindowAgg  (cost=0.43..123557.17 rows=402521 width=8) (actual time=0.217..1246.577 rows=230822 loops=1)
           ->  Index Only Scan using react_pkey on react  (cost=0.43..117519.35 rows=402521 width=8) (actual time=0.161..419.916 rows=230822 loops=1)
                 Index Cond: ((krant_id = 1) AND (topic_id < 5000000))
                 Heap Fetches: 442
   ->  Nested Loop  (cost=0.00..99136.69 rows=4052169 width=4) (actual time=0.264..1773.624 rows=1 loops=1)
         ->  CTE Scan on omg o1  (cost=0.00..9056.72 rows=2013 width=8) (actual time=0.249..59.252 rows=1 loops=1)
               Filter: (rn = 1)
               Rows Removed by Filter: 230821
         ->  CTE Scan on omg o2  (cost=0.00..9056.72 rows=2013 width=12) (actual time=0.003..1714.355 rows=1 loops=1)
               Filter: (rn = 1)
               Rows Removed by Filter: 230821
 Total runtime: 1782.887 ms
(14 rows)