Search Postgresql Archives

foreign key constraint, planner ignore index.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread 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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux