From: Andy <frum@ar-sd.net>
To: pgsql-performance@postgresql.org
Subject: Improving Inner Join Performance
Date: Thu, 5 Jan 2006 17:16:47 +0200
Message-ID: <001d01c6120b$0d783600$0b00a8c0@forge> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: frum@ar-sd.net
Subject: Re: Improving Inner Join Performance
In-Reply-To: <001d01c6120b$0d783600$0b00a8c0@forge>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox