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
