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

Hi Jagmohan,

The issue is reproducible in my environment.

postgres=# SELECT version();
version
------------------------------------------------------------
PostgreSQL 13.0, compiled by Visual C++ build 1900, 64-bit
(1 row)

postgres=# CREATE TABLE public.test(t *varchar(10)*);
CREATE TABLE
postgres=# CREATE VIEW public.test_view AS SELECT * FROM public.test WHERE
t='10';
CREATE VIEW

postgres=# SELECT pg_get_viewdef('public.test_view');
pg_get_viewdef
----------------------------------------
SELECT test.t +
FROM public.test +
WHERE ((test.t)::text = '10'::text);
(1 row)

@Jagmohan,
Would you please confirm that, whenever you execute the view's base query
without casting, is the query plan showing the index?

On Fri, Mar 26, 2021 at 10:02 AM Jagmohan Kaintura <jagmohan(at)tecorelabs(dot)com>
wrote:

> 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
>

--

Regards,
Dinesh
manojadinesh.blogspot.com