Why does the planner not use an index when a view is involved?
1) A description of what you are trying to achieve and what results you expect.
Why don't plans use indexes when views are involved? A similar query on the underlying table leverages the appropriate index.
== Point 1. The following query leverages the pipl10n_object_name_1 index.
tc=# EXPLAIN ANALYZE select substr(pval_0, 49, 128) from pl10n_object_name where substr(pval_0, 49, 128) = 'xxxx';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pl10n_object_name (cost=4.48..32.15 rows=7 width=32) (actual time=0.040..0.040 rows=0 loops=1)
Recheck Cond: (substr((pval_0)::text, 49, 128) = 'xxxx'::text)
-> Bitmap Index Scan on pipl10n_object_name_1 (cost=0.00..4.48 rows=7 width=0) (actual time=0.039..0.039 rows=0 loops=1)
Index Cond: (substr((pval_0)::text, 49, 128) = 'xxxx'::text)
Planning Time: 0.153 ms
Execution Time: 0.056 ms
(6 rows)
== Point 2. The equivalent query on the VL10N_OBJECT_NAME view executes a Seq Scan on the underlying pl10n_object_name. Why?
tc=# EXPLAIN ANALYZE select pval_0 from VL10N_OBJECT_NAME where pval_0 = 'xxxx';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on vl10n_object_name (cost=0.00..323818.92 rows=5228 width=32) (actual time=2851.799..2851.801 rows=0 loops=1)
Filter: (vl10n_object_name.pval_0 = 'xxxx'::text)
Rows Removed by Filter: 1043308
-> Append (cost=0.00..310749.58 rows=1045547 width=208) (actual time=0.046..2777.167 rows=1043308 loops=1)
-> Seq Scan on pl10n_object_name (cost=0.00..252460.06 rows=870536 width=175) (actual time=0.046..2389.282 rows=870645 loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..44356.42 rows=175011 width=175) (actual time=0.019..313.357 rows=172663 loops=1)
-> Seq Scan on pworkspaceobject (cost=0.00..42168.79 rows=175011 width=134) (actual time=0.016..291.661 rows=172663 loops=1)
Filter: ((pobject_name IS NOT NULL) AND (vla_764_24 = 0))
Rows Removed by Filter: 870629
Planning Time: 0.204 ms
Execution Time: 2851.830 ms
(11 rows)
== Additional Information ==
== View definition:
tc=# \d+ VL10N_OBJECT_NAME
View "public.vl10n_object_name"
Column | Type | Collation | Nullable | Default | Storage | Description
-------------+-----------------------+-----------+----------+---------+----------+-------------
puid | character varying(15) | | | | extended |
locale | text | | | | extended |
preference | text | | | | extended |
status | text | | | | extended |
sequence_no | numeric | | | | main |
pval_0 | text | | | | extended |
View definition:
SELECT pl10n_object_name.puid,
substr(pl10n_object_name.pval_0::text, 1, 5) AS locale,
substr(pl10n_object_name.pval_0::text, 7, 1) AS preference,
substr(pl10n_object_name.pval_0::text, 9, 1) AS status,
tc_to_number(substr(pl10n_object_name.pval_0::text, 11, 4)::character varying) AS sequence_no,
substr(pl10n_object_name.pval_0::text, 49, 128) AS pval_0
FROM pl10n_object_name
UNION ALL
SELECT pworkspaceobject.puid,
'NONE'::text AS locale,
'M'::text AS preference,
'M'::text AS status,
0 AS sequence_no,
pworkspaceobject.pobject_name AS pval_0
FROM pworkspaceobject
WHERE pworkspaceobject.pobject_name IS NOT NULL AND pworkspaceobject.vla_764_24 = 0;
== Table definition:
tc=# \d+ pl10n_object_name
Table "public.pl10n_object_name"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------------------------+-----------+----------+---------+----------+--------------+-------------
puid | character varying(15) | | not null | | extended | |
pseq | integer | | not null | | plain | |
pval_0 | character varying(176) | | | | extended | |
Indexes:
"pipl10n_object_name" PRIMARY KEY, btree (puid, pseq) DEFERRABLE INITIALLY DEFERRED
"pipl10n_object_name_0" btree (pval_0)
"pipl10n_object_name_1" btree (substr(pval_0::text, 49, 128))
"pipl10n_object_name_2" btree (upper(substr(pval_0::text, 49, 128)))
"pipl10n_object_name_3" btree (substr(pval_0::text, 1, 5))
"pipl10n_object_name_4" btree (upper(substr(pval_0::text, 1, 5)))
"pipl10n_object_name_t1" btree (substr(pval_0::text, 1, 5), substr(pval_0::text, 9, 1))
Access method: heap
Options: autovacuum_analyze_scale_factor=0.0, autovacuum_analyze_threshold=1000
** Any help would be greatly appreciated. **
2) The EXACT PostgreSQL version you are running
tc=# SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
3) How you installed PostgreSQL
Unsure... IT department installed it.
4) Changes made to the settings in the postgresql.conf file: see Server Configuration for a quick way to list them all.
tc=# SELECT name, current_setting(name), source
tc-# FROM pg_settings
tc-# WHERE source NOT IN ('default', 'override');
name | current_setting | source
------------------------------+--------------------+----------------------
application_name | psql | client
checkpoint_completion_target | 0.75 | configuration file
checkpoint_timeout | 30min | configuration file
client_encoding | UTF8 | client
DateStyle | ISO, MDY | configuration file
default_text_search_config | pg_catalog.english | configuration file
dynamic_shared_memory_type | posix | configuration file
effective_cache_size | 48GB | configuration file
lc_messages | en_US.UTF-8 | configuration file
lc_monetary | en_US.UTF-8 | configuration file
lc_numeric | en_US.UTF-8 | configuration file
lc_time | en_US.UTF-8 | configuration file
listen_addresses | * | configuration file
log_destination | stderr | configuration file
log_directory | log | configuration file
log_filename | postgresql-%a.log | configuration file
log_line_prefix | %m [%p] | configuration file
log_rotation_age | 1d | configuration file
log_rotation_size | 0 | configuration file
log_timezone | America/Detroit | configuration file
log_truncate_on_rotation | on | configuration file
logging_collector | on | configuration file
maintenance_work_mem | 512MB | configuration file
max_connections | 200 | configuration file
max_locks_per_transaction | 6400 | configuration file
max_stack_depth | 2MB | environment variable
max_wal_size | 1GB | configuration file
min_wal_size | 80MB | configuration file
port | 5432 | configuration file
shared_buffers | 16GB | configuration file
temp_buffers | 256MB | configuration file
TimeZone | America/Detroit | configuration file
wal_buffers | 2MB | configuration file
work_mem | 128MB | configuration file
(34 rows)
5) Operating system and version
# uname -a
Linux vcl6006 3.10.0-1160.25.1.el7.x86_64 #1 SMP Tue Apr 13 18:55:45 EDT 2021 x86_64 x86_64 x86_64 GNU/Linux
6) For questions about any kind of error:
No error.
7) What program you're using to connect to PostgreSQL
psql
8) Is there anything remotely unusual in the PostgreSQL server logs?
Nothing obvious
Why don't plans use indexes when views are involved? A similar query on the underlying table leverages the appropriate index.
== Point 1. The following query leverages the pipl10n_object_name_1 index.
tc=# EXPLAIN ANALYZE select substr(pval_0, 49, 128) from pl10n_object_name where substr(pval_0, 49, 128) = 'xxxx';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pl10n_object_name (cost=4.48..32.15 rows=7 width=32) (actual time=0.040..0.040 rows=0 loops=1)
Recheck Cond: (substr((pval_0)::text, 49, 128) = 'xxxx'::text)
-> Bitmap Index Scan on pipl10n_object_name_1 (cost=0.00..4.48 rows=7 width=0) (actual time=0.039..0.039 rows=0 loops=1)
Index Cond: (substr((pval_0)::text, 49, 128) = 'xxxx'::text)
Planning Time: 0.153 ms
Execution Time: 0.056 ms
(6 rows)
== Point 2. The equivalent query on the VL10N_OBJECT_NAME view executes a Seq Scan on the underlying pl10n_object_name. Why?
tc=# EXPLAIN ANALYZE select pval_0 from VL10N_OBJECT_NAME where pval_0 = 'xxxx';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on vl10n_object_name (cost=0.00..323818.92 rows=5228 width=32) (actual time=2851.799..2851.801 rows=0 loops=1)
Filter: (vl10n_object_name.pval_0 = 'xxxx'::text)
Rows Removed by Filter: 1043308
-> Append (cost=0.00..310749.58 rows=1045547 width=208) (actual time=0.046..2777.167 rows=1043308 loops=1)
-> Seq Scan on pl10n_object_name (cost=0.00..252460.06 rows=870536 width=175) (actual time=0.046..2389.282 rows=870645 loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..44356.42 rows=175011 width=175) (actual time=0.019..313.357 rows=172663 loops=1)
-> Seq Scan on pworkspaceobject (cost=0.00..42168.79 rows=175011 width=134) (actual time=0.016..291.661 rows=172663 loops=1)
Filter: ((pobject_name IS NOT NULL) AND (vla_764_24 = 0))
Rows Removed by Filter: 870629
Planning Time: 0.204 ms
Execution Time: 2851.830 ms
(11 rows)
== Additional Information ==
== View definition:
tc=# \d+ VL10N_OBJECT_NAME
View "public.vl10n_object_name"
Column | Type | Collation | Nullable | Default | Storage | Description
-------------+-----------------------+-----------+----------+---------+----------+-------------
puid | character varying(15) | | | | extended |
locale | text | | | | extended |
preference | text | | | | extended |
status | text | | | | extended |
sequence_no | numeric | | | | main |
pval_0 | text | | | | extended |
View definition:
SELECT pl10n_object_name.puid,
substr(pl10n_object_name.pval_0::text, 1, 5) AS locale,
substr(pl10n_object_name.pval_0::text, 7, 1) AS preference,
substr(pl10n_object_name.pval_0::text, 9, 1) AS status,
tc_to_number(substr(pl10n_object_name.pval_0::text, 11, 4)::character varying) AS sequence_no,
substr(pl10n_object_name.pval_0::text, 49, 128) AS pval_0
FROM pl10n_object_name
UNION ALL
SELECT pworkspaceobject.puid,
'NONE'::text AS locale,
'M'::text AS preference,
'M'::text AS status,
0 AS sequence_no,
pworkspaceobject.pobject_name AS pval_0
FROM pworkspaceobject
WHERE pworkspaceobject.pobject_name IS NOT NULL AND pworkspaceobject.vla_764_24 = 0;
== Table definition:
tc=# \d+ pl10n_object_name
Table "public.pl10n_object_name"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------------------------+-----------+----------+---------+----------+--------------+-------------
puid | character varying(15) | | not null | | extended | |
pseq | integer | | not null | | plain | |
pval_0 | character varying(176) | | | | extended | |
Indexes:
"pipl10n_object_name" PRIMARY KEY, btree (puid, pseq) DEFERRABLE INITIALLY DEFERRED
"pipl10n_object_name_0" btree (pval_0)
"pipl10n_object_name_1" btree (substr(pval_0::text, 49, 128))
"pipl10n_object_name_2" btree (upper(substr(pval_0::text, 49, 128)))
"pipl10n_object_name_3" btree (substr(pval_0::text, 1, 5))
"pipl10n_object_name_4" btree (upper(substr(pval_0::text, 1, 5)))
"pipl10n_object_name_t1" btree (substr(pval_0::text, 1, 5), substr(pval_0::text, 9, 1))
Access method: heap
Options: autovacuum_analyze_scale_factor=0.0, autovacuum_analyze_threshold=1000
** Any help would be greatly appreciated. **
2) The EXACT PostgreSQL version you are running
tc=# SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
3) How you installed PostgreSQL
Unsure... IT department installed it.
4) Changes made to the settings in the postgresql.conf file: see Server Configuration for a quick way to list them all.
tc=# SELECT name, current_setting(name), source
tc-# FROM pg_settings
tc-# WHERE source NOT IN ('default', 'override');
name | current_setting | source
------------------------------+--------------------+----------------------
application_name | psql | client
checkpoint_completion_target | 0.75 | configuration file
checkpoint_timeout | 30min | configuration file
client_encoding | UTF8 | client
DateStyle | ISO, MDY | configuration file
default_text_search_config | pg_catalog.english | configuration file
dynamic_shared_memory_type | posix | configuration file
effective_cache_size | 48GB | configuration file
lc_messages | en_US.UTF-8 | configuration file
lc_monetary | en_US.UTF-8 | configuration file
lc_numeric | en_US.UTF-8 | configuration file
lc_time | en_US.UTF-8 | configuration file
listen_addresses | * | configuration file
log_destination | stderr | configuration file
log_directory | log | configuration file
log_filename | postgresql-%a.log | configuration file
log_line_prefix | %m [%p] | configuration file
log_rotation_age | 1d | configuration file
log_rotation_size | 0 | configuration file
log_timezone | America/Detroit | configuration file
log_truncate_on_rotation | on | configuration file
logging_collector | on | configuration file
maintenance_work_mem | 512MB | configuration file
max_connections | 200 | configuration file
max_locks_per_transaction | 6400 | configuration file
max_stack_depth | 2MB | environment variable
max_wal_size | 1GB | configuration file
min_wal_size | 80MB | configuration file
port | 5432 | configuration file
shared_buffers | 16GB | configuration file
temp_buffers | 256MB | configuration file
TimeZone | America/Detroit | configuration file
wal_buffers | 2MB | configuration file
work_mem | 128MB | configuration file
(34 rows)
5) Operating system and version
# uname -a
Linux vcl6006 3.10.0-1160.25.1.el7.x86_64 #1 SMP Tue Apr 13 18:55:45 EDT 2021 x86_64 x86_64 x86_64 GNU/Linux
6) For questions about any kind of error:
No error.
7) What program you're using to connect to PostgreSQL
psql
8) Is there anything remotely unusual in the PostgreSQL server logs?
Nothing obvious
