BUG #15227: Planner often ignores covering indexes (with includeclause) - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15227
Logged by:          Hans Buschmann
Email address:      buschmann@nidsa.net
PostgreSQL version: 11beta1
Operating system:   Fedora 28 64bit
Description:

I am trying to prepare our indexing schema for the use of covering
indexes.
Currently our Production runs on Windows 64bit PG 10.4

I have a newly set up test environment under FEDORA 28 64bit, PG 11beta self
compiled

 PostgreSQL 11beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.1.1
20180502 (Red Hat 8.1.1-1), 64-bit

The goal is to achieve more index only scans with the most common columns
for our OLTP environment.
A copy of the production Data has been imported and analyzed.
The normal query performance is comparable to PG 10.4

I encountered different problem cases where specially changed indexes with
include clause where not used:


1. Primary key

I changed the primary key on table projects from
Indexes:
    "projects_active_pkey" PRIMARY KEY, btree (id_pr)
    "projects_active_pr_season_pr_cli_code_pr_name_key" UNIQUE CONSTRAINT,
btree (pr_season, pr_cli_code, pr_name)
to
Indexes:
    "projects_pkeyp" PRIMARY KEY, btree (pr_season, id_pr) INCLUDE
(pr_cli_code, pr_fac_code, pr_name, pr_style, pr_photo_default, pr_last)
    "projects_active_pr_season_pr_cli_code_pr_name_key" UNIQUE CONSTRAINT,
btree (pr_season, pr_cli_code, pr_name) INVALID
and disabled the other index with 

update pg_index set indisvalid = false where indexrelid =
'projects_active_pr_season_pr_cli_code_pr_name_key'::regclass;

Then I got a plan change from     

Index Scan using projects_active_pr_season_pr_cli_code_pr_name_key on
projects (cost=0.28..106.74 rows=1,990 width=53) (actual time=0.007..0.596
rows=1,990 loops=1)

    Index Cond: (pr_season = 26)
to  
Seq Scan on public.projects (cost=0.00..114.80 rows=1,990 width=53) (actual
time=0.036..0.451 rows=1,990 loops=1)

    Output: projects.id_pr, projects.pr_last, projects.pr_style,
projects.pr_photo_default, projects.pr_cli_code, projects.pr_season
    Filter: (projects.pr_season = 26)
    Rows Removed by Filter: 2154

the table projects is joined with
    LEFT JOIN ONLY projects ON id_pr=am_id_pr and pr_season=of_season
the of_season/pr_season in this query is literal constant 26

When the other index (projects_active_pr_season_pr_cli_code_pr_name_key) is
active, it is choosen over the changed primary key for index scan.

The changed primary key is never used.


2. Choosing between comparable indexes

On another table (models) I created an extra index:
from (10.4)
Indexes:
    "models_active_pkey" PRIMARY KEY, btree (id_am)
    "models_active_am_season_am_id_pr_am_style_ref_am_clis_sub_c_key" UNIQUE
CONSTRAINT, btree (am_season, am_id_pr, am_style_ref, am_clis_sub_code)
    "models_active_am_cancel_am_conf_date_idx" btree (am_cancel,
am_conf_date)
to (11beta1)
Indexes:
    "models_active_pkey" PRIMARY KEY, btree (id_am)
    "models_active_am_season_am_id_pr_am_style_ref_am_clis_sub_c_key" UNIQUE
CONSTRAINT, btree (am_season, am_id_pr, am_style_ref, am_clis_sub_code)
INVALID
    "ukp_models_season_id" UNIQUE, btree (am_season, id_am) INCLUDE
(am_fac_code, am_id_pr, am_clis_sub_code, am_fac_id_cu, am_our_id_cu,
am_style_ref)
    "models_active_am_cancel_am_conf_date_idx" btree (am_cancel,
am_conf_date)

when the second index
(models_active_am_season_am_id_pr_am_style_ref_am_clis_sub_c_key) is not
invalidated, it is choosen over the new third index ukp_models_season_id
This gives the same plan as in 10.4

Index Scan using
models_active_am_season_am_id_pr_am_style_ref_am_clis_sub_c_key on
public.models (cost=0.28..73.01 rows=990 width=32) (actual time=0.006..0.222
rows=990 loops=1) 

When the second index
models_active_am_season_am_id_pr_am_style_ref_am_clis_sub_c_key is
disabled,
The plan changes to the Index Only scan using the new Index as intended:


Index Only Scan using ukp_models_season_id on public.models
(cost=0.28..74.01 rows=990 width=32) (actual time=0.011..0.270 rows=990
loops=1)

    Output: models.am_season, models.id_am, models.am_fac_code,
models.am_id_pr, models.am_clis_sub_code, models.am_fac_id_cu,
models.am_our_id_cu, models.am_style_ref
    Index Cond: (models.am_season = 26)
    Heap Fetches: 990

It is not clear which indexes get prioritized and if a possible
index_only_scan is preferrable considered with the included extra columns.


3. Correct usage of a covering index when a partial index is changed

After adding the third index to the table clients (iotp_recent_cli_codigo)
Indexes:
    "clients_pkey" PRIMARY KEY, btree (cli_codigo)
    "clients_cli_nombre_key" UNIQUE CONSTRAINT, btree (cli_nombre)
    "iotp_recent_cli_codigo" UNIQUE, btree (cli_codigo) INCLUDE (cli_id_off,
cli_id_ctry, cli_id_usr_responsible, cli_nombre, cli_short_name, cli_group)
WHERE cli_recent_act IS NOT NULL
    "iot_cli_id_off_codigo_nombre_group" btree (cli_id_off, cli_codigo,
cli_group, cli_nombre)
    "iotp_clients_nombre_rlv_codigo_responsible" btree (cli_nombre,
cli_id_off, cli_codigo, cli_id_usr_responsible) WHERE cli_id_rlv <= 2

the plan changes correctly to 
 Index Only Scan using iotp_recent_cli_codigo on public.clients
(cost=0.14..13.57 rows=98 width=16) (actual time=0.006..0.035 rows=98
loops=1)

    Output: clients.cli_id_off, clients.cli_group,
clients.cli_id_usr_responsible, clients.cli_codigo
    Heap Fetches: 98

So partial index is no obstacle

The query is quite complex (refresh materialized view for caching), so the
complete data definitions and Explain analyze plans are not shown here.

I have tried to isolate the two problematic cases I encountered so far.

Please inform me, when I have missed something

Thanks

Hans Buschmann