Re: Views don't seem to use indexes? - Mailing list pgsql-performance
| From | Tim Slechta |
|---|---|
| Subject | Re: Views don't seem to use indexes? |
| Date | |
| Msg-id | CAJVU3y3odLD3b1inQDd+=+nJDE3U=mnpFnF7SeuYqOUhO4G37w@mail.gmail.com Whole thread Raw |
| In response to | Re: Views don't seem to use indexes? (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-performance |
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
pgsql-performance by date: