Почему медленная выборка в партицированных таблицах Postgresql?

Question

[jonasas]

Доброго времени суток!
Я использую Postgresql 9.5. Настройки из коробки.
Разбил таблицу на ~7000 партиций. Вставил одну запись.
Когда делаю SELECT * FROM "Offer";, запрос длится 1,5 сек.
Когда делаю SELECT * FROM "Offer" WHERE bid=4793;, где bid -- ограничение партиционирования (по одной таблице на каждое значение bid), запрос длится 1 секунду.

Вот EXPLAIN ANALYZE для второго варианта:

Append  (cost=0.00..12.14 rows=2 width=596) (actual time=0.014..0.014 rows=1 loops=1)
  ->  Seq Scan on "Offer"  (cost=0.00..1.01 rows=1 width=344) (actual time=0.011..0.011 rows=0 loops=1)
        Filter: (bid = 4793)
        Rows Removed by Filter: 1
  ->  Seq Scan on "Offer-4793"  (cost=0.00..11.12 rows=1 width=848) (actual time=0.002..0.002 rows=1 loops=1)
        Filter: (bid = 4793)
Planning time: 996.243 ms
Execution time: 0.261 ms

Почему так долго? Чем можно профилировать это всё?
У меня есть единственное предположение -- postgresql не держит в озу условия ограничений своих партиций и каждый раз читает с диска.

Очень рассчитываю на помощь!

UPDATE:
Ещё мне посоветовали каскадное партицирование (у основной таблицы 10 детей, у каждого из которых тоже по десять детей итд). Результат не лучше:

Append  (cost=0.00..12.24 rows=5 width=848) (actual time=0.013..0.013 rows=1 loops=1)
  ->  Seq Scan on "Offer"  (cost=0.00..1.11 rows=1 width=848) (actual time=0.006..0.006 rows=0 loops=1)
    Filter: (bid = 4793)
  ->  Seq Scan on "Offer-ddd-3"  (cost=0.00..0.00 rows=1 width=848) (actual time=0.001..0.001 rows=0 loops=1)
    Filter: (bid = 4793)
  ->  Seq Scan on "Offer-dd-33"  (cost=0.00..0.00 rows=1 width=848) (actual time=0.000..0.000 rows=0 loops=1)
    Filter: (bid = 4793)
  ->  Seq Scan on "Offer-d-336"  (cost=0.00..0.00 rows=1 width=848) (actual time=0.000..0.000 rows=0 loops=1)
    Filter: (bid = 4793)
  ->  Seq Scan on "Offer-4793"  (cost=0.00..11.12 rows=1 width=848) (actual time=0.006..0.006 rows=1 loops=1)
    Filter: (bid = 4793)
Planning time: 1449.872 ms
Execution time: 0.354 ms

Answer 1

[Артём Клименко]

а у вас индексы по этим полям в дочерних таблицах есть?

https://www.postgrespro.ru/doc/ddl-inherit.html#DD...

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

Comments

[jonasas]

Так а для чего тут индекс, когда в каждой таблице может быть только одно значение этого поля?

[Артём Клименко]

jonasas: общие ограничения через CHECK не являются индексами, они именно общие, соответственно планировщик ничего об этом не знает, он не знает где и какие хранятся значения, поэтому и делает полное сканирование таблицы, создайте индекс и будет вам счастье, сами индексы при этом не наследуются от основной таблицы, их именно нужно создавать!

[jonasas]

Артём Клименко: "делает полное сканирование таблицы" какой таблицы то?) Поиск партиций идёт вне таблиц.

"индексы при этом не наследуются от основной таблицы" это зависит от того, как создавать партиции. Я делаю полные копии (PK индекс копируется)

[Артём Клименко]

jonasas: Если запрос делается на родительскую таблицу то запрос происходит по родительской и всем дочерним таблицам.

[jonasas]

Артём Клименко: Так прежде чем приступить к поиску по таблице (ну и по индексу в том числе), postgresql ищет ту самую таблицу по CHECK, а потом уже работает с родительской и партицией. В ANALYZE видно, что поиск происходит по родительской таблице Offer и партиции Offer-4793. Родительская таблица Offer пуста, а в таблице Offer-4793 всего одно значение для bid, по которому идёт поиск. А так в тесте вообще всего одна запись.

[Артём Клименко]

jonasas: хм, таки да был невнимательным.
>Настройки из коробки.
тоесть даже потребление памяти? по умолчанию постгре настроен так чтоб запуститься даже на калькуляторе, и он очень медленный. по настройке есть хорошая книга postgresql.leopard.in.ua

[jonasas]

Артём Клименко: спасибо, изучу.

Answer 2

[Melkij]

Покажите explain analyze. Судя по cost в explain, само выполнение запроса корректно.

Рабочая версия - время жрет сам планировщик. 7к партиций - это очень много. Об этом есть даже заметка в мануале (о чём там только нет заметок, но попробуй их заметь)

All constraints on all partitions of the master table are examined during constraint exclusion, so large numbers of partitions are likely to increase query planning time considerably. Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousands of partitions.

https://www.postgresql.org/docs/9.4/static/ddl-par...

У postgresql партицирование сделано довольно странно и, вообще-то говоря, не предназначенными для этого средствами. Если у вас достаточно большая таблица, чтобы почувствовать бонусы от партицирования, то лучше ограничьтесь парой десятков разделов. Вот ещё статья тематическая есть: https://habrahabr.ru/post/273933/

Comments

[jonasas]

Melkij, Обновил.
По этой статье (на хабре) я начинал изучение партицирования в PostgreSQL.
Вот как ускорить планировщик?

[Melkij]

Ну да, фактически всё время - это Planning time. Что с ним делать - не использовать много партиций и ждать. Конкретных релизов пока нет, но в сторону более нормального партицирования разработчики движутся.

А зачем вам партицирование вообще? Что хотите его использованием добиться? Основные причины:
- сокращение размера индексов
- простые и лёгкие удаление и очищение ненужных разделов

По второму пункту добавить мне нечего - с такими задачами не сталкивался, что нужно удалять много данных, часто и быстро.
По первому - можете использовать partial index https://www.postgresql.org/docs/9.4/static/indexes...

[jonasas]

Melkij: У меня очень много данных, по которым должна быть быстрая вставка и быстрый поиск. А так как для этого нужен индекс, скорость вставки при росте таблицы экспоненциально снижается. Поэтому я решил избавится от индексирования по одному полю, сделав на каждое значение этого поля по таблице.

По поводу партиционных индексов. Это походу то что надо! Про партицирование таблиц я знал. О партицировании индексов догадывался, но беглым поиском ничего не нашёл. Спасибо за ссылку, буду изучать.

Answer 3

[shagguboy]

https://www.postgresql.org/docs/9.5/static/ddl-par...

All constraints on all partitions of the master table are examined during constraint exclusion, so large numbers of partitions are likely to increase query planning time considerably. Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousands of partitions.

Comments

[jonasas]

А какая тут алгоритмическая сложность? Вроде как я где-то читал, что O(log(n)). Но судя по результатам, она экспоненциальная. Если судить самому, то планировщику необходимо проверить условие в каждой партиции, следовательно сложность должна быть линейной.