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