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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
