Медленный запрос в представлении UNION ALL

Это похоже на ошибку пилота. План запроса "v" выбирает как минимум из 5 разных таблиц.

Теперь, вы уверены, что вы подключены к правильной базе данных? Может есть какие-то крутые настройки search_path? Может быть, t1 и t2 на самом деле являются представлениями (возможно, в другой схеме)? Может быть, Вы как-то выбираете из неправильного представления?

Отредактировано после уточнения:

Вы используете совершенно новую функцию под названием «удаление соединения»: http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#Join_Removal

http://rhaas.blogspot.com/2010/06/why-join-removal-is-cool.html

Похоже, что эта функция не срабатывает, когда задействовано объединение всех. Вероятно, вам придется переписать представление, используя только две необходимые таблицы.

другое редактирование: похоже, вы используете агрегат (например, «выбрать количество (*) из v» по сравнению с «выбрать * из v»), который может получить совершенно разные планы перед лицом удаления объединения. Думаю, мы не продвинемся далеко, если вы не опубликуете фактические запросы, представления и определения таблиц, а также используемые планы...

Я считаю, что ваш запрос выполняется аналогично:

(
   ( SELECT time, etc. FROM t1 // #1... )
   UNION ALL
   ( SELECT time, etc. FROM t2 // #2... )
)
WHERE time >= ... AND time < ...

которые оптимизатор испытывает трудности с оптимизацией. т. е. сначала выполняется UNION ALL перед применением предложения WHERE, но вы хотите, чтобы оно применяло предложение WHERE перед UNION ALL.

Не могли бы вы поместить пункт WHERE в пункт CREATE VIEW?

CREATE VIEW v AS
( SELECT time, etc. FROM t1  WHERE time >= ... AND time < ... )
UNION ALL
( SELECT time, etc. FROM t2  WHERE time >= ... AND time < ... )

В качестве альтернативы, если представление не может иметь предложение WHERE, то, возможно, вы можете придерживаться двух представлений и выполнять UNION ALL с предложением WHERE, когда они вам понадобятся:

CREATE VIEW v1 AS
SELECT time, etc. FROM t1 // #1...

CREATE VIEW v2 AS
SELECT time, etc. FROM t2 // #2...

( SELECT * FROM v1 WHERE time >= ... AND time < ... )
UNION ALL
( SELECT * FROM v2 WHERE time >= ... AND time < ... )

Я не знаю Postgres, но некоторые RMDB обрабатывают операторы сравнения хуже, чем BETWEEN в случае индексов. Я бы попытался использовать BETWEEN.

SELECT ... FROM v WHERE time BETWEEN ... AND ...

Можно было бы динамически выдавать новый SQL при каждом вызове вместо создания представления и интегрировать предложение where в каждый SELECT запроса на объединение.

SELECT time, etc. FROM t1
    WHERE time >= ... AND time < ...
UNION ALL
SELECT time, etc. FROM t2
    WHERE time >= ... AND time < ...

РЕДАКТИРОВАТЬ:

Можно ли использовать параметризованную функцию?

CREATE OR REPLACE FUNCTION CallMyView(t1 date, t2 date)
RETURNS TABLE(d date, etc.)
AS $$
    BEGIN
        RETURN QUERY
            SELECT time, etc. FROM t1
                WHERE time >= t1 AND time < t2
            UNION ALL
            SELECT time, etc. FROM t2
                WHERE time >= t1 AND time < t2;
    END;
$$ LANGUAGE plpgsql;

Вызов

SELECT * FROM CallMyView(..., ...);

Объедините две таблицы. Добавьте столбец для обозначения исходной таблицы. При необходимости замените исходные имена таблиц представлениями, которые выбирают только соответствующую часть. Проблема решена!

Изучение шаблона проектирования базы данных суперкласса/подкласса может быть вам полезно.

Попробуйте создать свое представление, используя UNION DISTINCT вместо UNION ALL. Посмотрите, дает ли это неправильные результаты. Посмотрите, дает ли это более высокую производительность.

Если это дает неправильные результаты, попробуйте сопоставить ваши операции SQL с таблицами обратно с реляционными операциями с отношениями. Элементы отношений всегда различны. В вашей модели может быть что-то принципиально неправильное.

Я очень подозрительно отношусь к ЛЕВЫМ СОЕДИНЕНИЯМ в показанном вами плане запроса. Нет необходимости выполнять LEFT JOINS, чтобы получить результаты, которые вы выбираете.

Столкнулся с таким же сценарием на 11g:

Сценарий 1:

CREATE VIEW v AS
  SELECT time, etc. FROM t1 // #1...

Следующий запрос выполняется быстро, план выглядит нормально:

SELECT ... FROM v WHERE time >= ... AND time < ...

Сценарий 2:

CREATE VIEW v AS
  SELECT time, etc. FROM t2 // #2...

Следующий запрос выполняется быстро, план выглядит нормально:

SELECT ... FROM v WHERE time >= ... AND time < ...

Сценарий 3 с UNION ALL:

CREATE VIEW v AS
  SELECT time, etc. FROM t1 // #1...
  UNION ALL
  SELECT time, etc. FROM t2 // #2...

Следующее работает медленно. Plan разбивает t1 и t2 (которые также были представлениями) и собирает их в виде большой серии союзов. Временные фильтры правильно применяются к отдельным компонентам, но все еще очень медленно:

SELECT ... FROM v WHERE time >= ... AND time < ...

Я был бы рад просто получить время примерное t1 плюс t2, но это было более чем вдвое. В этом случае мне помогло добавление подсказки parallel. Он упорядочил все по лучшему плану:

SELECT /*+ parallel */ ... FROM v WHERE time >= ... AND time < ...

Думаю, у меня не так много баллов, чтобы опубликовать это в качестве комментариев, поэтому я публикую это как ответ

Я не знаю, как PostgreSQL работает за кулисами, я думаю, вы можете получить ключ к разгадке, если бы это был Oracle, так что вот как будет работать Oracle.

Ваше представление UNION ALL работает медленнее, потому что записи из SELECT #1 и #2 сначала объединяются во временную таблицу, который создается на лету, а затем ваш SELECT ... FROM v WHERE time >= ... AND time ‹ ... выполняется для этой временной таблицы. Поскольку и #1, и #2 индексируются, они работают быстрее по отдельности, как и ожидалось, но эта временная таблица не индексируется (конечно), и выбираются окончательные записи. из этой временной таблицы, что приводит к более медленному ответу.

Теперь, по крайней мере, я не вижу способа сделать это быстрее + ​​просмотр + нематериализованный

Одним из способов, кроме запуска SELECT #1 и #2 и их явного объединения, может быть использование хранимой процедуры или функции на языке программирования вашего приложения. (если это так), и в этой процедуре вы делаете отдельные вызовы для каждой индексированной таблицы, а затем объединяете результаты, что не так просто, как SELECT ... FROM v WHERE time >= ... AND time ‹ ... :(