Hi to all,
I have the following query:
SELECT count(*) FROM orders o
INNER JOIN report r ON r.id_order=o.id
WHERE o.id_status>3
Explaing analyze:
Aggregate (cost=8941.82..8941.82 rows=1 width=0) (actual time=1003.297..1003.298 rows=1 loops=1)
-> Hash Join (cost=3946.28..8881.72 rows=24041 width=0) (actual time=211.985..951.545 rows=72121 loops=1)
Hash Cond: ("outer".id_order = "inner".id)
-> Seq Scan on report r (cost=0.00..2952.21 rows=72121 width=4) (actual time=0.005..73.869 rows=72121 loops=1)
-> Hash (cost=3787.57..3787.57 rows=24682 width=4) (actual time=211.855..211.855 rows=0 loops=1)
-> Seq Scan on orders o (cost=0.00..3787.57 rows=24682 width=4) (actual time=0.047..147.170 rows=72121 loops=1)
Filter: (id_status > 3)
Total runtime: 1003.671 ms
I could use it in the following format, because I have to the moment only the 4,6 values for the id_status.
SELECT count(*) FROM orders o
INNER JOIN report r ON r.id_order=o.id
WHERE o.id_status IN (4,6)
Explain analyze:
Aggregate (cost=5430.04..5430.04 rows=1 width=0) (actual time=1472.877..1472.877 rows=1 loops=1)
-> Hash Join (cost=2108.22..5428.23 rows=720 width=0) (actual time=342.080..1419.775 rows=72121 loops=1)
Hash Cond: ("outer".id_order = "inner".id)
-> Seq Scan on report r (cost=0.00..2952.21 rows=72121 width=4) (actual time=0.036..106.217 rows=72121 loops=1)
-> Hash (cost=2106.37..2106.37 rows=739 width=4) (actual time=342.011..342.011 rows=0 loops=1)
-> Index Scan using orders_id_status_idx, orders_id_status_idx on orders o (cost=0.00..2106.37 rows=739 width=4) (actual time=0.131..268.397 rows=72121 loops=1)
Index Cond: ((id_status = 4) OR (id_status = 6))
Total runtime: 1474.356 ms
How can I improve this query's performace?? The ideea is to count all the values that I have in the database for the following conditions. If the users puts in some other search fields on the where then the query runs faster but in this format sometimes it takes a lot lot of time(sometimes even 2,3 seconds).
Can this be tuned somehow???
Regards,
Andy.