5

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**
7
  • 3
    You are using LIMIT without ORDER BY, that is a bad practice because the results may change "randomly". – jkavalik May 22 '16 at 13:11
  • 2
    What indexes ar ethere on the orders table? A composite index would be better: (customer_id, log_id, order_count) – ypercubeᵀᴹ May 22 '16 at 13:19
  • And when you say slow, what do you mean? How much time does the query take? How big are the tables? How many rows match the criteria (and would be returned without the LIMIT)? – ypercubeᵀᴹ May 22 '16 at 13:21
  • 4
    I don't consider 34 milliseconds to be slow. How fast do you need it to be? – a_horse_with_no_name May 22 '16 at 13:23
  • 2
    Information in the question is incomplete. Please consider instructions for PostgreSQL performance questions. – Erwin Brandstetter May 22 '16 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.