Thread: Which index is used in the index scan.
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 PostgreSQL version : 9.5.2 Regards, SAKAI Teppei
On 17 October 2018 07:39:48 CEST, "Sakai, Teppei" <sakai.teppei@jp.fujitsu.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, tableand index bloat. You should compare the plans (using explain analyse with buffers). an combined index over all the columnswould be better for this query. > >PostgreSQL version : 9.5.2 there are several minor updates available. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company
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@jp.fujitsu.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 >