Avoiding seq scan over 3.2 millions rows

Поиск
Список
Период
Сортировка
От Andrus
Тема Avoiding seq scan over 3.2 millions rows
Дата
Msg-id gfcr5u$1dem$1@news.hub.org
обсуждение исходный текст
Ответы Re: Avoiding seq scan over 3.2 millions rows  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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)"


В списке pgsql-general по дате отправления:

Предыдущее
От: Adriana Alfonzo
Дата:
Сообщение: Re: Timestamp precission question
Следующее
От: Glen Beane
Дата:
Сообщение: Re: Problem using COPY command to load data