Re: Views don't seem to use indexes?

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

On Thu, Oct 28, 2021 at 1:15 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Tim Slechta <trslechta(at)gmail(dot)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
>