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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
