Re: 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