Re: Need help with one query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux