Обсуждение: Views don't seem to use indexes?
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
On Wed, Oct 27, 2021 at 7:31 PM Tim Slechta <trslechta@gmail.com> wrote:
== 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';
Just to confirm and simplify, the question boils down to:
Why does:
SELECT * FROM view WHERE view.view_column = ?;
And view is:
CREATE VIEW AS
SELECT ..., view_column
FROM tbl1
UNION ALL
SELECT ..., view_column
FROM tbl2
;
Where tbl1 has an index on view_column AND tbl2 does not have an index on view_column
Result in a plan where both tb11 and tbl2 are sequentially scanned and the filter applied to the unioned result
Instead of a plan where the index lookup rows of tbl1 are supplied to the union and only tbl2 is sequentially scanned
?
I don't have an answer to offer up here. I'm pretty sure we do handle predicate pushdown into UNION ALL generally. I'm unclear exactly what the equivalently rewritten query would be in this case - but demonstrating that a query that doesn't use union all applies the index while the direct access of the view doesn't isn't sufficient to narrow down the problem. It can still either be the rule processing or the union processing that is seeming to make a wrong plan choice.
That isn't meant to discount the possibility that this case is actually correct - or at least the best we do presently for one or more technical reasons that I'm not familiar with...
David J.
Tim Slechta <trslechta@gmail.com> writes:
> Why does the planner not use an index when a view is involved?
It's not about a "view" ... you'd get the same results if you wrote
out the UNION ALL construct in-line as a sub-select.
I think you may be shooting yourself in the foot by not making sure that
the UNION ALL arms match in data type. You did not show us the definition
of pworkspaceobject, but if pworkspaceobject.pobject_name isn't of type
text (maybe it's varchar?) then the type mismatch would prevent pushing
down a condition on that column. The source code says:
* For subqueries using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can
* push quals into each component query, but the quals can only reference
* subquery columns that suffer no type coercions in the set operation.
* Otherwise there are possible semantic gotchas.
I'm too tired to reconstruct an example of the semantic issues...
regards, tom lane
Tom, David,
Thank you for the time and information.
I lost my system this morning, so I need to re-establish a system and do some additional homework.
Thanks again.
-Tim
BTW: here is the definition of the pworkspaceobject table.
tc=# \d+ pworkspaceobject
Table "public.pworkspaceobject"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
puid | character varying(15) | | not null | | extended | |
pobject_name | character varying(128) | | not null | | extended | |
pobject_desc | character varying(240) | | | | extended | |
pobject_type | character varying(32) | | not null | | extended | |
pobject_application | character varying(32) | | not null | | extended | |
vla_764_7 | integer | | not null | 0 | plain | |
pip_classification | character varying(128) | | | | extended | |
vla_764_10 | integer | | not null | 0 | plain | |
pgov_classification | character varying(128) | | | | extended | |
vla_764_12 | integer | | not null | 0 | plain | |
pfnd0revisionid | character varying(32) | | | | extended | |
vla_764_18 | integer | | not null | 0 | plain | |
vla_764_20 | integer | | not null | 0 | plain | |
rwso_threadu | character varying(15) | | | | extended | |
rwso_threadc | integer | | | | plain | |
prevision_limit | integer | | not null | | plain | |
prevision_number | integer | | not null | | plain | |
rowning_organizationu | character varying(15) | | | | extended | |
rowning_organizationc | integer | | | | plain | |
pactive_seq | integer | | | | plain | |
rowning_projectu | character varying(15) | | | | extended | |
rowning_projectc | integer | | | | plain | |
pfnd0maturity | integer | | | | plain | |
pdate_released | timestamp without time zone | | | | plain | |
pfnd0isrevisiondiscontinued | smallint | | | | plain | |
pfnd0inprocess | smallint | | | | plain | |
aoid | character varying(15) | | not null | NULL::character varying | extended | |
arev_category | integer | | not null | 48 | plain | |
aspace_uid | character varying(15) | | | NULL::character varying | extended | |
avalid_from | timestamp without time zone | | not null | to_timestamp('1900/01/02 00:00:00'::text, 'YYYY/MM/DD HH24:MI:SS'::text)::timestamp without time zone | plain | |
avalid_to | timestamp without time zone | | | | plain | |
vla_764_26 | integer | | not null | 0 | plain | |
pawp0issuspect | smallint | | | | plain | |
vla_764_24 | integer | | not null | 0 | plain | |
vla_764_23 | integer | | not null | 0 | plain | |
Indexes:
"pipworkspaceobject" PRIMARY KEY, btree (puid)
"pipworkspaceobject_0" btree (aoid)
"pipworkspaceobject_1" btree (upper(pobject_type::text))
"pipworkspaceobject_2" btree (upper(pobject_name::text))
"pipworkspaceobject_3" btree (pobject_type)
"pipworkspaceobject_4" btree (pobject_name)
"pipworkspaceobject_5" btree (rwso_threadu)
"pipworkspaceobject_6" btree (rowning_projectu)
Access method: heap
Options: autovacuum_analyze_scale_factor=0.0, autovacuum_analyze_threshold=500
Table "public.pworkspaceobject"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
puid | character varying(15) | | not null | | extended | |
pobject_name | character varying(128) | | not null | | extended | |
pobject_desc | character varying(240) | | | | extended | |
pobject_type | character varying(32) | | not null | | extended | |
pobject_application | character varying(32) | | not null | | extended | |
vla_764_7 | integer | | not null | 0 | plain | |
pip_classification | character varying(128) | | | | extended | |
vla_764_10 | integer | | not null | 0 | plain | |
pgov_classification | character varying(128) | | | | extended | |
vla_764_12 | integer | | not null | 0 | plain | |
pfnd0revisionid | character varying(32) | | | | extended | |
vla_764_18 | integer | | not null | 0 | plain | |
vla_764_20 | integer | | not null | 0 | plain | |
rwso_threadu | character varying(15) | | | | extended | |
rwso_threadc | integer | | | | plain | |
prevision_limit | integer | | not null | | plain | |
prevision_number | integer | | not null | | plain | |
rowning_organizationu | character varying(15) | | | | extended | |
rowning_organizationc | integer | | | | plain | |
pactive_seq | integer | | | | plain | |
rowning_projectu | character varying(15) | | | | extended | |
rowning_projectc | integer | | | | plain | |
pfnd0maturity | integer | | | | plain | |
pdate_released | timestamp without time zone | | | | plain | |
pfnd0isrevisiondiscontinued | smallint | | | | plain | |
pfnd0inprocess | smallint | | | | plain | |
aoid | character varying(15) | | not null | NULL::character varying | extended | |
arev_category | integer | | not null | 48 | plain | |
aspace_uid | character varying(15) | | | NULL::character varying | extended | |
avalid_from | timestamp without time zone | | not null | to_timestamp('1900/01/02 00:00:00'::text, 'YYYY/MM/DD HH24:MI:SS'::text)::timestamp without time zone | plain | |
avalid_to | timestamp without time zone | | | | plain | |
vla_764_26 | integer | | not null | 0 | plain | |
pawp0issuspect | smallint | | | | plain | |
vla_764_24 | integer | | not null | 0 | plain | |
vla_764_23 | integer | | not null | 0 | plain | |
Indexes:
"pipworkspaceobject" PRIMARY KEY, btree (puid)
"pipworkspaceobject_0" btree (aoid)
"pipworkspaceobject_1" btree (upper(pobject_type::text))
"pipworkspaceobject_2" btree (upper(pobject_name::text))
"pipworkspaceobject_3" btree (pobject_type)
"pipworkspaceobject_4" btree (pobject_name)
"pipworkspaceobject_5" btree (rwso_threadu)
"pipworkspaceobject_6" btree (rowning_projectu)
Access method: heap
Options: autovacuum_analyze_scale_factor=0.0, autovacuum_analyze_threshold=500
On Thu, Oct 28, 2021 at 1:15 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Tim Slechta <trslechta@gmail.com> writes:
> Why does the planner not use an index when a view is involved?
It's not about a "view" ... you'd get the same results if you wrote
out the UNION ALL construct in-line as a sub-select.
I think you may be shooting yourself in the foot by not making sure that
the UNION ALL arms match in data type. You did not show us the definition
of pworkspaceobject, but if pworkspaceobject.pobject_name isn't of type
text (maybe it's varchar?) then the type mismatch would prevent pushing
down a condition on that column. The source code says:
* For subqueries using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can
* push quals into each component query, but the quals can only reference
* subquery columns that suffer no type coercions in the set operation.
* Otherwise there are possible semantic gotchas.
I'm too tired to reconstruct an example of the semantic issues...
regards, tom lane