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
