Planner forces seq scan when select without quoting its values

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