From: Andrus <kobruleht2@hot.ee>
To: pgsql-performance@postgresql.org
Subject: seq scan over 3.3 million rows instead of single key index access
Date: Sat, 22 Nov 2008 21:33:28 +0200
Message-ID: <gg9pg7$1edv$2@news.hub.org> (raw)
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"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kobruleht2@hot.ee
Subject: Re: seq scan over 3.3 million rows instead of single key index access
In-Reply-To: <gg9pg7$1edv$2@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox