Skip site navigation (1) Skip section navigation (2)

Re: Need help with one query

From: Richard Huxton <dev(at)archonet(dot)com>
To: Anne Rosset <arosset(at)collab(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Need help with one query
Date: 2009-03-20 09:21:18
Message-ID: 49C3600E.20504@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: ml@bortal.deDate: 2009-03-20 09:26:29
Subject: current transaction in productive database
Previous:From: Nimesh SatamDate: 2009-03-20 09:03:58
Subject: Re: Prepared statement does not exist

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group