I have a query on Postgres, and I also have added proper index. Is there anything missing here?
SELECT orders.*, demo.name as d_name
FROM orders
LEFT JOIN users as demo ON demo.id = orders.dr_id
WHERE orders.customer_id = 526373
AND (orders.log_id = 300)
AND (orders.order_count_id IN (10, 1, 8, 2, 3))
LIMIT 5
Query plan from EXPLAIN ANALYZE
:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2490.30..2867.15 rows=3 width=182) (actual time=33.292..34.115 rows=3 loops=1)
-> Nested Loop Left Join (cost=2490.30..2867.15 rows=3 width=182) (actual time=33.291..34.113 rows=3 loops=1)
-> Bitmap Heap Scan on orders (cost=2490.02..2842.22 rows=3 width=172) (actual time=33.279..34.097 rows=3 loops=1)
Recheck Cond: ((customer_id = 526373) AND (order_count_id = ANY ('{10,1,8,2,3}'::integer[])))
Rows Removed by Index Recheck: 1046
Filter: (log_id = 300)
-> BitmapAnd (cost=2490.02..2490.02 rows=89 width=0) (actual time=33.107..33.107 rows=0 loops=1)
-> Bitmap Index Scan on idx_orders_customer_id (cost=0.00..146.54 rows=7747 width=0) (actual time=1.134..1.134 rows=6026 loops=1)
Index Cond: (customer_id = 526373)
-> Bitmap Index Scan on idx_orders_order_count_id (cost=0.00..2343.23 rows=126540 width=0) (actual time=31.018..31.018 rows=70883 loops=1)
Index Cond: (order_count_id = ANY ('{10,1,8,2,3}'::integer[]))
-> Index Scan using users_pkey on users demo (cost=0.28..8.30 rows=1 width=14) (actual time=0.003..0.003 rows=0 loops=3)
Index Cond: (id = orders.dr_id)
**Total runtime: 34.233 ms**
LIMIT
withoutORDER BY
, that is a bad practice because the results may change "randomly".orders
table? A composite index would be better:(customer_id, log_id, order_count)
LIMIT
)?