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