There are indexes on rid(dokumnr) and dok(dokumnr) and dokumnr is int.
Instead of using single key index, 8.1.4 scans over whole rid table.
Sometimes idtelluued can contain more than single row so replacing join with
equality is not possible.
How to fix ?
Andrus.
CREATE TEMP TABLE idtellUued(dokumnr INT) ON COMMIT DROP;
INSERT INTO idtellUued VALUES(1249228);
explain analyze select 1
from dok JOIN rid USING(dokumnr)
JOIN idtellUued USING(dokumnr)
"Hash Join (cost=7483.22..222259.77 rows=5706 width=0) (actual
time=14905.981..27065.903 rows=8 loops=1)"
" Hash Cond: ("outer".dokumnr = "inner".dokumnr)"
" -> Seq Scan on rid (cost=0.00..198240.33 rows=3295833 width=4) (actual
time=0.036..15021.641 rows=3280576 loops=1)"
" -> Hash (cost=7477.87..7477.87 rows=2140 width=8) (actual
time=0.114..0.114 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..7477.87 rows=2140 width=8) (actual
time=0.076..0.099 rows=1 loops=1)"
" -> Seq Scan on idtelluued (cost=0.00..31.40 rows=2140
width=4) (actual time=0.006..0.011 rows=1 loops=1)"
" -> Index Scan using dok_dokumnr_idx on dok (cost=0.00..3.47
rows=1 width=4) (actual time=0.051..0.058 rows=1 loops=1)"
" Index Cond: (dok.dokumnr = "outer".dokumnr)"
"Total runtime: 27066.080 ms"
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
