seq scan over 3.3 million rows instead of single key index access

От: Andrus
Тема: seq scan over 3.3 million rows instead of single key index access
Дата: ,
Msg-id: gg9pg7$1edv$2@news.hub.org
(см: обсуждение, исходный текст)
Ответы: Re: seq scan over 3.3 million rows instead of single key index access  (Gregory Stark)
Re: seq scan over 3.3 million rows instead of single key index access  (Andreas Kretschmer)
Re: seq scan over 3.3 million rows instead of single key index access  (Andreas Kretschmer)
Список: pgsql-performance

Скрыть дерево обсуждения

seq scan over 3.3 million rows instead of single key index access  ("Andrus", )
 Re: seq scan over 3.3 million rows instead of single key index access  (Gregory Stark, )
  Re: seq scan over 3.3 million rows instead of single key index access  (Tom Lane, )
  Re: seq scan over 3.3 million rows instead of single key index access  ("Andrus", )
   Re: seq scan over 3.3 million rows instead of single key index access  ("A. Kretschmer", )
 Re: seq scan over 3.3 million rows instead of single key index access  (Andreas Kretschmer, )
  Re: seq scan over 3.3 million rows instead of single keyindex access  ("Andrus", )
 Re: seq scan over 3.3 million rows instead of single key index access  (Andreas Kretschmer, )

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"



В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: seq scan over 3.3 million rows instead of single key index access
От: "Andrus"
Дата:
Сообщение: Re: seq scan over 3.3 million rows instead of single key index access