Пример запроса для отображения ошибки оценки мощности в PostgreSQL

Question

Я работаю над проектом с использованием PostgreSQL9.3. Я использовал приведенный ниже запрос, чтобы показать, как ошибки оценки избирательности могут привести к многократному увеличению времени выполнения запроса для рабочей нагрузки TPC-H с использованием PostgreSQL8.3.

select 
    n_name, 
    sum(l_extendedprice * (1 - l_discount)) as revenue 
from 
    customer, 
    orders, 
    lineitem, 
    supplier, 
    nation,
    region 
where 
    c_custkey = o_custkey 
    and l_orderkey = o_orderkey 
    and l_suppkey = s_suppkey   
    and c_nationkey = s_nationkey 
    and s_nationkey = n_nationkey 
    and n_regionkey = r_regionkey 
    and (r_name='ASIA' or r_name='AFRICA') 
    and o_orderdate >= date '1994-01-01' 
    and o_orderdate < date '1994-01-01' + interval '1 year' 
    and l_shipdate <= l_receiptdate 
    and l_commitdate <= l_shipdate + integer '90' 
    and l_extendedprice <= 20000 
    and c_name like '%r#00%' 
    and c_acctbal <=2400 
group by 
    n_name 
order by    
    revenue desc

Проблема заключалась в том, что PostgreSQL8.3 выбирал план, включающий множество соединений NestedLoop, поскольку оценка избирательности по элементу lineitem и клиенту была ошибочной с большим отрывом. Я думаю, что это в основном из-за сопоставления с шаблоном. Но оптимальный план должен был использовать хеш-соединения.

Недавно я обновил PostgreSQL9.3 для своего проекта и заметил, что приведенный выше запрос больше не дает плохой план. Я потратил некоторое количество времени, пытаясь найти запрос с большой ошибкой оценки мощности в данных 1 ГБ TPC-H, но безуспешно. Знает ли кто-нибудь из фанатов PostgreSQL какой-нибудь готовый запрос на тесте TPC-H или какой-либо запрос, чтобы показать ошибку оценки количества элементов в PostgreSQL9.3?

Answer 1

Это ответ на комментарий @Twelfth, а также на сам вопрос.

Три цитаты из этой главы в руководстве:
" Управление планировщиком с явным JOIN Пункты "

Явный синтаксис внутреннего соединения (INNER JOIN, CROSS JOIN или без украшений JOIN) семантически совпадает с перечислением входных отношений в FROM так что это не ограничивает порядок соединения.

...

Чтобы заставить планировщика следовать порядку соединения, изложенному явным JOIN s, установите join_collapse_limit параметр времени выполнения до 1. (Другие возможные значения обсуждаются ниже.)

...

Ограничение поиска планировщика таким способом является полезным методом как для сокращения времени планирования, так и для направления планировщика на хороший план запроса.

Жирный акцент мой. И наоборот, вы можете злоупотребить этим же, чтобы направить планировщик запросов в неверный план запросов для ваших целей тестирования. Прочитайте всю страницу руководства. Это должно быть инструментальным.

Кроме того, вы можете форсировать вложенные циклы, отключая альтернативные методы один за другим (лучше всего в вашей сессии). Подобно:

SET enable_hashjoin = off;

И т.п.
О проверке и настройке параметров:

• Запросить параметр (параметр postgresql.conf), например "max_connections"

Принудительно использовать фактические ошибки оценки

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

Статистика хранится в таблицах каталога pg_class а также pg_statistics,

SELECT * FROM pg_class WHERE oid = 'mytable'::regclass;
SELECT * FROM pg_statistic WHERE starelid = 'mytable'::regclass;

Это приводит меня к другому варианту. Вы можете подделать записи в этих двух таблицах. Требуются права суперпользователя.
Вы не новичок, а предупреждение для широкой публики: если вы что-то сломаете в таблицах каталога, ваша база данных (кластер) может обанкротиться. Вы были предупреждены.