From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | buschmann(at)nidsa(dot)net |
Subject: | BUG #15227: Planner often ignores covering indexes (with include clause) |
Date: | 2018-06-03 18:48:03 |
Message-ID: | 152805168369.26715.12547441632848418945@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15227
Logged by: Hans Buschmann
Email address: buschmann(at)nidsa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-06-03 21:33:50 | Re: BUG #15227: Planner often ignores covering indexes (with include clause) |
Previous Message | Ádám Maracska | 2018-06-03 18:14:59 | Crashed libpq.dll in a multithreaded environment in case of SSL connection + callstack |