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