Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-20 Thread Andrew Nesheret
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 tes

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-20 Thread Richard Huxton
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] ---

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-20 Thread Andrew Nesheret
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;

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-20 Thread Richard Huxton
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

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-20 Thread Andrew Nesheret
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 tha

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-20 Thread Richard Huxton
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 forei

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-20 Thread Andrew Nesheret
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 "res

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-20 Thread Richard Huxton
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

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-20 Thread Richard Huxton
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 );

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-20 Thread Andrew Nesheret
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) a

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-20 Thread Richard Huxton
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 (co

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-19 Thread Andrew Nesheret
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( "exp

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-19 Thread Andrew Nesheret
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 wi

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-19 Thread Richard Huxton
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 SHAR

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-19 Thread Andrew Nesheret
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.se

Re: [GENERAL] foreign key constraint, planner ignore index.

2007-12-19 Thread Richard Huxton
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;