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

[Willy-Bas Loos <willybas@xxxxxxxxx>]

1)
 attrelid | attname | attstattarget
----------+---------+---------------
 g2       | gid     |           100
 d2       | gid     |           100
(2 rows)

setting statistics too 500 works!

I already tried overruling pg_statistic.stadistinct, but that didn't work.

thank you all for your help!!

Cheers,

Willy-Bas

On Wed, Jun 26, 2013 at 10:46 PM, Victor Yegorov <vyegorov@xxxxxxxxx> wrote:

2013/6/26 Willy-Bas Loos <willybas@xxxxxxxxx>

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

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