Re: [PERFORM] Join on incompatible types
Laurent Martelli wrote: "Shridhar" == Shridhar Daithankar <[EMAIL PROTECTED]> writes: [...] Shridhar> 2. Try following query EXPLAIN ANALYZE SELECT * from lists Shridhar> join classes on classes.id=lists.value where Shridhar> lists.id='16'::integer; Shridhar> classes.id=lists.value::integer. With classes.id of type integer and lists.value of type varchar, I get "ERROR: Cannot cast type character varying to integer", which is not such a surprise. Try to_numbr function to get a number out of string. Then cast it to integer. http://developer.postgresql.org/docs/postgres/functions-formatting.html I hope that works. Don't have postgresql installation handy here.. Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Join on incompatible types
> "Shridhar" == Shridhar Daithankar <[EMAIL PROTECTED]> writes: [...] Shridhar> 2. Try following query EXPLAIN ANALYZE SELECT * from lists Shridhar> join classes on classes.id=lists.value where Shridhar> lists.id='16'::integer; Shridhar> classes.id=lists.value::integer. With classes.id of type integer and lists.value of type varchar, I get "ERROR: Cannot cast type character varying to integer", which is not such a surprise. Thanks for your help anyway. -- Laurent Martelli [EMAIL PROTECTED]Java Aspect Components http://www.aopsys.com/ http://jac.aopsys.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Join on incompatible types
Laurent Martelli wrote: "Shridhar" == Shridhar Daithankar <[EMAIL PROTECTED]> writes: Shridhar> I am stripping the analyze outputs and directly jumping to Shridhar> the end. Shridhar> Can you try following? Shridhar> 1. Make all fields integer in all the table. I can't do this because lists.values contains non integer data which do not refer to a classes.id value. It may sound weird. This is because it's a generic schema for a transparent persistence framework. Fine .I understand. So instead of using a field value, can you use integer version of that field? (Was that one of your queries used that? I deleted the OP) The solution for me would rather be to have varchar everywhere. You need to cast every occurance of that varchar field appropriately, to start with. The performance might suffer as well for numbers. Shridhar> 2. Try following query EXPLAIN ANALYZE SELECT * from lists Shridhar> join classes on classes.id=lists.value where Shridhar> lists.id='16'::integer; classes.id=lists.value::integer. Try that. The aim is absolute type compatibility. If types aren't exactly same, the plan is effectively dead. I would say postgresql enforces good habits in it's application developers, from a cultural POV. Had C refused to compile without such strict type compatibility, we wouldn't have to worry about 16bit/32bit and 64 bit software. Just upgrade the compiler and everything is damn good..:-) I doubt if C would have so popular with such strict type checking but that is another issue. I think pascal enforces such strict syntax.. Not sure though.. Shridhar ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Join on incompatible types
> "Shridhar" == Shridhar Daithankar <[EMAIL PROTECTED]> writes: Shridhar> Laurent Martelli wrote: >>> "Shridhar" == Shridhar Daithankar >>> <[EMAIL PROTECTED]> writes: Shridhar> Laurent Martelli wrote: >> [...] >> Should I understand that a join on incompatible types >> (such as >> integer and varchar) may lead to bad performances ? Shridhar> Conversely, you should enforce strict type compatibility Shridhar> in comparisons for getting any good plans..:-) >> Ha ha, now I understand why a query of mine was so sluggish. Is >> there a chance I could achieve the good perfs without having he >> same types ? I've tried a CAST in the query, but it's even a >> little worse than without it. However, using a view to cast >> integers into varchar gives acceptable results (see at the end). >> I'm using Postgresql 7.3.4. Shridhar> I am stripping the analyze outputs and directly jumping to Shridhar> the end. Shridhar> Can you try following? Shridhar> 1. Make all fields integer in all the table. I can't do this because lists.values contains non integer data which do not refer to a classes.id value. It may sound weird. This is because it's a generic schema for a transparent persistence framework. The solution for me would rather be to have varchar everywhere. Shridhar> 2. Try following query EXPLAIN ANALYZE SELECT * from lists Shridhar> join classes on classes.id=lists.value where Shridhar> lists.id='16'::integer; Shridhar> How does it affect the runtime? Shridhar> Shridhar -- Laurent Martelli [EMAIL PROTECTED]Java Aspect Components http://www.aopsys.com/ http://jac.aopsys.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Join on incompatible types
Laurent Martelli wrote: "Shridhar" == Shridhar Daithankar <[EMAIL PROTECTED]> writes: Shridhar> Laurent Martelli wrote: [...] >> Should I understand that a join on incompatible types (such as >> integer and varchar) may lead to bad performances ? Shridhar> Conversely, you should enforce strict type compatibility Shridhar> in comparisons for getting any good plans..:-) Ha ha, now I understand why a query of mine was so sluggish. Is there a chance I could achieve the good perfs without having he same types ? I've tried a CAST in the query, but it's even a little worse than without it. However, using a view to cast integers into varchar gives acceptable results (see at the end). I'm using Postgresql 7.3.4. I am stripping the analyze outputs and directly jumping to the end. Can you try following? 1. Make all fields integer in all the table. 2. Try following query EXPLAIN ANALYZE SELECT * from lists join classes on classes.id=lists.value where lists.id='16'::integer; How does it affect the runtime? Shridhar ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Join on incompatible types
> "Shridhar" == Shridhar Daithankar <[EMAIL PROTECTED]> writes:
Shridhar> Laurent Martelli wrote:
[...]
>> Should I understand that a join on incompatible types (such as
>> integer and varchar) may lead to bad performances ?
Shridhar> Conversely, you should enforce strict type compatibility
Shridhar> in comparisons for getting any good plans..:-)
Ha ha, now I understand why a query of mine was so sluggish.
Is there a chance I could achieve the good perfs without having he
same types ? I've tried a CAST in the query, but it's even a little
worse than without it. However, using a view to cast integers into
varchar gives acceptable results (see at the end).
I'm using Postgresql 7.3.4.
iprofil-jac=# EXPLAIN ANALYZE SELECT * from classes where exists (select value from
lists where lists.id='16' and lists.value=classes.id);
QUERY PLAN
Seq Scan on classes (cost=0.00..5480289.75 rows=9610 width=25) (actual
time=31.68..7321.56 rows=146 loops=1)
Filter: (subplan)
SubPlan
-> Index Scan using lists_id on lists (cost=0.00..285.12 rows=1 width=8)
(actual time=0.38..0.38 rows=0 loops=19220)
Index Cond: (id = 16)
Filter: ((value)::text = ($0)::text)
Total runtime: 7321.72 msec
iprofil-jac=# EXPLAIN ANALYZE SELECT * from classes2 where exists (select value from
lists where lists.id='16' and lists.value=classes2.id);
QUERY PLAN
-
Seq Scan on classes2 (cost=0.00..5923.87 rows=500 width=64) (actual
time=0.76..148.20 rows=146 loops=1)
Filter: (subplan)
SubPlan
-> Index Scan using lists_value on lists (cost=0.00..5.90 rows=1 width=8)
(actual time=0.01..0.01 rows=0 loops=19220)
Index Cond: ((id = 16) AND (value = $0))
Total runtime: 148.34 msec
--
-- Tables classes and classes2 are populated with the same data, they
-- only differ on the type of the "id" column.
--
iprofil-jac=# \d classes
Table "public.classes"
Colonne | Type| Modifications
-+---+---
id | integer | not null
classid | character varying |
Index: classes_pkey primary key btree (id)
iprofil-jac=# \d classes2
Table "public.classes2"
Colonne | Type| Modifications
-+---+---
id | character varying | not null
classid | character varying |
Index: classes2_pkey primary key btree (id)
iprofil-jac=# \d lists
Table "public.lists"
Colonne | Type| Modifications
-+---+---
id | integer | not null
index | integer | not null
value | character varying |
Index: lists_index unique btree (id, "index"),
lists_id btree (id),
lists_value btree (id, value)
--
-- IT'S EVEN BETTER WITH A JOIN
--
iprofil-jac=# EXPLAIN ANALYZE SELECT * from lists join classes on
classes.id=lists.value where lists.id='16';
QUERY PLAN
---
Nested Loop (cost=0.00..90905.88 rows=298 width=41) (actual time=53.93..9327.87
rows=146 loops=1)
Join Filter: (("inner".id)::text = ("outer".value)::text)
-> Seq Scan on lists (cost=0.00..263.43 rows=146 width=16) (actual
time=8.38..9.70 rows=146 loops=1)
Filter: (id = 16)
-> Seq Scan on classes (cost=0.00..333.20 rows=19220 width=25) (actual
time=0.00..28.45 rows=19220 loops=146)
Total runtime: 9328.35 msec
iprofil-jac=# EXPLAIN ANALYZE SELECT * from lists join classes2 on
classes2.id=lists.value where lists.id='16';
QUERY PLAN
--
Merge Join (cost=268.67..324.09 rows=16 width=80) (actual time=9.59..65.55 rows=146
loops=1)
Merge Cond: ("outer".id = "inner".value)
-> Index Scan using classes2_pkey on classes2 (cost=0.00..52.00 rows=1000
width=64) (actual time=0.03..40.83 rows=18778 loops=1)
-> Sort (cost=268.67..269.03 rows=146 width=16) (actual time=9.50..9.56 rows=146
loops=1)
Sort Key: lists.value
-> Seq Scan on lists (cost=0.00..263.43 rows=146 width=16) (actual
time=8.83..9.17 rows=146 loops=1)
Re: [PERFORM] Join on incompatible types
Laurent Martelli wrote:
"scott" == scott marlowe <[EMAIL PROTECTED]> writes:
[...]
scott> Note here:
scott> Merge Join (cost=1788.68..4735.71 rows=1 width=85) (actual
scott> time=597.540..1340.526 rows=20153 loops=1) Merge Cond:
scott> ("outer".id = "inner".id)
scott> This estimate is WAY off. Are both of those fields indexed
scott> and analyzed? Have you tried upping the statistics target on
scott> those two fields? I assume they are compatible types.
Should I understand that a join on incompatible types (such as integer
and varchar) may lead to bad performances ?
Conversely, you should enforce strict type compatibility in comparisons for
getting any good plans..:-)
Shridhar
---(end of broadcast)---
TIP 8: explain analyze is your friend
