Avoiding seq scan over 3.2 millions rows

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: Avoiding seq scan over 3.2 millions rows
Date: 2008-11-11 20:50:05
Message-ID: gfcr5u$1dem$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

explain analyze SELECT sum(xxx)
FROM dok JOIN rid USING (dokumnr)
WHERE dok.kuupaev BETWEEN '2008-04-01' AND '2008-04-30'

"Hash Join (cost=29584.84..308259.32 rows=142686 width=0) (actual
time=68510.748..96932.174 rows=117883 loops=1)"
" Hash Cond: ("outer".dokumnr = "inner".dokumnr)"
" -> Seq Scan on rid (cost=0.00..195599.68 rows=3217368 width=4) (actual
time=17.130..56572.857 rows=3247363 loops=1)"
" -> Hash (cost=29243.76..29243.76 rows=53231 width=4) (actual
time=15878.782..15878.782 rows=44685 loops=1)"
" -> Index Scan using dok_kuupaev_idx on dok (cost=0.00..29243.76
rows=53231 width=4) (actual time=47.102..15651.059 rows=44685 loops=1)"
" Index Cond: ((kuupaev >= '2008-04-01'::date) AND (kuupaev <=
'2008-04-30'::date))"
"Total runtime: 97364.282 ms"

Query performs seq scan over 3.2 million of rows.

dokumnr is of type int, kuupaev is of type DATE.
There are regular indexes on

rid(dokumnr)
dok(dokumnr)
dok(kuupaev)

Vacuum is running automatically.
How to speed up this query ?

Andrus.

"PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 3.4.6 (Gentoo 3.4.6-r1, ssp-3.4.5-1.0,
pie-8.7.9)"

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2008-11-11 21:47:17 Re: [GENERAL] db_user_namespace, md5 and changing passwords
Previous Message Vaclav TVRDIK 2008-11-11 20:38:42 Re: Timestamp precission question