6

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, 2016 at 13:11
  • 2
    What indexes ar ethere on the orders table? A composite index would be better: (customer_id, log_id, order_count) May 22, 2016 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)? May 22, 2016 at 13:21
  • 5
    I don't consider 34 milliseconds to be slow. How fast do you need it to be? May 22, 2016 at 13:23
  • 2
    Information in the question is incomplete. Please consider instructions for PostgreSQL performance questions. May 22, 2016 at 15:25

0

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.