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.
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.
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] foreign key constraint, planner ignore index.
..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 (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 - 3. Step Recreate data with 499 rows (SAME DIFFERENT results on first execute and second!!!) First: CREATE TABLE INSERT 0 25 INSERT 0 499 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=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 (4 rows) Second: BEGIN QUERY PLAN 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 ~~~ Perfomance degradation!!! Total runtime: 7183.928 ms (4 rows) ROLLBACK You comments? 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. inms= VACUUM VERBOSE sf_ipv4traffic; INFO: vacuuming public.sf_ipv4traffic INFO: index sf_ipv4traffic_pkey now contains 15795376 row versions in 122709 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 3.88s/0.52u sec elapsed 38.44 sec. INFO: index fki_nodes now contains 15795376 row versions in 34664 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 1.04s/0.07u sec elapsed 13.34 sec. INFO: index sf_ipv4traffic_idx now contains 15795376 row versions in 60822 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 1.69s/0.14u sec elapsed 16.71 sec. INFO: index sf_ipv4traffic_idx1 now contains 15795376 row versions in 60822 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 1.90s/0.19u sec elapsed 17.77 sec. INFO: index sf_ipv4traffic_idx3 now contains 15795376 row versions in 60822 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 1.90s/0.19u sec elapsed 16.97 sec. INFO: index sf_ipv4traffic_idx4 now contains 15795376 row versions in 43311 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 1.38s/0.13u sec elapsed 12.09 sec. INFO: sf_ipv4traffic: found 0 removable, 15795376 nonremovable row versions in 162839 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 1 pages contain useful free space. 0 pages are entirely empty. CPU 21.91s/4.93u sec elapsed 252.77 sec. INFO: vacuuming pg_toast.pg_toast_16555 INFO: index pg_toast_16555_index now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: pg_toast_16555: found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages contain useful free space. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.01 sec. -- __ WBR, Andrew Nesheret ICQ:10518066 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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.
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.
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.
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
