seqscan for 100 out of 3M rows, index present

Поиск
Список
Период
Сортировка
От Willy-Bas Loos
Тема seqscan for 100 out of 3M rows, index present
Дата
Msg-id CAHnozTgJQq8vPns_WpS+7i=JrQm9zaCyQ_-eKD2HzCibfSZ1Qw@mail.gmail.com
обсуждение исходный текст
Ответы Re: seqscan for 100 out of 3M rows, index present  (Igor Neyman <ineyman@perceptron.com>)
Re: seqscan for 100 out of 3M rows, index present  (Scott Marlowe <scott.marlowe@gmail.com>)
Re: seqscan for 100 out of 3M rows, index present  (Victor Yegorov <vyegorov@gmail.com>)
Список pgsql-performance
Дерево обсуждения
Hi,

postgres does a seqscan, even though there is an index present and it should be much more efficient to use it.
I tried to synthetically reproduce it, but it won't make the same choice when i do.
I can reproduce it with a simplified set of the data itself though.

here's the query, and the analyzed plan:
select count(*)
from d2
join g2 on g2.gid=d2.gid
where g2.k=1942

Aggregate  (cost=60836.71..60836.72 rows=1 width=0) (actual time=481.526..481.526 rows=1 loops=1)
  ->  Hash Join  (cost=1296.42..60833.75 rows=1184 width=0) (actual time=317.403..481.513 rows=17 loops=1)
        Hash Cond: (d2.gid = g2.gid)
        ->  Seq Scan on d2  (cost=0.00..47872.54 rows=3107454 width=8) (actual time=0.013..231.707 rows=3107454 loops=1)
        ->  Hash  (cost=1290.24..1290.24 rows=494 width=8) (actual time=0.207..0.207 rows=121 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 5kB
              ->  Index Scan using g_blok on g2  (cost=0.00..1290.24 rows=494 width=8) (actual time=0.102..0.156 rows=121 loops=1)
                    Index Cond: (k = 1942)
Total runtime: 481.600 ms

Here's the DDL:
create table g2 (gid bigint primary key, k integer);
create table d2 (id bigint primary key, gid bigint);
--insert into g2 (...)
--insert into d2 (...)
create index g_blok on g2(blok);
create index d_gid on d2(gid);
alter table d2 add constraint d_g_fk foreign key (gid) references g2 (gid);
analyze d2;
analyze g2;


Any advice?

Cheers,

Willy-Bas Loos
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Weird, bad 0.5% selectivity estimate for a column equal to itself
Следующее
От: Igor Neyman
Дата:
Сообщение: Re: seqscan for 100 out of 3M rows, index present