> On Mar 25, 2021, at 10:32 PM, Jagmohan Kaintura <jagmo...@tecorelabs.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
If you run the equivalent query against the actual tables what do you see in explain analyze?
