Después de investigar por qué un índice de varias columnas no ayuda a acelerar una consulta cuando lo esperaba, me di cuenta de que se debe a una simple cláusula ORDER BY.
Reduje la consulta a esta forma simple (primero sin el ORDEN POR, luego con él):
somedb=# explain select * from user_resource where resource_id = 943 and status = 2 limit 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Limit (cost=0.56..39.29 rows=10 width=44) -> Index Scan using user_resource_resource_id_status on user_resource (cost=0.56..5422.22 rows=1400 width=44) Index Cond: ((resource_id = 943) AND (status = 2)) (3 rows) Time: 0.409 ms somedb=# explain select * from user_resource where resource_id = 943 and status = 2 order by id desc limit 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Limit (cost=1000.46..4984.60 rows=10 width=44) -> Gather Merge (cost=1000.46..558780.31 rows=1400 width=44) Workers Planned: 2 -> Parallel Index Scan Backward using idx_121518_primary on user_resource (cost=0.44..557618.69 rows=583 width=44) Filter: ((resource_id = 943) AND (status = 2)) Una vez que agrego ORDER BY, puede ver que la clave user_resource_resource_id_status ya no se usa y la consulta se vuelve aproximadamente 10 veces más lenta.
¿Por qué es esto? y hay alguna forma de arreglarlo? Creo que ordenar por un campo entero simple no debería hacer que un índice sea inútil. Gracias.
Depende de cómo haya creado el index . Ejemplo NULLS FIRST , ASC, DESC, NULLS FIRST, and/or NULLS LAST
Consulte https://www.postgresql.org/docs/current/indexes-ordering.html , explica cómo trabajar con Indexes and ORDER BY
Está relacionado con el limit .
Puede ejecutar la consulta sin la cláusula de límite y con un desplazamiento de 0 para evitar insertar la subconsulta y luego aplicar el límite.
select * from ( select * from user_resource where resource_id = 943 and status = 2 offset 0 ) sub order by id desc limit 10;