Re: foreign key constraint, planner ignore index.

Richard Huxton wrote:
> Richard Huxton wrote:
>>
>> I'm putting together a small test case to see if I can reproduce your
>> behaviour here.
>
> Does the attached small script misbehave in the same way as your real
> data? From here it works fine when the fkey is ON ... RESTRICT.
>
> I'm right in thinking that your "nodes" fkey is RESTRICT on update and
> delete?
>
You script is worked fine on same database, where is difference?
-- output ---
psql xxxx xxxxx -f fkey_index_prob.sql
psql:fkey_index_prob.sql:2: NOTICE: CREATE TABLE / PRIMARY KEY will
create implicit index "testnode_pkey" for table "testnode"
CREATE TABLE
psql:fkey_index_prob.sql:3: NOTICE: CREATE TABLE will create implicit
sequence "traffic_id_seq" for serial column "traffic.id"
CREATE TABLE
INSERT 0 25
INSERT 0 9999999
ALTER TABLE
CREATE INDEX
BEGIN
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using testnode_pkey on testnode (cost=0.00..8.27 rows=1
width=6) (actual time=43.480..43.485 rows=1 loops=1)
Index Cond: (node = 9)
Trigger for constraint traffic_node_fkey: time=106.053 calls=1
Total runtime: 165.925 ms
~~~~~~~~~~~~~~~~~~~~~~~~~~ GOOD!
(4 rows)

ROLLBACK
BEGIN
psql:fkey_index_prob.sql:16: ERROR: update or delete on table
"testnode" violates foreign key constraint "traffic_node_fkey" on table
"traffic"
DETAIL: Key (node)=(11) is still referenced from table "traffic".
ROLLBACK

--- My database ---------------

CREATE TABLE nodes
(
id integer NOT NULL DEFAULT nextval('nodesidseq'::regclass),
description character varying(256),
identifier character varying(256) NOT NULL,
CONSTRAINT nodes_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE nodes OWNER TO inms;

-- Index: "NodeIdentifierIndex"

-- DROP INDEX "NodeIdentifierIndex";

CREATE UNIQUE INDEX "NodeIdentifierIndex"
ON nodes
USING btree
(identifier);

CREATE TABLE sf_ipv4traffic
(
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,
CONSTRAINT sf_ipv4traffic_pkey PRIMARY KEY (timeframe, timemark, node,
source_address, source_port, destination_address, destination_port,
protocol_type),
CONSTRAINT nodes FOREIGN KEY (node)
REFERENCES nodes (id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT sf_ipv4traffic_timeframe_fkey FOREIGN KEY (timeframe)
REFERENCES sf_timeframes (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH (OIDS=FALSE);
ALTER TABLE sf_ipv4traffic OWNER TO inms;

-- Index: fki_nodes

-- DROP INDEX fki_nodes;

CREATE INDEX fki_nodes
ON sf_ipv4traffic
USING btree
(node);

-- Index: sf_ipv4traffic_idx

-- DROP INDEX sf_ipv4traffic_idx;

CREATE INDEX sf_ipv4traffic_idx
ON sf_ipv4traffic
USING btree
(source_port, timeframe, source_address);

-- Index: sf_ipv4traffic_idx1

-- DROP INDEX sf_ipv4traffic_idx1;

CREATE INDEX sf_ipv4traffic_idx1
ON sf_ipv4traffic
USING btree
(timeframe, node, timemark);

-- Index: sf_ipv4traffic_idx3

-- DROP INDEX sf_ipv4traffic_idx3;

CREATE INDEX sf_ipv4traffic_idx3
ON sf_ipv4traffic
USING btree
(destination_address, destination_port, timeframe);

-- Index: sf_ipv4traffic_idx4

-- DROP INDEX sf_ipv4traffic_idx4;

CREATE INDEX sf_ipv4traffic_idx4
ON sf_ipv4traffic
USING btree
(protocol_type, timeframe);

Other tables definition skipped.....

--- test1.sql ---------
begin;

--set enable_seqscan to
off;

delete from decimalnodeattributes where
node=2007;
delete from stringnodeattributes where
node=2007;
delete from datenodeattributes where
node=2007;
delete from topology where fromnode=2007 or
tonode=2007;
explain analyze delete from nodes where
id=2007;
rollback;
--- output ---
inms=> \i test1.sql
BEGIN
DELETE 0
DELETE 1
DELETE 1
DELETE 2
QUERY
PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on nodes (cost=0.00..1.29 rows=1 width=6) (actual
time=0.034..0.036 rows=1 loops=1)
Filter: (id = 2007)
Trigger for constraint booleannodeattributes_node_fkey: time=89.885 calls=1
Trigger for constraint datenodeattributes_node_fkey: time=0.466 calls=1
Trigger for constraint decimalnodeattributes_node_fkey: time=0.268 calls=1
Trigger for constraint node: time=369.983 calls=1
Trigger for constraint nodes: time=64278.862 calls=1
~~~~~~~~~~~~~~~~~~~~~~~~~~~ BAD.

Trigger for constraint snmp_nodes_access_nodeid_fkey: time=41.661 calls=1
Trigger for constraint stringnodeattributes_node_fkey: time=0.408 calls=1
Trigger for constraint topology_fromnode_fkey: time=0.308 calls=1
Trigger for constraint topology_tonode_fkey: time=0.277 calls=1
Total runtime: 64814.359 ms
~~~~~~~~~~~~~~~~~~~~~~~~~~~ BAD.
(12 rows)

ROLLBACK

--
__________________________________
WBR, Andrew Nesheret ICQ:10518066