From: Richard Huxton <dev@archonet.com>
To: Anne Rosset <arosset@collab.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need help with one query
Date: Fri, 20 Mar 2009 09:21:18 +0000
Message-ID: <49C3600E.20504@archonet.com> (raw)
In-Reply-To: <49C2AC75.5060601@collab.net>
References: <49C2AC75.5060601@collab.net>
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
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: dev@archonet.com, arosset@collab.net
Subject: Re: Need help with one query
In-Reply-To: <49C3600E.20504@archonet.com>
* 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