Message-ID: <49C3600E.20504@archonet.com>
Date: Fri, 20 Mar 2009 09:21:18 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.19 (X11/20090105)
MIME-Version: 1.0
To: Anne Rosset <arosset@collab.net>
CC: pgsql-performance@postgresql.org
Subject: Re: Need help with one query
References: <49C2AC75.5060601@collab.net>
In-Reply-To: <49C2AC75.5060601@collab.net>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Anne Rosset wrote:
> EXPLAIN ANALYZE
> SELECT
>   audit_change.id             AS id,
>   audit_change.audit_entry_id AS auditEntryId,
>   audit_entry.object_id       AS objectId,
>   audit_change.property_name  AS propertyName,
>   audit_change.property_type  AS propertyType,
>   audit_change.old_value      AS oldValue,
>   audit_change.new_value      AS newValue,
>   audit_change.flexfield      AS flexField
> FROM
>   audit_entry audit_entry, audit_change audit_change
> WHERE
>   audit_change.audit_entry_id = audit_entry.id
>   AND audit_entry.object_id = 'artf414029';
[query reformatted to make it more readable]

Not quite clear why you are aliasing the tables to their own names...

> ---------------------------------------------------------------------------------------------------------------------------------------------
> 
> Hash Join  (cost=8.79..253664.55 rows=4 width=136) (actual
> time=4612.674..6683.158 rows=4 loops=1)
>   Hash Cond: ((audit_change.audit_entry_id)::text = (audit_entry.id)::text)
>   ->  Seq Scan on audit_change  (cost=0.00..225212.52 rows=7584852
> width=123) (actual time=0.009..2838.216 rows=7584852 loops=1)
>   ->  Hash  (cost=8.75..8.75 rows=3 width=45) (actual time=0.049..0.049
> rows=4 loops=1)
>         ->  Index Scan using audit_entry_object on audit_entry 
> (cost=0.00..8.75 rows=3 width=45) (actual time=0.033..0.042 rows=4 loops=1)
>               Index Cond: ((object_id)::text = 'artf414029'::text)
> Total runtime: 6683.220 ms

Very odd. It knows the table is large and that the seq-scan is going to
be expensive.

Try issuing "set enable_seqscan = off" and run the explain analyse
again. That should show the cost of using the indexes.

-- 
  Richard Huxton
  Archonet Ltd