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