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.teppei@xxxxxxxxxxxxxx> 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 >
