alex1 is a table with 47 million rows and a rule that deletes from another
table whenever an insert is made into alex1. Insertions into alex1 are
causing
a seq scan that takes a very long time:
"EXPLAIN insert into alex1 (fileid, archiveset) select 35352974, 10003;"
(EXPLAIN output is at the bottom of this post)
Interestingly, quoting the values causes the plannner to use an index scan,
making the operation several orders of magnitude faster:
"EXPLAIN insert into alex1 (fileid, archiveset) select '35352974',
'10003';"
(EXPLAIN output is at the bottom of this post)
Can someone please tell me how to write the insert statement without the
quotes, but
still executes quickly?
Some additional information:
All search columns have a b-tree index.
Relevant parameter values:
effective_cache_size = 16GB
cpu_operator_cost = 0.0025
cpu_tuple_cost = 0.01
cursor_tuple_fraction = 0.1
enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_material = on
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on
This line from the EXPLAIN output suggests that the rule is causing a
seq scan
to be used instead on an index scan:
... WHERE ... a.archiveset = new.archiveset AND a.fileid <> new.fileid.
When I replace the "new." with actual integers:
... WHERE ... a.archiveset = 10003 AND a.fileid <> 35352974,
the problem goes away.
That makes me think that the planner is unable to find the index, so it
instead
uses a seq scan.
alex1 table:
Table "public.alex1"
Column | Type | Modifiers | Storage | Description
------------+--------------------------+-----------+---------+-------------
fileid | integer | | plain |
archiveset | integer | | plain |
lastchange | timestamp with time zone | | plain |
Indexes:
"pk_alex1_archiveset" btree (archiveset)
"pk_alex1_archiveset_lastchange" btree (archiveset, lastchange)
"pk_alex1_fileid" btree (fileid)
"pk_alex1_fileid_archiveset" btree (fileid, archiveset)
"pk_alex1_lastchange" btree (lastchange)
Rules:
alex_rule AS
ON INSERT TO alex1 DO DELETE FROM alex1 fa
WHERE ((fa.fileid, fa.archiveset) IN ( SELECT a.fileid, a.archiveset
FROM alex1 a
JOIN filemeta m ON m.fileid = a.fileid
JOIN filemeta o ON o.esdt::text = m.esdt::text AND o.key::text =
m.key::text
WHERE o.fileid = new.fileid AND a.archiveset = new.archiveset AND
a.fileid <> new.fileid))
Has OIDs: no
filemeta table:
Table "public.filemeta"
Column | Type | Modifiers | Storage | Description
--------+-------------------+-----------+----------+----------------------------------------------------------
fileid | integer | not null | plain | The unique SIPS
generated file identifier
esdt | character varying | | extended | The Earth Science Data
Type indicates the file contents
key | character varying | | extended |
source | character varying | | extended |
Indexes:
"pk_filemeta" PRIMARY KEY, btree (fileid)
"ak_filemeta_esdt" btree (esdt)
"ak_filemeta_esdt_fileid" btree (fileid, esdt)
"ak_filemeta_esdt_key" btree (esdt, key)
"ak_filemeta_fileid" btree (fileid)
"ak_filemeta_key" btree (key)
"ak_filemeta_source" btree (source)
Foreign-key constraints:
"fk_filemeta_esdt" FOREIGN KEY (esdt) REFERENCES esdt_def(esdt)
DEFERRABLE
"fk_filemeta_file" FOREIGN KEY (fileid) REFERENCES file(fileid) ON
DELETE CASCADE
"fk_filemeta_source" FOREIGN KEY (source) REFERENCES source_def(source)
Child tables: filemeta_anc,
filemeta_app,
filemeta_clg_aux,
filemeta_l0,
filemeta_l0r,
filemeta_mdkey,
filemeta_ompslookup,
filemeta_orbital,
filemeta_timerange
Has OIDs: no
===== SLOW INSERT (without quotes)
==================================================================
explain insert into alex1 (fileid, archiveset) select 35352974, 10003;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Insert on alex1 (cost=0.00..0.02 rows=1 width=8)
-> Subquery Scan on "*SELECT*" (cost=0.00..0.02 rows=1 width=8)
-> Result (cost=0.00..0.01 rows=1 width=0)
Delete on alex1 fa (cost=0.00..1023312671395.36 rows=23786988 width=38)
-> Nested Loop (cost=0.00..1023312671395.36 rows=23786988 width=38)
Join Filter: (SubPlan 1)
-> Subquery Scan on "*SELECT*" (cost=0.00..0.02 rows=1 width=40)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Seq Scan on alex1 fa (cost=0.00..732896.76 rows=47573976
width=14)
SubPlan 1
-> Nested Loop (cost=14.98..43004.15 rows=263 width=8)
-> Nested Loop (cost=14.98..12534.34 rows=13493 width=4)
Join Filter: (((m.esdt)::text = (o.esdt)::text)
AND ((m.key)::text = (o.key)::text))
-> Append (cost=0.00..37.68 rows=13 width=45)
-> Index Scan using ak_filemeta_fileid on
filemeta o (cost=0.00..4.29 rows=1 width=64)
Index Cond: (fileid =
"*SELECT*"."?column?")
-> Index Scan using pk_filemeta_anc on
filemeta_anc o (cost=0.00..3.28 rows=1 width=34)
Index Cond: (fileid =
"*SELECT*"."?column?")
-> Bitmap Heap Scan on filemeta_app o
(cost=1.77..4.34 rows=2 width=64)
Recheck Cond: (fileid =
"*SELECT*"."?column?")
-> Bitmap Index Scan on
ak_filemeta_app_fileid (cost=0.00..1.77 rows=2 width=0)
Index Cond: (fileid =
"*SELECT*"."?column?")
-> Bitmap Heap Scan on filemeta_clg_aux
o (cost=1.77..5.49 rows=3 width=64)
Recheck Cond: (fileid =
"*SELECT*"."?column?")
-> Bitmap Index Scan on
ak_filemeta_clg_aux_fileid_esdt (cost=0.00..1.77 rows=3 width=0)
Index Cond: (fileid =
"*SELECT*"."?column?")
-> Index Scan using pk_filemeta_l0 on
filemeta_l0 o (cost=0.00..3.28 rows=1 width=30)
Index Cond: (fileid =
"*SELECT*"."?column?")
-> Index Scan using pk_filemeta_l0r on
filemeta_l0r o (cost=0.00..3.27 rows=1 width=32)
Index Cond: (fileid =
"*SELECT*"."?column?")
-> Index Scan using
ak_filemeta_mdkey_fileid on filemeta_mdkey o (cost=0.00..3.31 rows=1
width=21)
Index Cond: (fileid =
"*SELECT*"."?column?")
-> Index Scan using
filemeta_ompslookup_pkey on filemeta_ompslookup o (cost=0.00..3.27
rows=1 width=35)
Index Cond: (fileid =
"*SELECT*"."?column?")
-> Index Scan using pk_filemeta_orbital
on filemeta_orbital o (cost=0.00..3.48 rows=1 width=14)
Index Cond: (fileid =
"*SELECT*"."?column?")
-> Index Scan using pk_filemeta_timerange
on filemeta_timerange o (cost=0.00..3.67 rows=1 width=34)
Index Cond: (fileid =
"*SELECT*"."?column?")
-> Append (cost=14.98..951.76 rows=635 width=68)
-> Bitmap Heap Scan on filemeta m
(cost=14.98..922.36 rows=624 width=68)
Recheck Cond: (((esdt)::text =
(o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Bitmap Index Scan on
ak_filemeta_esdt_key (cost=0.00..14.83 rows=624 width=0)
Index Cond: (((esdt)::text =
(o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using ak_filemeta_anc_key
on filemeta_anc m (cost=0.00..3.30 rows=1 width=38)
Index Cond: ((key)::text =
(o.key)::text)
-> Index Scan using
ak_filemeta_app_esdt_key on filemeta_app m (cost=0.00..1.31 rows=1
width=68)
Index Cond: (((esdt)::text =
(o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using
ak_filemeta_clg_aux_fileid_esdt on filemeta_clg_aux m (cost=0.00..5.82
rows=3 width=68)
Index Cond: ((esdt)::text =
(o.esdt)::text)
-> Index Scan using
ak_filemeta_l0_esdt_key on filemeta_l0 m (cost=0.00..3.30 rows=1 width=34)
Index Cond: (((esdt)::text =
(o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using
ak_filemeta_l0r_esdt_key on filemeta_l0r m (cost=0.00..2.35 rows=1
width=36)
Index Cond: (((esdt)::text =
(o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using
ak_filemeta_mdkey_esdt_key on filemeta_mdkey m (cost=0.00..3.35 rows=1
width=25)
Index Cond: (((esdt)::text =
(o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using
ak_filemeta_ompslookup_esdt_key on filemeta_ompslookup m
(cost=0.00..2.12 rows=1 width=39)
Index Cond: (((esdt)::text =
(o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using
ak_filemeta_orbital_esdt_key on filemeta_orbital m (cost=0.00..3.60
rows=1 width=18)
Index Cond: (((esdt)::text =
(o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using
ak_filemeta_timerange_esdt_key on filemeta_timerange m (cost=0.00..4.25
rows=1 width=38)
Index Cond: (((esdt)::text =
(o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using pk_alex1_fileid_archiveset on
alex1 a (cost=0.00..2.25 rows=1 width=8)
Index Cond: ((fileid = m.fileid) AND (archiveset
= "*SELECT*"."?column?"))
Filter: (fileid <> "*SELECT*"."?column?")
(65 rows)
============================================================================================
===== FAST INSERT (with quotes)
============================================================
explain insert into alex1 (fileid, archiveset) select '35352974', '10003';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on alex1 (cost=0.00..0.02 rows=1 width=0)
-> Subquery Scan on "*SELECT*" (cost=0.00..0.02 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
Delete on alex1 fa (cost=43024.02..90401.39 rows=2664064 width=56)
-> Nested Loop (cost=43024.02..90401.39 rows=2664064 width=56)
-> Subquery Scan on "*SELECT*" (cost=0.00..0.02 rows=1 width=24)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Nested Loop (cost=43024.02..63760.73 rows=2664064 width=32)
-> HashAggregate (cost=43024.02..43063.76 rows=3974
width=38)
-> Nested Loop (cost=14.98..43004.15 rows=3974
width=38)
-> Nested Loop (cost=14.98..12534.34
rows=13493 width=24)
Join Filter: (((m.esdt)::text =
(o.esdt)::text) AND ((m.key)::text = (o.key)::text))
-> Append (cost=0.00..37.68 rows=13
width=55)
-> Index Scan using
ak_filemeta_fileid on filemeta o (cost=0.00..4.29 rows=1 width=74)
Index Cond: (fileid =
35352974)
-> Index Scan using
pk_filemeta_anc on filemeta_anc o (cost=0.00..3.28 rows=1 width=44)
Index Cond: (fileid =
35352974)
-> Bitmap Heap Scan on
filemeta_app o (cost=1.77..4.34 rows=2 width=74)
Recheck Cond: (fileid =
35352974)
-> Bitmap Index Scan on
ak_filemeta_app_fileid (cost=0.00..1.77 rows=2 width=0)
Index Cond: (fileid
= 35352974)
-> Bitmap Heap Scan on
filemeta_clg_aux o (cost=1.77..5.49 rows=3 width=74)
Recheck Cond: (fileid =
35352974)
-> Bitmap Index Scan on
ak_filemeta_clg_aux_fileid_esdt (cost=0.00..1.77 rows=3 width=0)
Index Cond: (fileid
= 35352974)
-> Index Scan using
pk_filemeta_l0 on filemeta_l0 o (cost=0.00..3.28 rows=1 width=40)
Index Cond: (fileid =
35352974)
-> Index Scan using
pk_filemeta_l0r on filemeta_l0r o (cost=0.00..3.27 rows=1 width=42)
Index Cond: (fileid =
35352974)
-> Index Scan using
ak_filemeta_mdkey_fileid on filemeta_mdkey o (cost=0.00..3.31 rows=1
width=31)
Index Cond: (fileid =
35352974)
-> Index Scan using
filemeta_ompslookup_pkey on filemeta_ompslookup o (cost=0.00..3.27
rows=1 width=45)
Index Cond: (fileid =
35352974)
-> Index Scan using
pk_filemeta_orbital on filemeta_orbital o (cost=0.00..3.48 rows=1 width=24)
Index Cond: (fileid =
35352974)
-> Index Scan using
pk_filemeta_timerange on filemeta_timerange o (cost=0.00..3.67 rows=1
width=44)
Index Cond: (fileid =
35352974)
-> Append (cost=14.98..951.76
rows=635 width=78)
-> Bitmap Heap Scan on filemeta
m (cost=14.98..922.36 rows=624 width=78)
Recheck Cond:
(((esdt)::text = (o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Bitmap Index Scan on
ak_filemeta_esdt_key (cost=0.00..14.83 rows=624 width=0)
Index Cond:
(((esdt)::text = (o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using
ak_filemeta_anc_key on filemeta_anc m (cost=0.00..3.30 rows=1 width=48)
Index Cond: ((key)::text =
(o.key)::text)
-> Index Scan using
ak_filemeta_app_esdt_key on filemeta_app m (cost=0.00..1.31 rows=1
width=78)
Index Cond: (((esdt)::text
= (o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using
ak_filemeta_clg_aux_fileid_esdt on filemeta_clg_aux m (cost=0.00..5.82
rows=3 width=78)
Index Cond: ((esdt)::text
= (o.esdt)::text)
-> Index Scan using
ak_filemeta_l0_esdt_key on filemeta_l0 m (cost=0.00..3.30 rows=1 width=44)
Index Cond: (((esdt)::text
= (o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using
ak_filemeta_l0r_esdt_key on filemeta_l0r m (cost=0.00..2.35 rows=1
width=46)
Index Cond: (((esdt)::text
= (o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using
ak_filemeta_mdkey_esdt_key on filemeta_mdkey m (cost=0.00..3.35 rows=1
width=35)
Index Cond: (((esdt)::text
= (o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using
ak_filemeta_ompslookup_esdt_key on filemeta_ompslookup m
(cost=0.00..2.12 rows=1 width=49)
Index Cond: (((esdt)::text
= (o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using
ak_filemeta_orbital_esdt_key on filemeta_orbital m (cost=0.00..3.60
rows=1 width=28)
Index Cond: (((esdt)::text
= (o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using
ak_filemeta_timerange_esdt_key on filemeta_timerange m (cost=0.00..4.25
rows=1 width=48)
Index Cond: (((esdt)::text
= (o.esdt)::text) AND ((key)::text = (o.key)::text))
-> Index Scan using
pk_alex1_fileid_archiveset on alex1 a (cost=0.00..2.25 rows=1 width=14)
Index Cond: ((fileid = m.fileid) AND
(archiveset = 10003))
Filter: (fileid <> 35352974)
-> Index Scan using pk_alex1_fileid_archiveset on alex1
fa (cost=0.00..5.20 rows=1 width=14)
Index Cond: ((fileid = m.fileid) AND (archiveset =
10003))
(66 rows)
--
Best regards,
Alex Lai
OMI SIPS DBA ADNET Systems , Inc.
7515 Mission Drive,
Suite A100 Lanham, MD 20706
301-352-4657 (phone)
301-352-0437 (fax)
alai@sesda2.com
