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

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(at)gmail(dot)com> wrote:

> 2013/6/26 Willy-Bas Loos <willybas(at)gmail(dot)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
>

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