Re: [GENERAL] foreign key constraint, planner ignore index.
Andrew Nesheret wrote: Richard Huxton wrote: Andrew Nesheret wrote: Richard Huxton wrote: If you PREPARE then EXECUTE the same query, does it still use the index? Tested, comments? --- code - --- result --- Result for node #2007 Index Scan using fki_nodes on sf_ipv4traffic x (cost=0.00..9.65 rows=1 width=6) (actual time=0.060..0.060 rows=0 loops=1) Index Cond: ($1 = node) Total runtime: 0.131 ms Result for node #156 Seq Scan on sf_ipv4traffic x (cost=0.00..360281.29 rows=15795383 width=6) (actual time=0.048..108939.142 rows=15795376 loops=1) Filter: ($1 = node) Total runtime: 365989.953 ms Since you are getting different plans, this can't be a planned query. Just try feeding a text-file with some SQL PREPARE/EXECUTEs to psql - you can EXPLAIN ANALYSE EXECUTE ... -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] foreign key constraint, planner ignore index.
Richard Huxton wrote: Since you are getting different plans, this can't be a planned query. Just try feeding a text-file with some SQL PREPARE/EXECUTEs to psql - you can EXPLAIN ANALYSE EXECUTE ... Test 1. with set enable_seqscan to on; set enable_seqscan to on; prepare testStatement (int) as SELECT 1 FROM ONLY sf_ipv4traffic x WHERE $1 OPERATOR(pg_catalog.=) node FOR SHARE OF x; EXPLAIN ANALYZE execute testStatement( 2007 ); EXPLAIN ANALYZE execute testStatement( 156 ); SET PREPARE QUERY PLAN Seq Scan on sf_ipv4traffic x (cost=0.00..360281.29 rows=15795383 width=6) (actual time=129082.768..129082.768 rows=0 loops=1) Filter: ($1 = node) Total runtime: 129131.315 ms (3 rows) QUERY PLAN -- Seq Scan on sf_ipv4traffic x (cost=0.00..360281.29 rows=15795383 width=6) (actual time=0.042..105788.088 rows=15795376 loops=1) Filter: ($1 = node) Total runtime: 420342.751 ms (3 rows) Test 2. with set enable_seqscan to on; set enable_seqscan to off; prepare testStatement (int) as SELECT 1 FROM ONLY sf_ipv4traffic x WHERE $1 OPERATOR(pg_catalog.=) node FOR SHARE OF x; EXPLAIN ANALYZE execute testStatement( 2007 ); EXPLAIN ANALYZE execute testStatement( 156 ); SET PREPARE QUERY PLAN -- Index Scan using fki_nodes on sf_ipv4traffic x (cost=0.00..577918.84 rows=15795383 width=6) (actual time=93.810..93.810 rows=0 loops=1) Index Cond: ($1 = node) Total runtime: 93.944 ms (3 rows) QUERY PLAN - Index Scan using fki_nodes on sf_ipv4traffic x (cost=0.00..577918.84 rows=15795383 width=6) (actual time=13.725..157090.878 rows=15795376 loops=1) Index Cond: ($1 = node) Total runtime: 445145.901 ms (3 rows) PS: Test 2 is more applicable, for both types of nodes a) for nodes with no statistics b) for nodes with statistics in sf_ipv4traffic too. -- __ WBR, Andrew Nesheret ICQ:10518066 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] foreign key constraint, planner ignore index.
Andrew Nesheret wrote: set enable_seqscan to on; prepare testStatement (int) as SELECT 1 FROM ONLY sf_ipv4traffic x WHERE $1 OPERATOR(pg_catalog.=) node FOR SHARE OF x; EXPLAIN ANALYZE execute testStatement( 2007 ); EXPLAIN ANALYZE execute testStatement( 156 ); QUERY PLAN Seq Scan on sf_ipv4traffic x (cost=0.00..360281.29 rows=15795383 Total runtime: 129131.315 ms Seq Scan on sf_ipv4traffic x (cost=0.00..360281.29 rows=15795383 Total runtime: 420342.751 ms set enable_seqscan to off; Index Scan using fki_nodes on sf_ipv4traffic x (cost=0.00..577918.84 Total runtime: 93.944 ms Index Scan using fki_nodes on sf_ipv4traffic x (cost=0.00..577918.84 Total runtime: 445145.901 ms OK, so your cost estimates are about 360,000 for seq-scan and 578,000 for index-scan. Of course the row estimates are fixed regardless of the value you test so the estimated cost is the same for both 2007 and 156. However, the timings aren't in line with the costs for node=156, and you'd hope they would be. That should be fixable by tweaking the planner cost settings (see chapter 17.6.2 planner cost constants), particularly effective_cache_size and random_page cost I'd guess. I'm putting together a small test case to see if I can reproduce your behaviour here. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] foreign key constraint, planner ignore index.
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? -- Richard Huxton Archonet Ltd CREATE TABLE testnode (node integer, nodename text, PRIMARY KEY (node)); CREATE TABLE traffic (id SERIAL, node integer NOT NULL, ts TIMESTAMP(0) WITH TIME ZONE NOT NULL, msg text); INSERT INTO testnode SELECT s, 'node number ' || s FROM generate_series(1,25) s; INSERT INTO traffic (id, node, ts, msg) SELECT s, 11, 'epoch'::timestamptz + s * '1 second'::interval, null FROM generate_series(1,999) s; ALTER TABLE traffic ADD CONSTRAINT traffic_node_fkey FOREIGN KEY (node) REFERENCES testnode (node) ON UPDATE RESTRICT ON DELETE RESTRICT; CREATE INDEX traffic_node_idx ON traffic (node); BEGIN; EXPLAIN ANALYSE DELETE FROM testnode WHERE node=9; ROLLBACK; BEGIN; EXPLAIN ANALYSE DELETE FROM testnode WHERE node=11; ROLLBACK; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] foreign key constraint, planner ignore index.
Richard Huxton wrote: 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? Yes, data is real and my my foreign key contraint is restrict (i'm understand this is very important). -- __ WBR, Andrew Nesheret ICQ:10518066 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] foreign key constraint, planner ignore index.
Andrew Nesheret wrote: Richard Huxton wrote: 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? Yes, data is real and my my foreign key contraint is restrict (i'm understand this is very important). Well, the RESTRICT bit is because with that test script in my last email it's instant here on PostgreSQL 8.2 (not bothered testing 8.3beta). The test script generates 10 million rows in the traffic table which should be close enough to replicate your problem. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 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 x -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 999
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
Re: [GENERAL] foreign key constraint, planner ignore index.
Andrew Nesheret wrote: 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? Good question. There *must* be a difference. Why can't either of us see it? 1. Try adding another 5 million rows to the test traffic table and see if that makes any difference. It shouldn't. 2. Run a vacuum verbose sf_ipv4traffic and see if there's a lot of dead rows. I shouldn't have thought there are. We might want to move this to the performance mailing-list, and see if anyone has an idea over there. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] foreign key constraint, planner ignore index.
Richard Huxton wrote: 1. Try adding another 5 million rows to the test traffic table and see if that makes any difference. It shouldn't. Opps. 1. Step drop table testnode cascade; drop table traffic; CREATE TABLE testnode (node integer, nodename text, PRIMARY KEY (node)); CREATE TABLE traffic (id SERIAL, node integer NOT NULL, ts TIMESTAMP(0) WITH TIME ZONE NOT NULL, msg text); INSERT INTO testnode SELECT s, 'node number ' || s FROM generate_series(1,25) s; INSERT INTO traffic (id, node, ts, msg) SELECT s, 11, 'epoch'::timestamptz + s * '1 second'::interval, null FR OM generate_series(1,1599) s; ALTER TABLE traffic ADD CONSTRAINT traffic_node_fkey FOREIGN KEY (node) REFERENCES testnode (node) ON UPDATE RESTRICT ON DELETE RESTRICT; CREATE INDEX traffic_node_idx ON traffic (node); BEGIN; EXPLAIN ANALYSE DELETE FROM testnode WHERE node=9; ROLLBACK; BEGIN; EXPLAIN ANALYSE DELETE FROM testnode WHERE node=11; ROLLBACK; --- output --- inms= \i fkey_index_prob.sql psql:fkey_index_prob.sql:1: NOTICE: drop cascades to constraint traffic_node_fkey on table traffic DROP TABLE DROP TABLE psql:fkey_index_prob.sql:4: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index testnode_pkey for table testnode CREATE TABLE psql:fkey_index_prob.sql:5: NOTICE: CREATE TABLE will create implicit sequence traffic_id_seq for serial column traffic.id CREATE TABLE INSERT 0 25 INSERT 0 1599 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=45.494..45.509 rows=1 loops=1) Index Cond: (node = 9) Trigger for constraint traffic_node_fkey: time=459.164 calls=1 ~ Good. Total runtime: 656.148 ms (4 rows) ROLLBACK BEGIN psql:fkey_index_prob.sql:18: 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 2. Step Run script again w/o creating data. *NO ANY MODIFICATIONS* to database. BEGIN; EXPLAIN ANALYSE DELETE FROM testnode WHERE node=9; ROLLBACK; BEGIN; EXPLAIN ANALYSE DELETE FROM testnode WHERE node=11; ROLLBACK; --- output BEGIN QUERY PLAN Index Scan using testnode_pkey on testnode
Re: [GENERAL] foreign key constraint, planner ignore index.
Andrew Nesheret wrote: Richard Huxton wrote: 1. Try adding another 5 million rows to the test traffic table and see if that makes any difference. It shouldn't. Opps. 1. Step [snip re-running of script] -- Index Scan using testnode_pkey on testnode (cost=0.00..8.27 rows=1 width=6) (actual time=45.494..45.509 rows=1 loops=1) Index Cond: (node = 9) Trigger for constraint traffic_node_fkey: time=459.164 calls=1 ~ Good. Total runtime: 656.148 ms 2. Step Run script again w/o creating data. *NO ANY MODIFICATIONS* to database. Index Scan using testnode_pkey on testnode (cost=0.00..8.27 rows=1 width=6) (actual time=0.041..0.043 rows=1 loops=1) Index Cond: (node = 9) Trigger for constraint traffic_node_fkey: time=41469.620 calls=1 BAD Total runtime: 41497.467 ms Hmm - not seeing that here. Is it just that your machine has a very variable workload? The times above are far enough apart from the times below that I'm not sure they can be trusted. What if you run it 10 times - do the times stay consistent? 3. Step Recreate data with 499 rows (SAME DIFFERENT results on first execute and second!!!) -- Index Scan using testnode_pkey on testnode (cost=0.00..8.27 rows=1 width=6) (actual time=25.050..25.054 rows=1 loops=1) Index Cond: (node = 9) Trigger for constraint traffic_node_fkey: time=18.339 calls=1 ~~ GOOD! Total runtime: 43.519 ms Index Scan using testnode_pkey on testnode (cost=0.00..8.27 rows=1 width=6) (actual time=0.114..0.116 rows=1 loops=1) Index Cond: (node = 9) Trigger for constraint traffic_node_fkey: time=7183.677 calls=1 There's no reason for changes in timing here - the traffic table isn't updated by the delete, only the testnode table and that's small enough not to matter. 2. Run a vacuum verbose sf_ipv4traffic and see if there's a lot of dead rows. I shouldn't have thought there are. No dead rows. [snip vacuum verbose output] No, so that can't be anything to do with it. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] foreign key constraint, planner ignore index.
Richard Huxton wrote: Hmm - not seeing that here. Is it just that your machine has a very variable workload? The times above are far enough apart from the times below that I'm not sure they can be trusted. What if you run it 10 times - do the times stay consistent? stay consistent and this test has exclusive access to postgres and no other activites on this system. Thank you Richard! And my question is. What should i do further ? -- __ WBR, Andrew Nesheret ICQ:10518066 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] foreign key constraint, planner ignore index.
Andrew Nesheret wrote: 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) If you PREPARE then EXECUTE the same query, does it still use the index? The only thing I can think of is that the trigger is planning the query to work for any given value and you have a lot of rows with e.g. node=2004. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] foreign key constraint, planner ignore index.
Richard Huxton wrote:
If you PREPARE then EXECUTE the same query, does it still use the
index?
Done, same result.
--- Code ---
stmt = connection.prepareStatement( explain analyze SELECT 1 FROM
ONLY sf_ipv4traffic x WHERE ? OPERATOR(pg_catalog.=) node FOR SHARE OF x );
stmt.setLong( 1, 2004 );
rs = stmt.executeQuery();
while(rs.next())
{
System.out.println( rs.getString(1) );
}
--
Output:
Index Scan using fki_nodes on sf_ipv4traffic x (cost=0.00..9.65 rows=1
width=6) (actual time=0.063..0.063 rows=0 loops=1)
Index Cond: ($1 = node)
Total runtime: 0.131 ms
Test again with node=165
Seq Scan on sf_ipv4traffic x (cost=0.00..360281.29 rows=15795383
width=6) (actual time=0.041..105833.129 rows=15795376 loops=1)
Filter: ($1 = node)
Total runtime: 414955.170 ms
Planner/optimiser choice different strategies on different node IDS! But
'foreign key constraint checker' always use one strategy.
The only thing I can think of is that the trigger is planning the
query to work for any given value and you have a lot of rows with e.g.
node=2004.
Child table sf_ipv4traffic, contains only ONE value (in node column) and
this node never deleted
ALL 15 millions rows referenced to node 156
inms= select node from sf_ipv4traffic limit 10;
node
--
156
156
156
156
156
156
156
156
156
156
(10 rows)
--
__
WBR, Andrew Nesheret ICQ:10518066
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] foreign key constraint, planner ignore index.
Andrew Nesheret wrote:
Richard Huxton wrote:
If you PREPARE then EXECUTE the same query, does it still use the
index?
Done, same result.
--- Code ---
stmt = connection.prepareStatement( explain analyze SELECT 1 FROM
ONLY sf_ipv4traffic x WHERE ? OPERATOR(pg_catalog.=) node FOR SHARE OF
x );
stmt.setLong( 1, 2004 );
rs = stmt.executeQuery();
while(rs.next())
{
System.out.println( rs.getString(1) );
}
No, this will produce different plans - what happens if you supply each
parameter in turn - the second plan should stay the same as the first.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] foreign key constraint, planner ignore index.
Richard Huxton wrote: Andrew Nesheret wrote: Richard Huxton wrote: If you PREPARE then EXECUTE the same query, does it still use the index? Tested, comments? --- code - --- result --- Result for node #2007 Index Scan using fki_nodes on sf_ipv4traffic x (cost=0.00..9.65 rows=1 width=6) (actual time=0.060..0.060 rows=0 loops=1) Index Cond: ($1 = node) Total runtime: 0.131 ms Result for node #156 Seq Scan on sf_ipv4traffic x (cost=0.00..360281.29 rows=15795383 width=6) (actual time=0.048..108939.142 rows=15795376 loops=1) Filter: ($1 = node) Total runtime: 365989.953 ms UP(subject), we are go away from my problem, in case of integrity check with foreign key. -- __ WBR, Andrew Nesheret ICQ:10518066 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] foreign key constraint, planner ignore index.
Forget to add test source code.
--- code ---
long [] IDs = new long[] {
2007, // no references
156 // all rows in sf_ipv4traffic
referenced to
};
stmt = connection.prepareStatement( explain analyze SELECT 1 FROM
ONLY sf_ipv4traffic x WHERE ? OPERATOR(pg_catalog.=) node FOR SHARE OF x );
for ( long nodeID : IDs )
{
stmt.setLong( 1, nodeID );
rs = stmt.executeQuery();
System.out.println( Result for node # + nodeID );
while ( rs.next() )
System.out.println(+ rs.getString( 1 ) );
}
rs.close();
connection.close();
---
--
__
WBR, Andrew Nesheret ICQ:10518066
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] foreign key constraint, planner ignore 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
