X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 3037ED1CA92 for ; Tue, 18 Nov 2003 13:33:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 33208-01 for ; Tue, 18 Nov 2003 09:32:59 -0400 (AST) Received: from stan.aopsys.com (m173.net81-64-15.noos.fr [81.64.15.173]) by svr1.postgresql.org (Postfix) with ESMTP id CC052D1D880 for ; Tue, 18 Nov 2003 09:32:54 -0400 (AST) Received: from localhost ([127.0.0.1] helo=news.nerim.net) by stan.aopsys.com with esmtp (Exim 3.36 #1 (Debian)) id 1AM5qe-0006H6-00; Tue, 18 Nov 2003 14:24:52 +0100 To: Shridhar Daithankar Cc: pgsql-performance@postgresql.org Subject: Re: Join on incompatible types References: <87k75ym1v7.fsf@news.nerim.net> <3FB9F855.7010905@myrealbox.com> From: Laurent Martelli Date: Tue, 18 Nov 2003 14:24:51 +0100 In-Reply-To: <3FB9F855.7010905@myrealbox.com> (Shridhar Daithankar's message of "Tue, 18 Nov 2003 16:15:41 +0530") Message-ID: <87oev9lsgs.fsf@news.nerim.net> User-Agent: Gnus/5.1002 (Gnus v5.10.2) Emacs/20.7 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/145 X-Sequence-Number: 4705 >>>>> "Shridhar" == Shridhar Daithankar 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