Which index is used in the index scan.

From: "Sakai, Teppei" <sakai(dot)teppei(at)jp(dot)fujitsu(dot)com>
To: "'pgsql-general(at)lists(dot)postgresql(dot)org'" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Which index is used in the index scan.
Date: 2018-10-17 05:39:48
Message-ID: 99C4246E73ED1B478BBB9671718426203E3C31E4@G01JPEXMBKW03
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2018-10-17 06:19:32 Re: Problem creating a database
Previous Message Joshua White 2018-10-17 01:31:59 Re: Problem creating a database