foreign key constraint, planner ignore index.
От | Andrew Nesheret |
---|---|
Тема | foreign key constraint, planner ignore index. |
Дата | |
Msg-id | 4768B1C5.9030607@infinet.ru обсуждение исходный текст |
Ответы |
Re: foreign key constraint, planner ignore index.
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: