Re: PostgreSQL Views Where Clause Character Columns getting Typecasted to Text and Indexes not getting Used - Mailing list pgsql-general



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