RE: Which index is used in the index scan.

Hi, Andreas

Thank you your reply.

This problem was solved.
I revalidated your advice, source code and the statistics.
As a result, the cost was changing due to the difference in the index height.

Regards,
SAKAI Teppei

> On 17 October 2018 07:39:48 CEST, "Sakai, Teppei"
> <sakai(dot)teppei(at)jp(dot)fujitsu(dot)com> wrote:
> >Hi
> >
> >We have question about index scan.
> >
> >We created table and indexes and executed SQL, but the index used by
> >day is different.
> >One day the index of c1 was used, and on another day the index of c2
> >was used.
> > - CREATE TABLE tbl1 (c1 varchar, c2 varchar, c3 timestamptz);
> > - CREATE INDEX tbl1_idx_c1 ON tbl1(c1);
> > - CREATE INDEX tbl1_idx_c2 ON tbl2(c2);
> >- SELECT * FROM tbl1 WHERE c1 = 'XXX' AND C2 = 'YYY' AND C3 >=
> >'yyyy/mm/dd' AND C3 <= 'yyyy/mm/dd';
> >
> >We calculated cost from the source code using pg_class and
> >pg_statistic, but the cost values at each index were the same.
> >We did not rebuild or update indexes.
> >
> >What are the reasons why the index has been changed besides the
> >following factors?
> > - cost values
> > - Index creation order
>
>
> there are a lot of things which planner has to consider, for instance data
> distribution, the age of the statistics, table and index bloat. You should
> compare the plans (using explain analyse with buffers). an combined index
> over all the columns would be better for this query.
>
>
> >
> >PostgreSQL version : 9.5.2
>
> there are several minor updates available.
>
>
> Regards, Andreas
>
>
> --
> 2ndQuadrant - The PostgreSQL Support Company
>