Re: seqscan for 100 out of 3M rows, index present

Поиск
Список
Период
Сортировка
От Victor Yegorov
Тема Re: seqscan for 100 out of 3M rows, index present
Дата
Msg-id CAGnEbogEAwHufCgXKt2DzBbokfwzmAu0gjkPn8jeF3ajY2x2wg@mail.gmail.com
обсуждение исходный текст
Ответ на seqscan for 100 out of 3M rows, index present  (Willy-Bas Loos <willybas@gmail.com>)
Ответы Re: seqscan for 100 out of 3M rows, index present  (Willy-Bas Loos <willybas@gmail.com>)
Список pgsql-performance
Дерево обсуждения
2013/6/26 Willy-Bas Loos <willybas@gmail.com>
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

1) Could you show the output of the following queries, please?
select relname,relpages,reltuples::numeric
  from pg_class where oid in ('d2'::regclass, 'g2'::regclass);
select attrelid::regclass, attname,
       CASE WHEN attstattarget<0 THEN current_setting('default_statistics_target')::int4 ELSE attstattarget END
  from pg_attribute
 where attrelid in ('d2'::regclass, 'g2'::regclass) and attname='gid';

2) Will it help running the following?:
ALTER TABLE d2 ALTER gid SET STATISTICS 500;
VACUUM ANALYZE d2;
EXPLAIN (ANALYZE, BUFFERS) ...
SET enable_seqscan TO 'off';
EXPLAIN (ANALYZE, BUFFERS) ...


--
Victor Y. Yegorov

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

Предыдущее
От: Willy-Bas Loos
Дата:
Сообщение: Re: seqscan for 100 out of 3M rows, index present
Следующее
От: "ktm@rice.edu"
Дата:
Сообщение: Re: seqscan for 100 out of 3M rows, index present