PostgreSQL Views Where Clause Character Columns getting Typecasted to Text and Indexes not getting Used

Hi All,

PostgreSQL version : 12.5

When we are compiling View definition into database , the where clause on
the Character Columns is getting internally typecasted to text.
Example : Source Code being Applied :

" FROM tms_vessel_visit_aggregate a, tms_vessel_visit v
LEFT OUTER JOIN tms_vsl_svc s ON (v.svc_c = s.svc_c)
WHERE v.vessel_visit_c = a.vessel_visit_c and v.vessel_call_nr =
a.vessel_call_nr
"

Now when it's stored internally in the database the columns are getting
typecast with *text *but those table columns are not text they are
varchar(10).

Example :
FROM tms_vessel_visit_aggregate a,
tms_vessel_visit v
LEFT JOIN tms_vsl_svc s ON *v.svc_c::text* = *s.svc_c::text*
WHERE *v.vessel_visit_c::text* = *a.vessel_visit_c::text* AND
v.vessel_call_nr = a.vessel_call_nr

Even when we placed the casting in the Original Source to varchar(10), its
typecasting internally to *::text .*

These Columns are Primary Key columns and because of this Type Casting on
those columns Index scan is not happening and we are always getting
Sequential Scan.
Example :

-> Subquery Scan on "*SELECT* 1" (cost=36.88..115.01
rows=995 width=13) (actual time=0.763..3.144 rows=995 loops=1)
Buffers: shared hit=65
-> Hash Join (cost=36.88..105.06 rows=995
width=6361) (actual time=0.763..2.964 rows=995 loops=1)

* Hash Cond: (((v.vessel_visit_c)::text =
(a.vessel_visit_c)::text) AND (v.vessel_call_nr = a.vessel_call_nr))*
Buffers: shared hit=65

* -> Seq Scan on tms_vessel_visit v
(cost=0.00..62.95 rows=995 width=18) (actual time=0.006..0.292 rows=995
loops=1)* Buffers: shared hit=53
-> Hash (cost=21.95..21.95 rows=995
width=13) (actual time=0.735..0.736 rows=995 loops=1)
Buckets: 1024 Batches: 1 Memory
Usage: 52kB
Buffers: shared hit=12

* -> Seq Scan on
tms_vessel_visit_aggregate a (cost=0.00..21.95 rows=995 width=13) (actual
time=0.009..0.238 rows=995 loops=1) *
Buffers: shared hit=12
-> Subquery Scan on "*SELECT* 2" (cost=1.35..2.86
rows=1 width=88) (actual time=0.079..0.111 rows=14 loops=1)
Buffers: shared hit=2
-> Hash Join (cost=1.35..2.85 rows=1
width=6422) (actual time=0.078..0.107 rows=14 loops=1)

* Hash Cond: (((a_1.vessel_visit_c)::text =
(v_1.vessel_visit_c)::text) AND (a_1.vessel_call_nr = v_1.vessel_call_nr))
* Buffers: shared hit=2

* -> Seq Scan on tms_vessel_visit_aggregate_bak
a_1 (cost=0.00..1.33 rows=33 width=60) (actual time=0.007..0.011
rows=33loops=1) * Buffers: shared hit=1
-> Hash (cost=1.14..1.14 rows=14
width=98) (actual time=0.043..0.043 rows=14 loops=1)
Buckets: 1024 Batches: 1 Memory
Usage: 9kB
Buffers: shared hit=1

* -> Seq Scan on tms_vessel_visit_bak v_1
(cost=0.00..1.14 rows=14 width=98) (actual time=0.007..0.034 rows=14
loops=1) * Buffers: shared hit=1

Executing Same Stuff in Oracle as its not doing implicit typecasting it
gives us perfect Index scans as expected.

| 8 | UNION ALL PUSHED PREDICATE | |
| | | |
| 9 | NESTED LOOPS | |
1 | 30 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| TMS_VESSEL_VISIT_AGGREGATE |
1 | 12 | 2 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | TMS_VESSEL_VISIT_AGGREGATE_PK |
1 | | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| TMS_VESSEL_VISIT |
1 | 18 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | TMS_VESSEL_VISIT_PK |
1 | | 0 (0)| 00:00:01 |
| 14 | NESTED LOOPS | |
1 | 29 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID| TMS_VESSEL_VISIT_AGGREGATE_BAK |
1 | 12 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | TMS_VESSEL_VISIT_AGG_BAK_PK |
1 | | 0 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID| TMS_VESSEL_VISIT_BAK |
1 | 17 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | TMS_VESSEL_VISIT_BAK_PK |
1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

What can be done to Resolve this Issue so that implicit casting is not
happening in the compiled Views. Any type of casting at Original source
code is everytime being converted to typecasted text in database.

Please help us in identifying the solution.

--
*Best Regards,*
Jagmohan