Yes I have indexes an all join fields.
The tables have around 30 columns each and around 100k rows.
The database is vacuumed every hour.
Andy.
----- Original Message -----
From: "Frank Wiles" <frank@xxxxxxxxx>
To: "Andy" <frum@xxxxxxxxx>
Cc: <pgsql-performance@xxxxxxxxxxxxxx>
Sent: Thursday, January 05, 2006 9:20 PM
Subject: Re: [PERFORM] Improving Inner Join Performance
On Thu, 5 Jan 2006 17:16:47 +0200
"Andy" <frum@xxxxxxxxx> 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
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???
Do you have an index on report.id_order ? Try creating an index for
it if not and run a vacuum analyze on the table to see if it gets
rid of the sequence scan in the plan.
---------------------------------
Frank Wiles <frank@xxxxxxxxx>
http://www.wiles.org
---------------------------------
