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 9670BD1B432 for ; Wed, 28 Apr 2004 05:12:37 -0300 (ADT) 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 37012-07 for ; Wed, 28 Apr 2004 05:12:36 -0300 (ADT) Received: from 192.168.0.11 (fw.i-trade.hu [212.92.1.62]) by svr1.postgresql.org (Postfix) with SMTP id 96F6DD1B8E4 for ; Wed, 28 Apr 2004 05:12:33 -0300 (ADT) Received: (qmail 27852 invoked from network); 28 Apr 2004 07:52:31 -0000 Received: from unknown (HELO freemail.hu) (212.92.7.130) by 0 with SMTP; 28 Apr 2004 07:52:31 -0000 Message-ID: <408F6817.1060708@freemail.hu> Date: Wed, 28 Apr 2004 10:15:19 +0200 From: =?ISO-8859-2?Q?Suller_Andr=E1s?= User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: hu, en-us MIME-Version: 1.0 To: Silke Trissl Cc: pgsql-performance@postgresql.org Subject: Re: Join problem References: <408E9934.8060509@informatik.hu-berlin.de> In-Reply-To: <408E9934.8060509@informatik.hu-berlin.de> Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/478 X-Sequence-Number: 6778 these two queries are not equal. Query1 returns 6 rows, query2 returns 0 rows, because '~*' and '=' operators are not same. BTW when you use '=', it could use index on "item.description". On query1, "Seq Scan on item" estimates 1 row, on query2 it estimates 733 rows. IMHO that's why query1 uses nested loop, query2 uses hash join. bye, Suller Andras Silke Trissl írta: > Hi, > > Query 1: > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------ > > Nested Loop (cost=0.00..28836.75 rows=1 width=4) (actual > time=65350.780..452130.702 rows=6 loops=1) > Join Filter: ("inner".order_id = "outer".order_id) > -> Seq Scan on item (cost=0.00..28814.24 rows=1 width=8) (actual > time=33.180..1365.190 rows=716 loops=1) > Filter: (description ~* 'CD'::text) > -> Seq Scan on orders (cost=0.00..22.50 rows=1 width=4) (actual > time=21.644..629.500 rows=18 loops=716) > Filter: ((order_name)::text ~* 'Smith'::text) > Total runtime: 452130.782 ms > ########################################################################### > > > Query 2: > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------ > > Hash Join (cost=22.50..28840.44 rows=4 width=4) (actual > time=1187.798..1187.798 rows=0 loops=1) > Hash Cond: ("outer".order_id = "inner".order_id) > -> Seq Scan on item (cost=0.00..28814.24 rows=733 width=8) > (actual time=542.737..542.737 rows=0 loops=1) > Filter: (description = 'CD'::text) > -> Hash (cost=22.50..22.50 rows=1 width=4) (actual > time=645.042..645.042 rows=0 loops=1) > -> Seq Scan on orders (cost=0.00..22.50 rows=1 width=4) > (actual time=22.373..644.996 rows=18 loops=1) > Filter: ((order_name)::text ~* 'Smith'::text) > Total runtime: 1187.865 ms > ############################################################################ > > > > Query 1 with 'set enable_nestloop to false' > > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------- > > Hash Join (cost=22.50..28836.75 rows=1 width=4) (actual > time=1068.593..2003.330 rows=6 loops=1) > Hash Cond: ("outer".item_id = "inner".item_id) > -> Seq Scan on item (cost=0.00..28814.24 rows=1 width=8) (actual > time=33.347..1357.073 rows=716 loops=1) > Filter: (description ~* 'CD'::text) > -> Hash (cost=22.50..22.50 rows=1 width=4) (actual > time=645.287..645.287 rows=0 loops=1) > -> Seq Scan on orders (cost=0.00..22.50 rows=1 width=4) > (actual time=22.212..645.239 rows=18 loops=1) > Filter: ((order_name)::text ~* 'CD'::text) > Total runtime: 2003.409 ms