From: <Masahiro.Ikeda@nttdata.com>
To: <nagata@sraoss.co.jp>
CC: <pgsql-hackers@lists.postgresql.org>, <Masao.Fujii@nttdata.com>
Subject: RE: Improve EXPLAIN output for multicolumn B-Tree Index
Thread-Topic: Improve EXPLAIN output for multicolumn B-Tree Index
Thread-Index: AdrDpKhVBnEGANC4SCWCuOW3/7+ItgAS2HGAAHndHcA=
Date: Mon, 24 Jun 2024 02:52:10 +0000
Deferred-Delivery: Mon, 24 Jun 2024 02:45:20 +0000
Message-ID: 
 <TYWPR01MB109823CB6D43D728D2255D54DB1D42@TYWPR01MB10982.jpnprd01.prod.outlook.com>
References: 
 <TYWPR01MB1098260B694D27758FE2BA46FB1C92@TYWPR01MB10982.jpnprd01.prod.outlook.com>
 <20240622003109.6a4db3d72cd296cc3a89c4c9@sraoss.co.jp>
In-Reply-To: <20240622003109.6a4db3d72cd296cc3a89c4c9@sraoss.co.jp>
Accept-Language: ja-JP, en-US
Content-Language: ja-JP
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?iso-2022-jp?B?UVhVRUlZa2JiV2pOSjNwVGFuWGNVQWFDR2twaDVtOGVQeDA5R1YzT2xk?=
 =?iso-2022-jp?B?b3k3ekhmY2hZLzlWbWZ4ZzJXOUlmbGZzVGJ2ZUJYNkQ2aVpkdTdxZm9y?=
 =?iso-2022-jp?B?LzdraEFWeXZOayt0QjU5VUZMZjZrdS9IL2I4NW9Zdk1qSW0ydHZYNVJr?=
 =?iso-2022-jp?B?ckl1QUN1L0ovWVBlWktuS1BEQ1lheUEyTi9hejNNQzdnbUlpT1QydFJx?=
 =?iso-2022-jp?B?aEJFUFJRN3hTZEpXbnJMWEtwTzRZWU42UFRxYVRWTzY1L2NHT3Q0THNV?=
 =?iso-2022-jp?B?RDhma1RjMWY1cGJkYTV2TUNZVzY2Vm9wUDRBZXhTQXpNZjV4Y0ZFWWFG?=
 =?iso-2022-jp?B?WnFCZml5QUhjdjRlMTArTDhmYVlRekgydFZ3ZnZsd2hQaVRoamd3Y0Zk?=
 =?iso-2022-jp?B?K3RPUC8wRm1FNEU4TU1qc0Y5aFlJTk91R2RZbEFVYTEzWUVNZjdZaklW?=
 =?iso-2022-jp?B?N1ZSWTFla2U5bEtYRnRqSXgxajcxZ1VuS1UyY2JXVGdsSjFxVWFrdHVU?=
 =?iso-2022-jp?B?YnVCeUtjbFJtZlIzQ1Ryam4wWTJYYU9ybE41WHJNZkFQZXBDd3BEYjIv?=
 =?iso-2022-jp?B?bDE5RjRLN05PMDhHQkwrdDN4cnJUclpSVlIwTDg2VGRQTm5Xc2pOY1NZ?=
 =?iso-2022-jp?B?bHZ4SktrRW80SUpzdDVOcTVUQkZJTjFqY1drSjJ3YXBWUldRbGFDTDFC?=
 =?iso-2022-jp?B?a1d3d0psVFkybDZxTlErK0hURlQ0RGs4aldDNlQyY2dpZVNtdjBUaXF2?=
 =?iso-2022-jp?B?SHBQQU1BTXF6QzRzWi80NVZuSEdtMjVJZHZEZlhzVDZlcXcycXQ5emVM?=
 =?iso-2022-jp?B?VU1GRy9wSTNoZWNyOTRtZ3hUYmZBQ2Z1Uk85Y3BHK2pxSm1VNEY4MVFu?=
 =?iso-2022-jp?B?L3Q4c3dPaVBiVjJoUmp5RXVQNDU4c0tjdjc0UlNzaU5VZDlJdEc2T0tu?=
 =?iso-2022-jp?B?VlNodlJXR1ExeENmQm9uV2tBMkdWcUVYU1hPejM4Z2FYRHBENDRvaEp1?=
 =?iso-2022-jp?B?NDN3eHZOS1lJeEg0MlRsNW9CL3ZqTVpjbDZ1Nzh4cE54MzY3cFRBbWNK?=
 =?iso-2022-jp?B?cXhQTmVKeHBUQUxOcFFhK2d0VVV1Rjd6M0gwM2dyU2pUNk5wVitHekxR?=
 =?iso-2022-jp?B?ZVBTMkd0S0ZoS25JOThJMW5MOFlTZENKbmVFT05tZHJ1dm93STV1QTRI?=
 =?iso-2022-jp?B?T3YxdDlWOXhDY2JwMWRZRGQ5TElhdUk1QW1XNmszMExLOWp0Rzk3dVov?=
 =?iso-2022-jp?B?aTlZZVluQWQ5MDZOdE9SWjB0SEI2NzhYb0lCMHdrdnF2U0VkMVdYckFO?=
 =?iso-2022-jp?B?dFcvU0RFVENENGQwVzZlK0VBcWFuMVNoclRDVVlhT1F4ZjhhTHRmNmJJ?=
 =?iso-2022-jp?B?amNZVytIeGFqUElrMGhiVDJNaUhkc29Xc2ExRU9iSVJvVHJoSW9ZVVRK?=
 =?iso-2022-jp?B?RE9ENlBOcEFHWnFXMmNncHJGOFVqMkt4THA1Wlk4NkdINkJJdDgrYllG?=
 =?iso-2022-jp?B?eXZjd01mK2srS1IzRjFQb2NZTnNsNEsyTXdsOEczaUlCaGtHdFRzZjJX?=
 =?iso-2022-jp?B?QkJHWDdLZ2lnSHlhcnFWNUI0dENYWmltNlpMeGN0UjZtcGJtbE5Dcmti?=
 =?iso-2022-jp?B?MUpKNDd1OHJxWjFGK2xKenVYc1ZpeElTaWlVMXZ3TEJDNU9oRmxWOGkz?=
 =?iso-2022-jp?B?dS9lMk9HYjBKckVSeEllcWl1bkFMU2kxcWJoUGxrTG5DUCtlcVJQTjhT?=
 =?iso-2022-jp?B?dkIzQjFIelJQR05WTjNodGNrQi9MWklaVDE4bWJmOEI1UktSRDIrdVFj?=
 =?iso-2022-jp?B?eVg0c0dHWmx2TjdncUtwWG9DMXdmVWt0aldIYmFCUW0zQ2VJTC8yNHQ4?=
 =?iso-2022-jp?B?SlMxVnZjeEhvbStWVmVBVEJUbjZnUWRzNFlHcVRWWlltaUVocW5rWmlU?=
 =?iso-2022-jp?B?ZDBPdEJwRURzSHdZRGNsKzlOdUVkbFNIa1R6ZjlHTlRXUG1KU3k1OHp6?=
 =?iso-2022-jp?B?V3NGYi9JcHRrSmQ2VS9XbzVNUC9XODFVTFJqUEIwQ0JkQzE2S2F2MzJ5?=
 =?iso-2022-jp?B?SjZxZm5IbmNna3RjckVnT2JkWVUxOTdOZkhkQzlLUGkvSDE3dG0xTlFQ?=
 =?iso-2022-jp?B?NHRUZm0rMGN5VUtjdTByRWpJdFlOck1SZFZZdmx1SUs3TURWd1hPR0h3?=
 =?iso-2022-jp?B?V1BJRXg1eEQ1TzRUSVl5QkN6cVQvaGt0bWJwMXl1S2twNzlxSklCQmJG?=
 =?iso-2022-jp?B?UDNzK3pvM0dGWTZ3UHZPb0Jpdit3V09nR01pZEZuYllGNmFZdm5veFNl?=
 =?iso-2022-jp?B?K0UzTXFPZ05STTJCaE9aVlYrQ2VET2ZOVFE9PQ==?=
Content-Type: text/plain; charset="iso-2022-jp"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: jp.nttdata.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: TYWPR01MB10982.jpnprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 592e3359-de3c-4487-806a-08dc93f8b25a
X-MS-Exchange-CrossTenant-originalarrivaltime: 24 Jun 2024 02:52:33.2608
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 1fcf450d-bb71-4efd-ae5d-90c7be757e12
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: W+mDFTYs8eXVru3svgy5kWEe8YzHiaS2hC6QvidM1oID5zN0uUgBcRNiaPjofJvbMrkynW0kAeljQnLmefuxTGJj7ouf/zoisq/hqW3cHZI=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TY3PR01MB10140
X-Proofpoint-Virus-Version: vendor=baseguard
 engine=ICAP:2.0.293,Aquarius:18.0.1039,Hydra:6.0.680,FMLib:17.12.28.16
 definitions=2024-06-24_02,2024-06-21_01,2024-05-17_01
X-Proofpoint-Spam-spam_all_notspam: 1
X-Proofpoint-GUID: 3y0ZTqD68vmd15ZE-Tu5KbwcuauBU5uD
X-Proofpoint-ORIG-GUID: 3y0ZTqD68vmd15ZE-Tu5KbwcuauBU5uD
X-Proofpoint-Virus-Version: vendor=baseguard
 engine=ICAP:2.0.293,Aquarius:18.0.1039,Hydra:6.0.680,FMLib:17.12.28.16
 definitions=2024-06-24_02,2024-06-21_01,2024-05-17_01
X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 mlxscore=0 suspectscore=0 phishscore=0
 adultscore=0 mlxlogscore=635 lowpriorityscore=0 impostorscore=0
 priorityscore=1501 bulkscore=0 spamscore=0 clxscore=1015 malwarescore=0
 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.19.0-2406140001
 definitions=main-2406240022
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: <https://www.postgresql.org/message-id/TYWPR01MB109823CB6D43D728D2255D54DB1D42%40TYWPR01MB10982.jpnprd01.prod.outlook.com>
Precedence: bulk

> > * Is this feature useful? Is there a possibility it will be accepted?
>=20
> I think adding such information to EXPLAIN outputs is useful because it w=
ill help users
> confirm the effect of a multicolumn index on a certain query and decide t=
o whether
> leave, drop, or recreate the index, and so on.

Thank you for your comments and for empathizing with the utility of the app=
roach.

> > * Are there any other ideas for determining if multicolumn indexes are
> >
> > being used efficiently? Although I considered calculating the
> > efficiency using
> >
> > pg_statio_all_indexes.idx_blks_read and
> > pg_stat_all_indexes.idx_tup_read,
> >
> >  I believe improving the EXPLAIN output is better because it can be
> > output
> >
> > per query and it's more user-friendly.
>=20
> It seems for me improving EXPLAIN is a natural way to show information on=
 query
> optimization like index scans.

OK, I'll proceed with the way.

> > * Is "Index Bound Cond" the proper term?I also considered changing
> >
> > "Index Cond" to only show quals for the boundary condition and adding
> >
> > a new term "Index Filter".
>=20
> "Index Bound Cond" seems not intuitive for me because I could not find de=
scription
> explaining what this means from the documentation. I like "Index Filter" =
that implies the
> index has to be scanned.

OK, I think you are right. Even at this point, there are things like =1B$B!=
F=1B(BFilter=1B$B!G=1B(B and
=1B$B!F=1B(BRows Removed by Filter=1B$B!G=1B(B, so it seems natural to alig=
n with them. I described a
new output example in the previous email, how about that?

> > * Would it be better to add new interfaces to Index AM? Is there any
> > case
> >
> >   to output the EXPLAIN for each index context? At least, I think it's
> > worth
> >
> >   considering whether it's good for amcostestimate() to modify the
> >
> >   IndexPath directly as the PoC patch does.
>=20
> I am not sure it is the best way to modify IndexPath in amcostestimate(),=
 but I don't
> have better ideas for now.

OK, I=1B$B!G=1B(Bll consider what the best way to change is. In addition, i=
f we add
"Rows Removed by Index Filter", we might need to consider a method to recei=
ve the
number of filtered tuples at execution time from Index AM.

Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION