Sample Header Ad - 728x90

Why is this Postgres query so slow?

0 votes
2 answers
102 views
I'm trying to debug the slow query below but I'm struggling to understand why it is slow. I can see that both plan and subplan do an index scan, including an "Index only scan" for the subplan so both should be fast. Yet it's taking 7 seconds for this particular query. Any idea from this EXPLAIN output where the problem might be?
select "id", "item_id", "item_name", "type", "updated_time" from "changes"
where (
  ((type = 1 OR type = 3) AND user_id = 'USER_ID')
  or type = 2 AND item_id IN (SELECT item_id FROM user_items WHERE user_id = 'USER_ID')
) and "counter" > '35885954' order by "counter" asc limit 100;
Limit  (cost=8409.70..8553.44 rows=100 width=101) (actual time=7514.730..7514.731 rows=0 loops=1)
   ->  Index Scan using changes_pkey on changes  (cost=8409.70..2387708.44 rows=1655325 width=101) (actual time=7514.728..7514.729 rows=0 loops=1)
         Index Cond: (counter > 35885954)
         Filter: ((((type = 1) OR (type = 3)) AND ((user_id)::text = 'USER_ID'::text)) OR ((type = 2) AND (hashed SubPlan 1)))
         Rows Removed by Filter: 11378536
         SubPlan 1
           ->  Index Only Scan using user_items_user_id_item_id_unique on user_items  (cost=0.56..8401.57 rows=3030 width=24) (actual time=0.085..3.011 rows=3589 loops=1)
                 Index Cond: (user_id = 'USER_ID'::text)
                 Heap Fetches: 2053
 Planning Time: 0.245 ms
 Execution Time: 7514.781 ms
(11 rows)
Asked by laurent (191 rep)
Oct 16, 2023, 06:16 PM
Last activity: Oct 18, 2023, 06:41 AM