Message-ID: <49C2AC75.5060601@collab.net>
Date: Thu, 19 Mar 2009 13:35:01 -0700
From: Anne Rosset <arosset@collab.net>
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Need help with one query
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,
We have the following 2  tables:
\d audit_change
             Table "public.audit_change"
     Column     |          Type          | Modifiers
----------------+------------------------+-----------
 id             | character varying(32)  | not null
audit_entry_id | character varying(32)  |
...
Indexes:
    "audit_change_pk" primary key, btree (id)
    "audit_change_entry" btree (audit_entry_id)

and
\d audit_entry;
              Table "public.audit_entry"
     Column     |           Type           | Modifiers
----------------+--------------------------+-----------
 id             | character varying(32)    | not null
 object_id      | character varying(32)    | not null
...
Indexes:
    "audit_entry_pk" primary key, btree (id)
      "audit_entry_object" btree (object_id)


We do the following query:
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 
PLAN                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------
 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
(7 rows)


Why does the query not use the index on audit_entry_id and do a seq scan 
(as you see the table has many rows)?


If we split the query into 2 queries, it only takes less than 0.3 ms
EXPLAIN ANALYZE select * from audit_entry WHERE 
audit_entry.object_id='artf414029';
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using audit_entry_object on audit_entry  (cost=0.00..8.75 
rows=3 width=111) (actual time=0.037..0.044 rows=4 loops=1)
   Index Cond: ((object_id)::text = 'artf414029'::text)
 Total runtime: 0.073 ms
(3 rows)

EXPLAIN ANALYZE select * from audit_change WHERE audit_entry_id in 
('adte1DDFEA5B011C8988C3928752', 'adte5DDFEA5B011D441230BD20CC', 
'adte5DDFEA5B011E40601E8DA10F', 'adte5DDFEA5B011E8CC26071627C') ORDER BY 
property_name ASC;
                                                                                                    
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=30.25..30.27 rows=10 width=123) (actual time=0.190..0.192 
rows=4 loops=1)
   Sort Key: property_name
   ->  Bitmap Heap Scan on audit_change  (cost=9.99..30.08 rows=10 
width=123) (actual time=0.173..0.177 rows=4 loops=1)
         Recheck Cond: ((audit_entry_id)::text = ANY 
(('{adte1DDFEA5B011C8988C3928752,adte5DDFEA5B011D441230BD20CC,adte5DDFEA5B011E40601E8DA10F,adte5DDFEA5B011E8CC26071627C}'::character 
varying[])::text[]))
         ->  Bitmap Index Scan on audit_change_entry  (cost=0.00..9.99 
rows=10 width=0) (actual time=0.167..0.167 rows=4 loops=1)
               Index Cond: ((audit_entry_id)::text = ANY 
(('{adte1DDFEA5B011C8988C3928752,adte5DDFEA5B011D441230BD20CC,adte5DDFEA5B011E40601E8DA10F,adte5DDFEA5B011E8CC26071627C}'::character 
varying[])::text[]))
 Total runtime: 0.219 ms
(7 rows)

Thanks for your help,
Anne