Re: foreign key constraint, planner ignore index. - Mailing list pgsql-general

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


SET
PREPARE
                                                           QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on sf_ipv4traffic x  (cost=0.00..360281.29 rows=15795383
width=6) (actual time=129082.768..129082.768 rows=0 loops=1)
   Filter: ($1 = node)
 Total runtime: 129131.315 ms
(3 rows)

                                                            QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on sf_ipv4traffic x  (cost=0.00..360281.29 rows=15795383
width=6) (actual time=0.042..105788.088 rows=15795376 loops=1)
   Filter: ($1 = node)
 Total runtime: 420342.751 ms
(3 rows)

Test 2. with set enable_seqscan to on;

set enable_seqscan to off;

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

SET
PREPARE
                                                                QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using fki_nodes on sf_ipv4traffic x  (cost=0.00..577918.84
rows=15795383 width=6) (actual time=93.810..93.810 rows=0 loops=1)
   Index Cond: ($1 = node)
 Total runtime: 93.944 ms
(3 rows)


QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using fki_nodes on sf_ipv4traffic x  (cost=0.00..577918.84
rows=15795383 width=6) (actual time=13.725..157090.878 rows=15795376
loops=1)
   Index Cond: ($1 = node)
 Total runtime: 445145.901 ms
(3 rows)

PS: Test 2 is more applicable, for both types of nodes
a) for nodes with no statistics
b) for nodes with statistics in sf_ipv4traffic too.

--
__________________________________
WBR, Andrew Nesheret ICQ:10518066