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

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)

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 );

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

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

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

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

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.

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;

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]

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

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;

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 );

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

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

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(

[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 |