pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Join on incompatible types
8+ messages / 3 participants
[nested] [flat]

* Join on incompatible types
@ 2003-11-18 10:01  Laurent Martelli <laurent@bearteam.org>
  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:45  Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
  parent: Laurent Martelli <laurent@bearteam.org>
  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 13:24  Laurent Martelli <laurent@aopsys.com>
  parent: 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-19 06:54  Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
  parent: 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-19 09:52  Laurent Martelli <laurent@aopsys.com>
  parent: 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-19 10:19  Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
  parent: 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-19 12:16  Laurent Martelli <laurent@aopsys.com>
  parent: 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-19 12:31  Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
  parent: Laurent Martelli <laurent@aopsys.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