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
Last activity: Apr 11, 2024, 04:28 PM