Запрос PostgreSQL очень медленный с лимитом 1

Мои запросы становятся очень медленными, когда я добавляю limit 1.

У меня есть таблица object_values с временными значениями для объектов:

 timestamp |  objectID |  value
--------------------------------
 2014-01-27|       234 | ksghdf

На объект Я хочу получить последнее значение:

SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC LIMIT 1;

(я отменил запрос после более 10 минут)

Этот запрос очень медленный, когда нет значений для данного объекта ID (это быстро, если есть результаты). Если я удалю предел, это говорит мне почти мгновенно, что результатов нет:

SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC;  
...  
Time: 0.463 ms

Объяснение показывает мне, что запрос без ограничений использует индекс, где, поскольку запрос с limit 1 не использует индекс:

Медленный запрос:

explain SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC limit 1;  
QUERY PLAN`
----------------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.00..2350.44 rows=1 width=126)
->  Index Scan Backward using object_values_timestamp on object_values  (cost=0.00..3995743.59 rows=1700 width=126)
     Filter: (objectID = 53708)`

Быстрый запрос:

explain SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Sort  (cost=6540.86..6545.11 rows=1700 width=126)
   Sort Key: timestamp
   ->  Index Scan using object_values_objectID on working_hours_t  (cost=0.00..6449.65 rows=1700 width=126)
         Index Cond: (objectID = 53708)

Таблица содержит 44 884 559 строк и 66 762 различных идентификатора объекта.
У меня есть отдельные индексы в обоих полях: timestamp и objectID.
Я сделал vacuum analyze в таблице, и я переиндексировал таблицу.

Кроме того, медленный запрос становится быстрым, когда я устанавливаю лимит на 3 или выше:

explain SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC limit 3;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Limit  (cost=6471.62..6471.63 rows=3 width=126)
   ->  Sort  (cost=6471.62..6475.87 rows=1700 width=126)
         Sort Key: timestamp
         ->  Index Scan using object_values_objectID on object_values  (cost=0.00..6449.65 rows=1700 width=126)
               Index Cond: (objectID = 53708)

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

Это настоящая причина? Есть ли решение для этого?

Ответы

Ответ 1

Вы сталкиваетесь с проблемой, которая, как мне кажется, связана с отсутствием статистики по корреляциям строк. Подумайте о том, чтобы сообщить об этом pg-bugs для справки, если используется последняя версия Postgres.

Интерпретация, которую я предлагаю для ваших планов:

  • limit 1 заставляет Postgres искать одну строку, и при этом предполагается, что ваш object_id достаточно распространен, что он будет достаточно быстро обнаруживаться в сканировании индекса.

    Основываясь на статистике, которую вы дали, возможно, это то, что в среднем нужно найти ~ 70 строк, чтобы найти одну строку, которая подходит; он просто не понимает, что object_id и timestamp соотносятся с точкой, где она действительно будет читать большую часть таблицы.

  • limit 3, напротив, позволяет понять, что это необычно, поэтому он серьезно рассматривает (и заканчивает...) top-n сортирует ожидаемые 1700 строк с object_id, которые вы хотите, на том основании, что делать это, вероятно, дешевле.

    Например, он может знать, что распределение этих строк таково, что все они упакованы в одну и ту же область на диске.

  • no limit означает, что он будет получать 1700 в любом случае, поэтому он идет прямо для индекса на object_id.

Решение, btw: добавьте индекс на (object_id, timestamp) или (object_id, timestamp desc).

Ответ 2

Вы можете избежать этой проблемы, добавив в запрос ненулевое предложение ORDER BY.

SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp, objectID DESC limit 1;