Sample Header Ad - 728x90

Extremely slow query whenever using ORDER BY, even when an index is present

0 votes
1 answer
242 views
I've been trying to debug a particularly slow query that never completes (it takes forever and eventually timeout), and found out that it's down to the ORDER BY statement: if it's there, it never completes, if I remove it, it returns instantly. My assumption was that there was no index on that field, however I found out that there is one:
CREATE UNIQUE INDEX changes_pkey ON public.changes USING btree (counter)
However that doesn't seem to make any difference so I'm wondering what could be the reason? Is that perhaps because it's a "UNIQUE INDEX" unlike the other indexes on this table? Please see below for the queries: **Never complete:**
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND (type = 1 OR type = 3)
AND user_id = 'xxxxxxx'
ORDER BY "counter" ASC
LIMIT 200
**Completes instantly:**
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND (type = 1 OR type = 3)
AND user_id = 'xxxxxxx'
LIMIT 200
**Indexes on that table:**
changes              | changes_id_index                          | CREATE INDEX changes_id_index ON public.changes USING btree (id)
changes              | changes_id_unique                         | CREATE UNIQUE INDEX changes_id_unique ON public.changes USING btree (id)
changes              | changes_item_id_index                     | CREATE INDEX changes_item_id_index ON public.changes USING btree (item_id)
changes              | changes_pkey                              | CREATE UNIQUE INDEX changes_pkey ON public.changes USING btree (counter)
changes              | changes_user_id_index                     | CREATE INDEX changes_user_id_index ON public.changes USING btree (user_id)
postgres=> EXPLAIN SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
postgres-> FROM "changes"
postgres-> WHERE counter > -1
postgres-> AND (type = 1 OR type = 3)
postgres-> AND user_id = 'xxxxxxxx'
postgres-> ORDER BY "counter" ASC
postgres-> LIMIT 200;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..9206.44 rows=200 width=99)
   ->  Index Scan using changes_pkey on changes  (cost=0.56..5746031.01 rows=124834 width=99)
         Index Cond: (counter > '-1'::integer)
         Filter: (((user_id)::text = 'xxxxxxxx'::text) AND ((type = 1) OR (type = 3)))
(4 rows)
***EXPLAIN for the fast query:**
postgres=> EXPLAIN SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
postgres-> FROM "changes"
postgres-> WHERE counter > -1
postgres-> AND (type = 1 OR type = 3)
postgres-> AND user_id = 'xxxxxxxx'
postgres-> LIMIT 200;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..1190.09 rows=200 width=99)
   ->  Index Scan using changes_user_id_index on changes  (cost=0.56..742468.10 rows=124834 width=99)
         Index Cond: ((user_id)::text = 'xxxxxxxx'::text)
         Filter: ((counter > '-1'::integer) AND ((type = 1) OR (type = 3)))
(4 rows)
Any idea what could be the reason for this slow query?
Asked by laurent (191 rep)
Apr 11, 2024, 10:42 AM
Last activity: Apr 11, 2024, 04:28 PM