0

I have a SQL with multiple join left, because some data is empty. However, I need to sort the data using a string column from a join table.

I thought about creating an index for this table, but this index does not affect this SQL. How can I lower the cost of this SQL?

SELECT
*
FROM 
sales s 
LEFT JOIN salesman sm ON s.idsalesman = sm.id AND sm.account = s.account
WHERE s.situation = 2
AND s.account = 123
ORDER BY sm.name DESC
OFFSET 0 
LIMIT 30;

CREATE INDEX idx_salesman_name ON salesman (name  NULLS FIRST);

This is the Explain Analyze:

Limit  (cost=5228.94..5229.01 rows=30 width=2153) (actual time=7.435..7.459 rows=30 loops=1)
   ->  Sort  (cost=5228.94..5240.47 rows=4615 width=2153) (actual time=7.434..7.455 rows=30 loops=1)
         Sort Key: sm.name DESC
         Sort Method: top-N heapsort  Memory: 87kB
         ->  Hash Left Join  (cost=36.57..5092.64 rows=4615 width=2153) (actual time=0.992..6.634 rows=153 loops=1)
               Hash Cond: ((s.account = sm.account) AND (s.idsalesman = sm.id))
               ->  Index Scan using idx_sales_conta_situation on sales s  (cost=0.43..5021.83 rows=4615 width=1252) (actual time=0.096..5.489 rows=153 loops=1)
                     Index Cond: ((account = 123) AND (situation = 2))
               ->  Hash  (cost=35.33..35.33 rows=54 width=901) (actual time=0.811..0.811 rows=55 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 30kB
                     ->  Index Scan using idx_salesman_account on salesman sm  (cost=0.29..35.33 rows=54 width=901) (actual time=0.052..0.748 rows=55 loops=1)
                           Index Cond: (account = 123)
 Planning time: 5.513 ms
 Execution time: 7.854 ms
(14 rows)

PostgreSQL Version: 9.5.14

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.