0

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?

7
  • The queries are totally different. and they don't match the execution plans. Please fix the question. Apr 11 at 10:59
  • Sorry, wrong copy and paste. I've now fixed the question
    – laurent
    Apr 11 at 11:11
  • Thanks, but the execution plans still look wrong. The index scan is on user_id, and the slow query has no join with user_items. Apr 11 at 11:39
  • Ok again sorry, this time I got it right. I'm investigating two separate by similar queries and got them confused in the question.
    – laurent
    Apr 11 at 12:00
  • Thanks. This looks similar to this other question. Perhaps you can try to ORDER BY counter + 0. Apr 11 at 12:07

1 Answer 1

3

Presumably user_id 'xxxxxxx' is deficient in rows with a low value of "counter", so when it walks the counter index it has to go a very long way before finding 200 of them satisfying 'xxxxxxx' (plus the other condition). The PostgreSQL stats system has no way of knowing about such interactions.

Much better would be an index on (user_id, counter). This way it can jump right to the correct user_id and then scan just those, but already in order.

Even better would be a filtered index:

create index on changes (user_id, counter) where type = 1 OR type = 3

But that is getting into "one index per query" category, so I would stick to the more general one unless you really need the extra performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.