Re: Need help with one query

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