Postgres Pro
Downloads
Home   >   mailing lists

Re: seqscan for 100 out of 3M rows, index present - Mailing list pgsql-performance

From Victor Yegorov
Subject Re: seqscan for 100 out of 3M rows, index present
Date
Msg-id CAGnEbogEAwHufCgXKt2DzBbokfwzmAu0gjkPn8jeF3ajY2x2wg@mail.gmail.com
Whole thread Raw
In response to seqscan for 100 out of 3M rows, index present  (Willy-Bas Loos <willybas@gmail.com>)
Responses Re: seqscan for 100 out of 3M rows, index present
List pgsql-performance
Tree view
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 by date:

Previous
From: Willy-Bas Loos
Date:
Subject: Re: seqscan for 100 out of 3M rows, index present
Next
From: "ktm@rice.edu"
Date:
Subject: Re: seqscan for 100 out of 3M rows, index present