performance - Consulta de PostgreSQL muy lenta con límite 1

limit

Question

Mis consultas se vuelven muy lentas cuando agrego un limit 1 .

Tengo una tabla object_values con valores timestamped para objetos:

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

Por objeto quiero obtener el último valor:

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

(He cancelado la consulta después de más de 10 minutos)

Esta consulta es muy lenta cuando no hay valores para un objeto ID determinado (es rápido si hay resultados). Si elimino el límite, me dice casi instantáneamente que no hay resultados:

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

Una explicación me muestra que la consulta sin límite utiliza el índice, mientras que la consulta con el limit 1 no hace uso del índice:

Consulta lenta:

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)`

Consulta rápida:

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)

La tabla contiene 44,884,559 filas y 66,762 ID de objetos distintos.
Tengo índices separados en ambos campos: timestamp y objectID .
He hecho un vacuum analyze en la tabla y he vuelto a indexar la tabla.

Además, la consulta lenta se vuelve rápida cuando configuro el límite en 3 o más:

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)

En general, asumo que tiene que ver con que el planificador haga suposiciones erróneas sobre los costos de ejecución y, por lo tanto, opte por un plan de ejecución más lento.

¿Es esta la verdadera razón? ¿Existe alguna solucion para esto?

Answer 1

Puede evitar este problema agregando una cláusula ORDER BY innecesaria a la consulta.

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

Answer 2

Te encuentras con un problema que se relaciona, creo, con la falta de estadísticas sobre las correlaciones de filas. Considere reportarlo a pg-bugs para referencia si está usando la última versión de Postgres.

La interpretación que sugeriría para tus planes es:

• limit 1 hace que Postgres busque una sola fila, y al hacerlo asume que su object_id es lo suficientemente común como para que se muestre de manera razonablemente rápida en una exploración de índice.

  Basándose en las estadísticas que le diste a pensar, es probable que tenga que leer ~ 70 filas en promedio para encontrar una fila que se ajuste; simplemente no se da cuenta de que object_id y timestamp se correlacionan con el punto en el que realmente va a leer una gran parte de la tabla.

• limit 3 , por el contrario, hace que se dé cuenta de que es poco común, por lo que considera seriamente (y termina ...) la clasificación n de las 1700 filas esperadas con el object_id que desea, por el hecho de que hacerlo es más barato.

  Por ejemplo, podría saber que la distribución de estas filas es para que todas estén empaquetadas en la misma área del disco.

• la cláusula sin limit significa que recuperará los 1700 de todos modos, por lo que va directo al índice en object_id .

Solución, por cierto: agregar un índice en (object_id, timestamp) o (object_id, timestamp desc) .