Re: foreign key constraint, planner ignore index.
От | Andrew Nesheret |
---|---|
Тема | Re: foreign key constraint, planner ignore index. |
Дата | |
Msg-id | 476A4E8E.5010104@infinet.ru обсуждение исходный текст |
Ответ на | Re: foreign key constraint, planner ignore index. (Richard Huxton <dev@archonet.com>) |
Ответы |
Re: foreign key constraint, planner ignore index.
(Richard Huxton <dev@archonet.com>)
|
Список | 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
В списке pgsql-general по дате отправления: