foreign key constraint, planner ignore index.

Greetings, List.

Environment: Linux, (PostgreSQL) 8.3beta4 or  (PostgreSQL) 8.2.4, same
results.

Billing database with two tables.

1. Small table with nodes (23 rows)
inms=> \d nodes
                                  Table "public.nodes"
   Column    |          Type          |
Modifiers
-------------+------------------------+--------------------------------------------------
 id          | integer                | not null default
nextval('nodesidseq'::regclass)
 description | character varying(256) |
 identifier  | character varying(256) | not null
Indexes:
    "nodes_pkey" PRIMARY KEY, btree (id)
    "NodeIdentifierIndex" UNIQUE, btree (identifier)
inms=> analyze verbose nodes;
INFO:  analyzing "public.nodes"
INFO:  "nodes": scanned 1 of 1 pages, containing 23 live rows and 4 dead
rows; 23 rows in sample, 23 estimated total rows

2. Large table with collected traffic ( 15795383 rows )
inms=> \d sf_ipv4traffic
               Table "public.sf_ipv4traffic"
       Column        |           Type           | Modifiers
---------------------+--------------------------+-----------
 timeframe           | integer                  | not null
 timemark            | timestamp with time zone | not null
 node                | integer                  | not null
 source_address      | bytea                    | not null
 source_port         | integer                  | not null
 destination_address | bytea                    | not null
 destination_port    | integer                  | not null
 protocol_type       | integer                  | not null
 octets_counter      | bigint                   |
 packets_counter     | integer                  |
Indexes:
    "sf_ipv4traffic_pkey" PRIMARY KEY, btree (timeframe, timemark, node,
source_address, source_port, destination_address, destination_port,
protocol_type)
    "fki_nodes" btree (node)
    "sf_ipv4traffic_idx" btree (source_port, timeframe, source_address)
    "sf_ipv4traffic_idx1" btree (timeframe, node, timemark)
    "sf_ipv4traffic_idx3" btree (destination_address, destination_port,
timeframe)
    "sf_ipv4traffic_idx4" btree (protocol_type, timeframe)
Foreign-key constraints:
    "nodes" FOREIGN KEY (node) REFERENCES nodes(id) ON UPDATE RESTRICT
ON DELETE RESTRICT
    "sf_ipv4traffic_timeframe_fkey" FOREIGN KEY (timeframe) REFERENCES
sf_timeframes(id) ON UPDATE CASCADE ON DELETE RESTRICT

inms=> ANALYZE verbose sf_ipv4traffic;
INFO:  analyzing "public.sf_ipv4traffic"
INFO:  "sf_ipv4traffic": scanned 3000 of 162839 pages, containing 291000
live rows and 0 dead rows; 3000 rows in sample, 15795383 estimated total
rows

Problem is.
Planner ignore index when delete some node from nodes tables.

Test script:
begin;

--set enable_seqscan to
off;

delete from decimalnodeattributes where
node=2003;
delete from stringnodeattributes where
node=2003;
delete from datenodeattributes where
node=2003;
delete from topology where fromnode=2003 or
tonode=2003;
explain analyze delete from nodes where
id=2003;
rollback;

                                          QUERY
PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on nodes  (cost=0.00..1.29 rows=1 width=6) (actual
time=0.046..0.047 rows=1 loops=1)
   Filter: (id = 2003)
 Trigger for constraint booleannodeattributes_node_fkey: time=1.315 calls=1
 Trigger for constraint datenodeattributes_node_fkey: time=0.361 calls=1
 Trigger for constraint decimalnodeattributes_node_fkey: time=0.288 calls=1
 Trigger for constraint node: time=28.109 calls=1
 Trigger for constraint nodes: time=71011.395 calls=1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Trigger for constraint snmp_nodes_access_nodeid_fkey: time=372.504 calls=1
 Trigger for constraint stringnodeattributes_node_fkey: time=7.008 calls=1
 Trigger for constraint topology_fromnode_fkey: time=0.368 calls=1
 Trigger for constraint topology_tonode_fkey: time=0.274 calls=1
 Total runtime: 71430.159 ms
(12 rows)

------- !!!!!!!! ---------------
 Trigger for constraint nodes: time=71011.395 calls=1
--------------------------------

But if, turn off <seqscan>, same test

begin;

set enable_seqscan to off; --
!!!
delete from decimalnodeattributes where
node=2003;
delete from stringnodeattributes where
node=2003;
delete from datenodeattributes where
node=2003;
delete from topology where fromnode=2003 or
tonode=2003;
explain analyze delete from nodes where
id=2003;
rollback;


 Index Scan using nodes_pkey on nodes  (cost=0.00..8.27 rows=1 width=6)
(actual time=0.029..0.033 rows=1 loops=1)
   Index Cond: (id = 2003)
 Trigger for constraint booleannodeattributes_node_fkey: time=1.365 calls=1
 Trigger for constraint datenodeattributes_node_fkey: time=0.359 calls=1
 Trigger for constraint decimalnodeattributes_node_fkey: time=0.252 calls=1
 Trigger for constraint node: time=28.197 calls=1
 Trigger for constraint nodes: time=1.911 calls=1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Trigger for constraint snmp_nodes_access_nodeid_fkey: time=0.611 calls=1
 Trigger for constraint stringnodeattributes_node_fkey: time=0.310 calls=1
 Trigger for constraint topology_fromnode_fkey: time=0.351 calls=1
 Trigger for constraint topology_tonode_fkey: time=0.289 calls=1
 Total runtime: 42.930 ms
(12 rows)
------
Additional info:
Termination Ctrl+C - during execution of psql dumps

BEGIN
DELETE 1
DELETE 1
DELETE 1
DELETE 2
Cancel request sent
psql:test.sql:7: ERROR:  canceling statement due to user request
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."sf_ipv4traffic" x
WHERE $1 OPERATOR(pg_catalog.=) "node" FOR SHARE OF x"
---

And if i'm execute same statement without access to nodes table planer
chose to use index "fki_nodes"!!!

explain analyze SELECT 1 FROM ONLY "public"."sf_ipv4traffic" x WHERE
2003 OPERATOR(pg_catalog.=) "node" FOR SHARE OF x;

                                                         QUERY
PLAN

----------------------------------------------------------------------------------------------------------------------------
 Index Scan using fki_nodes on sf_ipv4traffic x  (cost=0.00..9.65 rows=1
width=6) (actual time=0.019..0.019 rows=0 loops=1)
   Index Cond: (2003 = node)
 Total runtime: 0.089 ms
(3 rows)

---

Any suggesions?,
thanks.

--
__________________________________
WBR, Andrew Nesheret ICQ:10518066