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
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)
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@xxxxxxxxxxxxxx> wrote:
Hi All,PostgreSQL version : 12.5When 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 vLEFT 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_nrEven 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=1Executing 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
--
