9.8.1 Enhanced Query Plan Stability

Fujitsu Enterprise Postgres estimates the cost of query plans based on SQL statements and database statistics, and selects the least expensive query plan. However, like other databases, Fujitsu Enterprise Postgres does not necessarily select the most suitable query plan. For example, it may suddenly select unsuitable query plan due to changes in the data conditions.

If you know that the statistics will not change significantly, you can control the selection of the same query plan by directly specifying which query plan to select in pg_hint_plan, or by fixing the statistics referenced by the planner in pg_dbms_stats.

Also, pg_dbms_stats from Fujitsu Enterprise Postgres can fix statistics (height of Btree indexes) that OSS pg_dbms_stats cannot account for. Refer to "9.8.1.1 Fixing the Height of a Btree Index".

See

For information about setting up pg_hint_plan and pg_dbms_stats, refer to "pg_hint_plan" and "pg_dbms_stats" in the Installation and Setup Guide for Server.
For basic usage of pg_hint_plan and pg_dbms_stats, refer to below.
- Control execution plans with pg_hint_plan
  https://www.postgresql.fastware.com/postgresql-insider-tun-hint-plan
- Control execution plans by fixing statistics with pg_dbms_stats
  https://www.postgresql.fastware.com/postgresql-insider-tun-dbms-stt
For more information about pg_hint_plan and pg_dbms_stats, refer to the OSS web page.

When PostgreSQL creates a query plan, it also references the height of the Btree index to be used as part of the statistics. However, OSS pg_dbms_stats does not include the height of the Btree index in its fixed statistics. The height of a Btree index is also fixed in pg_dbms_stats for Fujitsu Enterprise Postgres.

Fixing the height of a Btree index covers Fujitsu Enterprise Postgres 16 and later, so to maintain compatibility with earlier versions, specify something like:

PostgreSQL 9.2 and earlier compatibility
```
pg_dbms_stats.use_tree_height = off
```
Fujitsu Enterprise Postgres 15 SP2 and earlier compatibility
```
pg_dbms_stats.lock_tree_height = off
```

See the table below for details on each parameter. If you want the parameter value to be valid for all sessions, specify it in postgresql.conf and then reload.

Parameter	Specified value	Default
pg_dbms_stats.use_tree_height	Specify whether to include the Btree index height in cost calculation and plan generation when statistics are fixed. on: Includes Btree index height in cost calculations and plan generation. off: Do not include Btree index height in cost calculation or plan generation.	on
pg_dbms_stats.lock_tree_height	Specify whether to also fix the height of the Btree index when you fix the statistics. Note that this is valid only when the specified value of pg_dbms_stats.use_tree_height is "on". on: Fixes the height of the Btree index at the height when locked. off: The height of the Btree index is not fixed and varies depending on the amount of data in the index.	on

Parameter

Specified value

Default

pg_dbms_stats.use_tree_height

Specify whether to include the Btree index height in cost calculation and plan generation when statistics are fixed.

on: Includes Btree index height in cost calculations and plan generation.

off: Do not include Btree index height in cost calculation or plan generation.

pg_dbms_stats.lock_tree_height

Specify whether to also fix the height of the Btree index when you fix the statistics.
Note that this is valid only when the specified value of pg_dbms_stats.use_tree_height is "on".

on: Fixes the height of the Btree index at the height when locked.

off: The height of the Btree index is not fixed and varies depending on the amount of data in the index.

When you want to import statistics from your legacy environment

The format of files handled by the import/export functions of this version of pg_dbms_stats is different from the format of pg_dbms_stats in OSS or Fujitsu Enterprise Postgres 15SP2 or earlier. Therefore, statistics exported from pg_dbms_stats for OSS or Fujitsu Enterprise Postgres 15SP2 or earlier should be imported using the dbms_stats.import_<obj>_stats_no_tree_height function.

How to specify the dbms_stats.import_<obj>_stats_no_tree_height function

dbms_stats.import_<obj>_stats_no_tree_height('Absolute path of export file')

<obj> indicates a database (currently connected), schema, table, or column.

Import using the dbms_stats.import_<obj>_stats function provided by OSS is not possible.

If you use the wrong import function, an error message containing the following HINT will be output. Check the combination of input file and import function type, and use the correct import function.

HINT:  The import function may be incorrectly combined with the format of the exported data. Please check the documentation for the relationship between the import function and the available data.