[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: PostgreSQL Views Where Clause Character Columns getting Typecasted to Text and Indexes not getti
From: Jagmohan Kaintura <jagmohan () tecorelabs ! com>
Date: 2021-03-30 4:33:12
Message-ID: CA+cYFtsw0xucDXjUEkikw1ajYsywXxt=jYgJLnN6HftrT-00yA () mail ! gmail ! com
[Download RAW message or body]
Hi Tom,
Thanks for your analysis!!
As we were discussing on the typecasted column not able to use the indexes
as base table columns are with datatype varchar and they are type casted to
text internally .
We made some more analysis and tried to create some test tables with
columns datatype as *text *which are being referenced in the *where* clause.
tms_vessel_visit_aggregate_test
tms_owner_cz1qa@zdcq12c2:5434=> \d tms_vessel_visit_aggregate_test
Table "tms_owner_cz1qa.tms_vessel_visit_aggregate_test"
Column | Type | Collation |
Nullable | Default
-------------------------+--------------------------------+-----------+----------+---------
vessel_visit_c | text | | not
null |
....
tms_vessel_visit_aggregate_bak_test
tms_owner_cz1qa@zdcq12c2:5434=> \d tms_vessel_visit_aggregate_bak_test
Table "tms_owner_cz1qa.tms_vessel_visit_aggregate_bak_test"
Column | Type | Collation |
Nullable | Default
-------------------------+--------------------------------+-----------+----------+---------
vessel_visit_c | text | | not
null |
...
tms_vessel_visit_test
tms_owner_cz1qa@zdcq12c2:5434=> \d tms_vessel_visit_test
Table "tms_owner_cz1qa.tms_vessel_visit_test"
Column | Type | Collation
| Nullable | Defa
ult
-------------------------------+--------------------------------+-----------+----------+-------
-------
vessel_visit_c | text |
| not null |
....
tms_vessel_visit_bak_test
...
vessel_visit_c | text |
| not null |
....
After above modification created a new View TMV_VESSEL_VISIT_TEXT_VERIFY.
Now as the base columns are *text and *typecast is also* text. *We are
getting a good optimum plan as we expected.
The application call which was earlier taking 7-8 seconds for execution is
now taking 900-1100 msec. It got improved by many factors.
From the application single call view is getting executed 4k times.
Aggregate (cost=47.91..47.92 rows=1 width=32) (actual time=0.021..0.024
rows=1 loops=1)
Buffers: shared hit=5
-> Nested Loop (cost=4.49..47.91 rows=1 width=0) (actual
time=0.019..0.021 rows=0 loops=1)
Buffers: shared hit=5
-> Nested Loop (cost=0.57..41.83 rows=1 width=38) (actual
time=0.019..0.020 rows=0 loops=1)
Join Filter: ((t.trgt_vvd_n)::text = "*SELECT*
1".vessel_visit_c)
Buffers: shared hit=5
* -> Index Scan using tms_tdr_pk on tms_tdr t
(cost=0.29..2.50 rows=1 width=7) (actual time=0.011..0.012 rows=1 loops=1)*
Index Cond: (tdr_id = '13581258'::numeric)
Buffers: shared hit=3
-> Append (cost=0.28..39.30 rows=2 width=70) (actual
time=0.005..0.006 rows=0 loops=1)
Buffers: shared hit=2
-> Subquery Scan on "*SELECT* 1" (cost=0.28..19.65
rows=1 width=70) (actual time=0.002..0.003 rows=0 loops=1)
Buffers: shared hit=1
-> Nested Loop (cost=0.28..19.64 rows=1
width=6418) (actual time=0.002..0.002 rows=0 loops=1)
Buffers: shared hit=1
* -> Index Scan using
tms_vessel_visit_test_vessel_visit_c_idx on tms_vessel_visit_test v
(cost=0.14..9.09 rows=10 width=80) (actual time=0.002..0.002 rows=0
loops=1) * Buffers: shared hit=1
* -> Index Scan using
tms_vessel_visit_aggregate_test_vessel_visit_c_idx on
tms_vessel_visit_aggregate_test a (cost=0.14..1.05 rows=1 width=42) (never
executed) * Index Cond:
(vessel_visit_c = v.vessel_visit_c)
Filter: (v.vessel_call_nr =
vessel_call_nr)
-> Subquery Scan on "*SELECT* 2" (cost=0.28..19.65
rows=1 width=70) (actual time=0.002..0.002 rows=0 loops=1)
Buffers: shared hit=1
-> Nested Loop (cost=0.28..19.64 rows=1
width=6404) (actual time=0.002..0.002 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using
tms_vessel_visit_bak_test_pkey on tms_vessel_visit_bak_test v_1
(cost=0.14..9.09 rows=10 width=80)
(actual time=0.002..0.002 rows=0 loops=1)
Buffers: shared hit=1
* -> Index Scan using
tms_vessel_visit_aggregate_bak_test_pkey on
tms_vessel_visit_aggregate_bak_test a_1 (cost=0.14..1.05 rows=1 width=42)
(never executed) Index Cond:
(vessel_visit_c = v_1.vessel_visit_c)*
Filter: (v_1.vessel_call_nr = vessel_call_nr)
-> Bitmap Heap Scan on tms_tdr_region_map m (cost=3.92..6.06
rows=2 width=38) (never executed)
Recheck Cond: ((vsl_svc_c)::text = ("*SELECT*
1".svc_c)::text)
-> Bitmap Index Scan on tms_tdr_region_map_pk
(cost=0.00..3.92 rows=2 width=0) (never executed)
Index Cond: ((vsl_svc_c)::text = ("*SELECT*
1".svc_c)::text)
Another one where we have columns as varchar in the database the plan is
with seq join and application being tested is taking around 7-8 seconds.
Aggregate (cost=145.19..145.20 rows=1 width=32) (actual time=3.819..3.823
rows=1 loops=1)
Buffers: shared hit=71
-> Nested Loop (cost=39.54..145.16 rows=11 width=0) (actual
time=3.817..3.821 rows=0 loops=1)
Buffers: shared hit=71
-> Hash Join (cost=39.39..128.04 rows=5 width=5) (actual
time=1.154..3.806 rows=1 loops=1)
Hash Cond: (("*SELECT* 1".vessel_visit_c)::text =
(t.trgt_vvd_n)::text)
Buffers: shared hit=70
-> Append (cost=36.88..122.85 rows=996 width=13) (actual
time=0.806..3.569 rows=1009 loops=1)
Buffers: shared hit=67
-> Subquery Scan on "*SELECT* 1" (cost=36.88..115.01
rows=995 width=13) (actual time=0.806..3.318 rows=995 loops=1)
Buffers: shared hit=65
-> Hash Join (cost=36.88..105.06 rows=995
width=6361) (actual time=0.805..3.124 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.007..0.278 rows=995
loops=1)* Buffers: shared hit=53
-> Hash (cost=21.95..21.95 rows=995
width=13) (actual time=0.774..0.775 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.008..0.275 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.064..0.099 rows=14 loops=1)
Buffers: shared hit=2
-> Hash Join (cost=1.35..2.85 rows=1
width=6422) (actual time=0.064..0.095 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.011..0.015 rows=33loops=1)*
Buffers: shared hit=1
-> Hash (cost=1.14..1.14 rows=14
width=98) (actual time=0.027..0.027 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.006..0.017 rows=14 loops=1)*
Buffers: shared hit=1
-> Hash (cost=2.50..2.50 rows=1 width=7) (actual
time=0.019..0.019 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=3
-> Index Scan using tms_tdr_pk on tms_tdr t
(cost=0.29..2.50 rows=1 width=7) (actual time=0.015..0.016 rows=1 loops=1)
Index Cond: (tdr_id = '13581258'::numeric)
Buffers: shared hit=3
-> Index Only Scan using tms_tdr_region_map_pk on
tms_tdr_region_map m (cost=0.15..3.40 rows=2 width=38) (actual
time=0.004..0.004 rows=0 loops
=1)
Index Cond: (vsl_svc_c = ("*SELECT* 1".svc_c)::text)
Heap Fetches: 0
Buffers: shared hit=1
Can you please help with any method where we can try to cast the views
creation with actual data type casting rather than text for characters.
Or are there any additional index classes which we can create to overcome
this situation.
Our more focus is if we can stop from typecasting to *text and t*est how an
application behaves.
On Fri, Mar 26, 2021 at 10:38 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jagmohan Kaintura <jagmohan@tecorelabs.com> writes:
> > When we are compiling View definition into database , the where clause on
> > the Character Columns is getting internally typecasted to text.
>
> That's normal. In Postgres, varchar has no operators of its own;
> everything you might do with it involves a (no-cost) cast to text.
> You might think of varchar as being a domain over text, though for
> historical reasons it's not implemented exactly that way.
>
> > 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.
>
> This, on the other hand, is just nonsense. You haven't presented
> any examples in which an indexscan looks likely to be a win.
> Postgres generally won't bother with an index when scanning a tiny
> table. Nor is an index necessarily helpful when doing a join,
> unless the join is designed to access just a small part of the table.
>
> > Please help us in identifying the solution.
>
> Please read
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> regards, tom lane
>
--
*Best Regards,*
Jagmohan
[Attachment #3 (text/html)]
<div dir="ltr"><div class="gmail_default" \
style="font-family:verdana,sans-serif;font-size:large">Hi Tom,</div><div \
class="gmail_default" \
style="font-family:verdana,sans-serif;font-size:large"><br></div><div \
class="gmail_default" style="font-family:verdana,sans-serif;font-size:large">Thanks \
for your analysis!!</div><div class="gmail_default" \
style="font-family:verdana,sans-serif;font-size:large">As we were discussing on the \
typecasted column not able to use the indexes as base table columns are with \
datatype varchar and they are type casted to text internally .</div><div \
class="gmail_default" \
style="font-family:verdana,sans-serif;font-size:large"><br></div><div \
class="gmail_default" style="font-family:verdana,sans-serif;font-size:large">We made \
some more analysis and tried to create some test tables with columns datatype as \
<b>text </b>which are being referenced in the <b>where</b> clause.</div><div \
class="gmail_default" \
style="font-family:verdana,sans-serif;font-size:large"><br></div><div \
class="gmail_default" \
style="font-family:verdana,sans-serif">tms_vessel_visit_aggregate_test</div><div \
class="gmail_default" \
style="font-family:verdana,sans-serif">tms_owner_cz1qa@zdcq12c2:5434=> \d \
tms_vessel_visit_aggregate_test<br> Table \
"tms_owner_cz1qa.tms_vessel_visit_aggregate_test"<br> Column \
| Type | Collation | Nullable | \
Default<br>-------------------------+--------------------------------+-----------+----------+---------<br> \
vessel_visit_c | text | \
| not null |<br>....</div><div class="gmail_default" \
style="font-family:verdana,sans-serif"> </div><div class="gmail_default" \
style="font-family:verdana,sans-serif">tms_vessel_visit_aggregate_bak_test<br>tms_owner_cz1qa@zdcq12c2:5434=> \
\d tms_vessel_visit_aggregate_bak_test<br> Table \
"tms_owner_cz1qa.tms_vessel_visit_aggregate_bak_test"<br> \
Column | Type | Collation | \
Nullable | Default<br>-------------------------+--------------------------------+-----------+----------+---------<br> \
vessel_visit_c | text | \
| not null |<br>... <br></div><div class="gmail_default" \
style="font-family:verdana,sans-serif"><br></div><div class="gmail_default" \
style="font-family:verdana,sans-serif">tms_vessel_visit_test</div><div \
class="gmail_default" \
style="font-family:verdana,sans-serif">tms_owner_cz1qa@zdcq12c2:5434=> \d \
tms_vessel_visit_test<br> Table \
"tms_owner_cz1qa.tms_vessel_visit_test"<br> Column \
| Type | Collation | Nullable | \
Defa<br>ult<br>-------------------------------+--------------------------------+-----------+----------+-------<br>-------<br> \
vessel_visit_c | text | \
| not null |<br>....</div><div class="gmail_default" \
style="font-family:verdana,sans-serif"><br>tms_vessel_visit_bak_test<br></div><div \
class="gmail_default" style="font-family:verdana,sans-serif">...</div><div \
class="gmail_default" style="font-family:verdana,sans-serif"> vessel_visit_c \
| text | | not null \
|<br></div><div class="gmail_default" \
style="font-family:verdana,sans-serif">....</div><div class="gmail_default" \
style="font-family:verdana,sans-serif;font-size:large"><br></div><div \
class="gmail_default" style="font-family:verdana,sans-serif;font-size:large">After \
above modification created a new View TMV_VESSEL_VISIT_TEXT_VERIFY. Now as the base \
columns are <b>text and </b>typecast is also<b> text. </b>We are getting a good \
optimum plan as we expected.</div><div class="gmail_default" \
style="font-family:verdana,sans-serif;font-size:large"><br></div><div \
class="gmail_default" style="font-family:verdana,sans-serif;font-size:large">The \
application call which was earlier taking 7-8 seconds for execution is now taking \
900-1100 msec. It got improved by many factors. </div><div class="gmail_default" \
style="font-family:verdana,sans-serif;font-size:large">From the application single \
call view is getting executed 4k times.</div><div class="gmail_default" \
style="font-family:verdana,sans-serif"><br></div><div class="gmail_default" \
style="font-family:verdana,sans-serif"> Aggregate (cost=47.91..47.92 rows=1 \
width=32) (actual time=0.021..0.024 rows=1 loops=1)<br> Buffers: shared hit=5<br> \
-> Nested Loop (cost=4.49..47.91 rows=1 width=0) (actual time=0.019..0.021 \
rows=0 loops=1)<br> Buffers: shared hit=5<br> -> \
Nested Loop (cost=0.57..41.83 rows=1 width=38) (actual time=0.019..0.020 rows=0 \
loops=1)<br> Join Filter: ((t.trgt_vvd_n)::text = \
"*SELECT* 1".vessel_visit_c)<br> Buffers: shared \
hit=5<br><b> -> Index Scan using tms_tdr_pk on tms_tdr t \
(cost=0.29..2.50 rows=1 width=7) (actual time=0.011..0.012 rows=1 loops=1)<br></b> \
Index Cond: (tdr_id = '13581258'::numeric)<br> \
Buffers: shared hit=3<br> -> Append (cost=0.28..39.30 \
rows=2 width=70) (actual time=0.005..0.006 rows=0 loops=1)<br> \
Buffers: shared hit=2<br> -> Subquery Scan on \
"*SELECT* 1" (cost=0.28..19.65 rows=1 width=70) (actual time=0.002..0.003 \
rows=0 loops=1)<br> Buffers: shared hit=1<br> \
-> Nested Loop (cost=0.28..19.64 rows=1 width=6418) (actual time=0.002..0.002 \
rows=0 loops=1)<br> Buffers: shared \
hit=1<br> <b> -> Index Scan \
using tms_vessel_visit_test_vessel_visit_c_idx on tms_vessel_visit_test v \
(cost=0.14..9.09 rows=10 width=80) (actual time=0.002..0.002 rows=0 loops=1)<br> \
</b> Buffers: shared \
hit=1<br> <b> -> Index Scan \
using tms_vessel_visit_aggregate_test_vessel_visit_c_idx on \
tms_vessel_visit_aggregate_test a (cost=0.14.<br>.1.05 rows=1 width=42) (never \
executed)<br> </b> Index \
Cond: (vessel_visit_c = v.vessel_visit_c)<br> \
Filter: (v.vessel_call_nr = vessel_call_nr)<br> -> \
Subquery Scan on "*SELECT* 2" (cost=0.28..19.65 rows=1 width=70) (actual \
time=0.002..0.002 rows=0 loops=1)<br> \
Buffers: shared hit=1<br> -> Nested Loop \
(cost=0.28..19.64 rows=1 width=6404) (actual time=0.002..0.002 rows=0 loops=1)<br> \
Buffers: shared hit=1<br> -> \
Index Scan using tms_vessel_visit_bak_test_pkey on tms_vessel_visit_bak_test v_1 \
(cost=0.14..9.09 rows=10 width=80)<br>(actual time=0.002..0.002 rows=0 loops=1)<br> \
Buffers: shared hit=1<br><b> -> \
Index Scan using tms_vessel_visit_aggregate_bak_test_pkey on \
tms_vessel_visit_aggregate_bak_test a_1 (cost=0.14..1.05 rows=1 width=42) (never \
executed)<br> Index Cond: \
(vessel_visit_c = v_1.vessel_visit_c)<br></b> \
Filter: (v_1.vessel_call_nr = vessel_call_nr)<br> -> Bitmap Heap \
Scan on tms_tdr_region_map m (cost=3.92..6.06 rows=2 width=38) (never executed)<br> \
Recheck Cond: ((vsl_svc_c)::text = ("*SELECT* 1".svc_c)::text)<br> \
-> Bitmap Index Scan on tms_tdr_region_map_pk (cost=0.00..3.92 rows=2 width=0) \
(never executed)<br> Index Cond: ((vsl_svc_c)::text = \
("*SELECT* 1".svc_c)::text)<br></div><div class="gmail_default" \
style="font-family:verdana,sans-serif;font-size:large"><br></div><div \
class="gmail_default" \
style="font-family:verdana,sans-serif;font-size:large"><br></div><div \
class="gmail_default" style="font-family:verdana,sans-serif;font-size:large">Another \
one where we have columns as varchar in the database the plan is with seq join and \
application being tested is taking around 7-8 seconds. </div><div \
class="gmail_default" style="font-family:verdana,sans-serif"><br></div><div \
class="gmail_default" style="font-family:verdana,sans-serif"> Aggregate \
(cost=145.19..145.20 rows=1 width=32) (actual time=3.819..3.823 rows=1 loops=1)<br> \
Buffers: shared hit=71<br> -> Nested Loop (cost=39.54..145.16 rows=11 \
width=0) (actual time=3.817..3.821 rows=0 loops=1)<br> Buffers: shared \
hit=71<br> -> Hash Join (cost=39.39..128.04 rows=5 width=5) \
(actual time=1.154..3.806 rows=1 loops=1)<br> Hash Cond: \
(("*SELECT* 1".vessel_visit_c)::text = (t.trgt_vvd_n)::text)<br> \
Buffers: shared hit=70<br> -> Append (cost=36.88..122.85 \
rows=996 width=13) (actual time=0.806..3.569 rows=1009 loops=1)<br> \
Buffers: shared hit=67<br> -> Subquery Scan on \
"*SELECT* 1" (cost=36.88..115.01 rows=995 width=13) (actual \
time=0.806..3.318 rows=995 loops=1)<br> \
Buffers: shared hit=65<br> -> Hash Join \
(cost=36.88..105.06 rows=995 width=6361) (actual time=0.805..3.124 rows=995 \
loops=1)<br> Hash Cond: \
(((v.vessel_visit_c)::text = (a.vessel_visit_c)::text) AND (v.vessel_call_nr = \
a.vessel_call_nr))<br> Buffers: \
shared hit=65<br><b> -> Seq \
Scan on tms_vessel_visit v (cost=0.00..62.95 rows=995 width=18) (actual \
time=0.007..0.278 rows=995 loops=1)<br></b> \
Buffers: shared hit=53<br> -> \
Hash (cost=21.95..21.95 rows=995 width=13) (actual time=0.774..0.775 rows=995 \
loops=1)<br> Buckets: 1024 \
Batches: 1 Memory Usage: 52kB<br> \
Buffers: shared hit=12<br><b> \
-> Seq Scan on tms_vessel_visit_aggregate a (cost=0.00..21.95 rows=995 \
width=13) (actual time=0.008..0.275 rows=995 loops=1)<br></b> \
Buffers: shared hit=12<br> -> Subquery Scan on \
"*SELECT* 2" (cost=1.35..2.86 rows=1 width=88) (actual time=0.064..0.099 \
rows=14 loops=1)<br> Buffers: shared \
hit=2<br> -> Hash Join \
(cost=1.35..2.85 rows=1 width=6422) (actual time=0.064..0.095 rows=14 loops=1)<br> \
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))<br> \
Buffers: shared hit=2<br><b> -> \
Seq Scan on tms_vessel_visit_aggregate_bak a_1 (cost=0.00..1.33 rows=33 width=60) \
(actual time=0.011..0.015 rows=33loops=1)<br></b> \
Buffers: shared hit=1<br> -> \
Hash (cost=1.14..1.14 rows=14 width=98) (actual time=0.027..0.027 rows=14 \
loops=1)<br> Buckets: 1024 \
Batches: 1 Memory Usage: 9kB<br> \
Buffers: shared hit=1<br><b> \
-> Seq Scan on tms_vessel_visit_bak v_1 (cost=0.00..1.14 rows=14 width=98) \
(actual time=0.006..0.017 rows=14 loops=1)<br></b> \
Buffers: shared hit=1<br> -> Hash (cost=2.50..2.50 \
rows=1 width=7) (actual time=0.019..0.019 rows=1 loops=1)<br> \
Buckets: 1024 Batches: 1 Memory Usage: 9kB<br> \
Buffers: shared hit=3<br> -> Index Scan using \
tms_tdr_pk on tms_tdr t (cost=0.29..2.50 rows=1 width=7) (actual time=0.015..0.016 \
rows=1 loops=1)<br> Index Cond: (tdr_id = \
'13581258'::numeric)<br> Buffers: \
shared hit=3<br> -> Index Only Scan using tms_tdr_region_map_pk on \
tms_tdr_region_map m (cost=0.15..3.40 rows=2 width=38) (actual time=0.004..0.004 \
rows=0 loops<br>=1)<br> Index Cond: (vsl_svc_c = \
("*SELECT* 1".svc_c)::text)<br> Heap Fetches: 0<br> \
Buffers: shared hit=1</div><div class="gmail_default" \
style="font-family:verdana,sans-serif;font-size:large"><br></div><div \
class="gmail_default" style="font-family:verdana,sans-serif;font-size:large">Can you \
please help with any method where we can try to cast the views creation with actual \
data type casting rather than text for characters. </div><div class="gmail_default" \
style="font-family:verdana,sans-serif;font-size:large">Or are there any additional \
index classes which we can create to overcome this situation.</div><div \
class="gmail_default" \
style="font-family:verdana,sans-serif;font-size:large"><br></div><div \
class="gmail_default" style="font-family:verdana,sans-serif;font-size:large">Our more \
focus is if we can stop from typecasting to <b>text and t</b>est how an application \
behaves.<b> </b></div><div class="gmail_default" \
style="font-family:verdana,sans-serif;font-size:large"><b><br></b></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Mar 26, 2021 at 10:38 \
AM Tom Lane <<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Jagmohan Kaintura \
<<a href="mailto:jagmohan@tecorelabs.com" \
target="_blank">jagmohan@tecorelabs.com</a>> writes:<br> > When we are \
compiling View definition into database , the where clause on<br> > the Character \
Columns is getting internally typecasted to text.<br> <br>
That's normal. In Postgres, varchar has no operators of its own;<br>
everything you might do with it involves a (no-cost) cast to text.<br>
You might think of varchar as being a domain over text, though for<br>
historical reasons it's not implemented exactly that way.<br>
<br>
> These Columns are Primary Key columns and because of this Type Casting on<br>
> those columns Index scan is not happening and we are always getting<br>
> Sequential Scan.<br>
<br>
This, on the other hand, is just nonsense. You haven't presented<br>
any examples in which an indexscan looks likely to be a win.<br>
Postgres generally won't bother with an index when scanning a tiny<br>
table. Nor is an index necessarily helpful when doing a join,<br>
unless the join is designed to access just a small part of the table.<br>
<br>
> Please help us in identifying the solution.<br>
<br>
Please read<br>
<a href="https://wiki.postgresql.org/wiki/Slow_Query_Questions" rel="noreferrer" \
target="_blank">https://wiki.postgresql.org/wiki/Slow_Query_Questions</a><br> <br>
regards, tom lane<br>
</blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" \
class="gmail_signature"><div dir="ltr"><font face="verdana, sans-serif" \
size="4"><b>Best Regards,</b></font><div><font face="verdana, sans-serif" \
size="4">Jagmohan</font><span style="font-family:verdana,sans-serif;font-size:large"> \
</span></div></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic