Re: foreign key constraint, planner ignore index. - Mailing list pgsql-general
From | Andrew Nesheret |
---|---|
Subject | Re: foreign key constraint, planner ignore index. |
Date | |
Msg-id | 476A6D9F.30107@infinet.ru Whole thread Raw |
In response to | Re: foreign key constraint, planner ignore index. (Richard Huxton <dev@archonet.com>) |
Responses |
Re: foreign key constraint, planner ignore index.
|
List | pgsql-general |
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
pgsql-general by date: