From: | Victor Yegorov <vyegorov(at)gmail(dot)com> |
---|---|
To: | Willy-Bas Loos <willybas(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: seqscan for 100 out of 3M rows, index present |
Date: | 2013-06-26 20:46:27 |
Message-ID: | CAGnEbogEAwHufCgXKt2DzBbokfwzmAu0gjkPn8jeF3ajY2x2wg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | ktm@rice.edu | 2013-06-26 20:48:04 | Re: seqscan for 100 out of 3M rows, index present |
Previous Message | Willy-Bas Loos | 2013-06-26 20:36:10 | Re: seqscan for 100 out of 3M rows, index present |