Материализовать общее табличное выражение в Greenplum

Есть ли способ заставить Greenplum PostgreSQL материализовать подзапрос в предложении WITH, подобно тому, что MATERIALIZE и INLINE подсказки оптимизатора делать в Oracle, как показано ниже?

WITH dept_count AS (
  SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

Я искал это некоторое время, только чтобы найти эту функциональность в Oracle.

Я знаю, что могу использовать CREATE TABLE AS, но у меня есть несколько похожих запросов, вынуждающих меня удалять временную таблицу после каждого запроса, что очень неудобно и, возможно, неэффективно.

Обновление: я протестировал следующую таблицу:

CREATE TABLE test (id: INT);

EXPLAIN WITH test2 AS (SELECT id FROM test)
SELECT COUNT(*) FROM test2;

                                 QUERY PLAN                                     
------------------------------------------------------------------------------------
Aggregate  (cost=0.36..0.37 rows=1 width=8)
   ->  Gather Motion 32:1  (slice1; segments: 32)  (cost=0.01..0.35 rows=1 width=8)
         ->  Aggregate  (cost=0.01..0.01 rows=1 width=8)
               ->  Subquery Scan test2  (cost=0.00..0.00 rows=1 width=0)
                     ->  Seq Scan on test  (cost=0.00..0.00 rows=1 width=4)

Я использую Greenplum Postgresql 8.2.


postgresql sql common-table-expression greenplum
person Yang    schedule 09.03.2013    source источник
comment
Опубликуйте explain analyze вывод.   -  person Craig Ringer    schedule 09.03.2013
comment
Постгрес 8.2? CTE были представлены в Postgres 8.4. Вы случайно не 9.2 имеете в виду?   -  person Erwin Brandstetter    schedule 09.03.2013
comment
Это 8,2. Я использую Greenplum, а Greenplum поддерживает только старые версии Postgres.   -  person Yang    schedule 09.03.2013
comment
Я сбит с толку. Как вы запускаете CTE в 8.2? Или это вывод EXPLAIN от Oracle? Кроме того, под сообщениями, которые не являются моими, вам нужно использовать @-reply в комментарии, чтобы убедиться, что я получаю уведомление.   -  person Erwin Brandstetter    schedule 09.03.2013
comment
@ErwinBrandstetter Это psql (8.2.15), план ничего не реализует.   -  person Yang    schedule 09.03.2013
comment
psql (инструмент командной строки) может быть версии 8.2, но Postgres не может - иначе у него не было бы CTE. Или Greeplum обновил CTE? Я бы скорее удивился.   -  person Erwin Brandstetter    schedule 09.03.2013
comment
@Yang Пожалуйста, покажите точный текст вывода SELECT version().   -  person Craig Ringer    schedule 09.03.2013
comment
@Yang Кроме того, если вы используете Greenplum или аналогичный, сообщите об этом заранее. Это очень сбивает с толку, когда люди пишут о сторонних вариантах PostgreSQL, не говоря об этом, поскольку были сделаны всевозможные модификации. В PostgreSQL даже нет узла плана Gather Motion, его просто нет; Pg 8.2 также не поддерживает CTE. Так что ваш вопрос на самом деле вовсе не о PostgreSQL...   -  person Craig Ringer    schedule 09.03.2013
comment
@CraigRinger извини за это. версия -------------------------------------------------- ----------------------------- --------------------- ------------------------------------------------------------- PostgreSQL 8.2. 15 (база данных Greenplum 4.2.2.4 сборка 1 Community Edition) на x86 _64-unknown-linux-gnu, скомпилированная GCC gcc (GCC) 4.4.2 скомпилированная 17 октября 2012 г. 1 1:52:28   -  person Yang    schedule 09.03.2013

Ответы (2)


arrow_upward
1
arrow_downward

Временная таблица

Если вы ищете временную таблицу, которая сохраняется на время вашего сеанса, используйте фактическую TEMPORARY TABLE.

CREATE TEMPORARY TABLE t AS
SELECT deptno, COUNT(*) AS dept_count
FROM   emp
GROUP  BY deptno;

SELECT ...
FROM t ...

CREATE [TEMPORARY] TABLE AS в руководстве.

Однако в PostgreSQL нет глобальной временной таблицы. Временные таблицы видны только создавшему их пользователю и только на время сеанса, в котором они были созданы.

CTE видны только в том запросе, частью которого они являются. Никогда дальше этого.

Временная таблица исключительно для одного запроса

Чтобы ограничить видимость ваших временных таблиц одним запросом, поместите их в транзакцию и добавьте ON COMMIT DROP, который автоматически удаляет временную таблицу в конце транзакции:

BEGIN;
CREATE TEMP TABLE t ON COMMIT DROP AS
SELECT ...

Единственный вариант использования, который я могу придумать, где это имело бы смысл: если вы хотите создать индексы в огромной временной таблице:

CREATE INDEX ON t(col1);

SELECT ..
FROM t ...;

ROLLBACK;

Или (здесь без разницы):

COMMIT;

Если вы используете ROLLBACK, вы также можете просто использовать временную таблицу без ON COMMIT DROP, так как все равно все откатывается.

person Erwin Brandstetter    schedule 09.03.2013
comment
Да, это может решить мою проблему, но было бы лучше, если бы временная таблица отображалась только в рамках одного запроса, поскольку у меня есть несколько запросов, для каждого из которых требуется временная таблица. Было бы довольно неудобно часто создавать и удалять таблицы. - person Yang; 09.03.2013
comment
@Yang: Хорошо, но это то, что CTE делает из коробки: a temporary table visible only within one query. В отличие от подзапроса, CTE виден на любом уровне запроса этого запроса. При чем тут путаница? - person Erwin Brandstetter; 09.03.2013
comment
CTE не материализует таблицу. Я присоединяюсь к трем очень большим таблицам. У Postgresql закончилось место для хранения, а первое соединение не материализовалось. - person Yang; 09.03.2013
comment
@Yang: я добавил способы создания временной таблицы только для одного запроса. Но я не уверен, что это даст вам какое-либо преимущество перед использованием CTE. - person Erwin Brandstetter; 09.03.2013

arrow_upward
3
arrow_downward

В PostgreSQL CTE — это барьер оптимизации, который вызывает материализацию термина CTE. Мы надеемся, что это изменится в будущих выпусках, но будет предоставлена ​​возможность обратной совместимости, если это когда-либо произойдет.

Если вы сделаете explain analyze в своем запросе, вы обнаружите, что термин dept_count выполняется в отдельном дереве плана как CTE Scan. Он накапливается в хранилище кортежей, как и материализованные результаты, IIRC.

Обновление: на самом деле автор использует Greenplum. Вышеупомянутое утверждение не похоже на правду для Greenplum, которая внедрила свою собственную поддержку CTE поверх кодовой базы PostgreSQL 8.2 или сделала непрямой бэкпорт функции CTE 8.4 со значительными изменениями. В Greenplum похоже, что вам, возможно, придется использовать временную таблицу, если нет доступных дополнительных функций, специфичных для Greenplum.

person Craig Ringer    schedule 09.03.2013
comment
@Yang Ответ обновлен. Короче говоря, вы задали вопрос с тегом PostgreSQL, и вы действительно используете Greenplum, а не PostgreSQL, поэтому я не могу подробно помочь вам с проблемой, специфичной для Greenplum; возможно, спросите на greenplum.com/communities/forums . Я подозреваю, что вам не повезло, и вам нужно будет использовать временные таблицы. - person Craig Ringer; 10.03.2013