> If the users puts in some other search fields on the where then the query runs faster but > in this format sometimes
ittakes a lot lot of time(sometimes even 2,3 seconds).
Can you eloborate under what conditions which query is slower?
On 1/5/06, Andy <frum@ar-sd.net> wrote:
>
> 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.
>
>