pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedJoin on incompatible types
8+ messages / 3 participants
[nested] [flat]
* Join on incompatible types
@ 2003-11-18 10:01 Laurent Martelli <laurent@bearteam.org>
2003-11-18 10:45 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
0 siblings, 1 reply; 8+ messages in thread
From: Laurent Martelli @ 2003-11-18 10:01 UTC (permalink / raw)
To: pgsql-performance
>>>>> "scott" == scott marlowe <scott.marlowe@ihs.com> 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 ?
--
Laurent Martelli
laurent@aopsys.com Java Aspect Components
http://www.aopsys.com/ http://jac.aopsys.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Join on incompatible types
2003-11-18 10:01 Join on incompatible types Laurent Martelli <laurent@bearteam.org>
@ 2003-11-18 10:45 ` Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-18 13:24 ` Re: Join on incompatible types Laurent Martelli <laurent@aopsys.com>
0 siblings, 1 reply; 8+ messages in thread
From: Shridhar Daithankar @ 2003-11-18 10:45 UTC (permalink / raw)
To: Laurent Martelli <laurent@bearteam.org>; +Cc: pgsql-performance
Laurent Martelli wrote:
>>>>>>"scott" == scott marlowe <scott.marlowe@ihs.com> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Join on incompatible types
2003-11-18 10:01 Join on incompatible types Laurent Martelli <laurent@bearteam.org>
2003-11-18 10:45 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
@ 2003-11-18 13:24 ` Laurent Martelli <laurent@aopsys.com>
2003-11-19 06:54 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
0 siblings, 1 reply; 8+ messages in thread
From: Laurent Martelli @ 2003-11-18 13:24 UTC (permalink / raw)
To: Shridhar Daithankar <shridhar_daithankar@myrealbox.com>; +Cc: pgsql-performance
>>>>> "Shridhar" == Shridhar Daithankar <shridhar_daithankar@myrealbox.com> 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)
Filter: (id = 16)
Total runtime: 65.73 msec
--
-- CASTING IN THE QUERY IS NO GOOD
--
iprofil-jac=# EXPLAIN ANALYZE SELECT * from lists join classes on CAST(classes.id AS character varying)=lists.value where lists.id='16';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..90905.88 rows=298 width=41) (actual time=69.03..10017.26 rows=146 loops=1)
Join Filter: ((("inner".id)::text)::character varying = "outer".value)
-> Seq Scan on lists (cost=0.00..263.43 rows=146 width=16) (actual time=20.64..22.03 rows=146 loops=1)
Filter: (id = 16)
-> Seq Scan on classes (cost=0.00..333.20 rows=19220 width=25) (actual time=0.00..30.45 rows=19220 loops=146)
Total runtime: 10017.72 msec
--
-- CREATING A VIEW IS BETTER
--
iprofil-jac=# CREATE VIEW classes3 as SELECT CAST(id AS varchar), classid from classes;
iprofil-jac=# EXPLAIN ANALYZE SELECT * from classes3 where exists (select value from lists where lists.id='16' and lists.value=classes3.id);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Seq Scan on classes (cost=0.00..113853.60 rows=9610 width=25) (actual time=0.91..192.31 rows=146 loops=1)
Filter: (subplan)
SubPlan
-> Index Scan using lists_value on lists (cost=0.00..5.91 rows=1 width=8) (actual time=0.01..0.01 rows=0 loops=19220)
Index Cond: ((id = 16) AND (value = (($0)::text)::character varying))
Total runtime: 192.47 msec
--
Laurent Martelli
laurent@aopsys.com Java Aspect Components
http://www.aopsys.com/ http://jac.aopsys.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Join on incompatible types
2003-11-18 10:01 Join on incompatible types Laurent Martelli <laurent@bearteam.org>
2003-11-18 10:45 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-18 13:24 ` Re: Join on incompatible types Laurent Martelli <laurent@aopsys.com>
@ 2003-11-19 06:54 ` Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-19 09:52 ` Re: Join on incompatible types Laurent Martelli <laurent@aopsys.com>
0 siblings, 1 reply; 8+ messages in thread
From: Shridhar Daithankar @ 2003-11-19 06:54 UTC (permalink / raw)
To: Laurent Martelli <laurent@aopsys.com>; +Cc: pgsql-performance
Laurent Martelli wrote:
>>>>>>"Shridhar" == Shridhar Daithankar <shridhar_daithankar@myrealbox.com> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Join on incompatible types
2003-11-18 10:01 Join on incompatible types Laurent Martelli <laurent@bearteam.org>
2003-11-18 10:45 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-18 13:24 ` Re: Join on incompatible types Laurent Martelli <laurent@aopsys.com>
2003-11-19 06:54 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
@ 2003-11-19 09:52 ` Laurent Martelli <laurent@aopsys.com>
2003-11-19 10:19 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
0 siblings, 1 reply; 8+ messages in thread
From: Laurent Martelli @ 2003-11-19 09:52 UTC (permalink / raw)
To: Shridhar Daithankar <shridhar_daithankar@myrealbox.com>; +Cc: pgsql-performance
>>>>> "Shridhar" == Shridhar Daithankar <shridhar_daithankar@myrealbox.com> writes:
Shridhar> Laurent Martelli wrote:
>>>>>>> "Shridhar" == Shridhar Daithankar
>>>>>>> <shridhar_daithankar@myrealbox.com> 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
laurent@aopsys.com Java Aspect Components
http://www.aopsys.com/ http://jac.aopsys.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Join on incompatible types
2003-11-18 10:01 Join on incompatible types Laurent Martelli <laurent@bearteam.org>
2003-11-18 10:45 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-18 13:24 ` Re: Join on incompatible types Laurent Martelli <laurent@aopsys.com>
2003-11-19 06:54 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-19 09:52 ` Re: Join on incompatible types Laurent Martelli <laurent@aopsys.com>
@ 2003-11-19 10:19 ` Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-19 12:16 ` Re: Join on incompatible types Laurent Martelli <laurent@aopsys.com>
0 siblings, 1 reply; 8+ messages in thread
From: Shridhar Daithankar @ 2003-11-19 10:19 UTC (permalink / raw)
To: Laurent Martelli <laurent@aopsys.com>; +Cc: pgsql-performance
Laurent Martelli wrote:
>>>>>>"Shridhar" == Shridhar Daithankar <shridhar_daithankar@myrealbox.com> 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.
<OT>
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..
</OT>
Shridhar
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Join on incompatible types
2003-11-18 10:01 Join on incompatible types Laurent Martelli <laurent@bearteam.org>
2003-11-18 10:45 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-18 13:24 ` Re: Join on incompatible types Laurent Martelli <laurent@aopsys.com>
2003-11-19 06:54 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-19 09:52 ` Re: Join on incompatible types Laurent Martelli <laurent@aopsys.com>
2003-11-19 10:19 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
@ 2003-11-19 12:16 ` Laurent Martelli <laurent@aopsys.com>
2003-11-19 12:31 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
0 siblings, 1 reply; 8+ messages in thread
From: Laurent Martelli @ 2003-11-19 12:16 UTC (permalink / raw)
To: Shridhar Daithankar <shridhar_daithankar@myrealbox.com>; +Cc: pgsql-performance
>>>>> "Shridhar" == Shridhar Daithankar <shridhar_daithankar@myrealbox.com> 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
laurent@aopsys.com Java Aspect Components
http://www.aopsys.com/ http://jac.aopsys.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Join on incompatible types
2003-11-18 10:01 Join on incompatible types Laurent Martelli <laurent@bearteam.org>
2003-11-18 10:45 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-18 13:24 ` Re: Join on incompatible types Laurent Martelli <laurent@aopsys.com>
2003-11-19 06:54 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-19 09:52 ` Re: Join on incompatible types Laurent Martelli <laurent@aopsys.com>
2003-11-19 10:19 ` Re: Join on incompatible types Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-19 12:16 ` Re: Join on incompatible types Laurent Martelli <laurent@aopsys.com>
@ 2003-11-19 12:31 ` Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
0 siblings, 0 replies; 8+ messages in thread
From: Shridhar Daithankar @ 2003-11-19 12:31 UTC (permalink / raw)
To: Laurent Martelli <laurent@aopsys.com>; +Cc: Shridhar Daithankar <shridhar_daithankar@myrealbox.com>; pgsql-performance
Laurent Martelli wrote:
>>>>>>"Shridhar" == Shridhar Daithankar <shridhar_daithankar@myrealbox.com> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-11-19 12:31 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-18 10:01 Join on incompatible types Laurent Martelli <laurent@bearteam.org>
2003-11-18 10:45 ` Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-18 13:24 ` Laurent Martelli <laurent@aopsys.com>
2003-11-19 06:54 ` Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-19 09:52 ` Laurent Martelli <laurent@aopsys.com>
2003-11-19 10:19 ` Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-19 12:16 ` Laurent Martelli <laurent@aopsys.com>
2003-11-19 12:31 ` Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox