X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DA75FD1B590 for ; Tue, 2 Dec 2003 22:32:36 +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 04544-08 for ; Tue, 2 Dec 2003 18:32:12 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 61304D1B4BD for ; Tue, 2 Dec 2003 18:32:05 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 15416372CB; Tue, 2 Dec 2003 17:32:12 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1ARJ3z-0006Vz-00; Tue, 02 Dec 2003 17:32:11 -0500 To: Jared Carr Cc: Greg Stark , pgsql-performance@postgresql.org Subject: Re: A question on the query planner References: <3FCBB639.10409@89glass.com> <1070385381.24915.8650.camel@camel> <3FCCD225.8030609@89glass.com> <87d6b79har.fsf@stark.dyndns.tv> <3FCCE48B.10406@89glass.com> <871xrn9e09.fsf@stark.dyndns.tv> <3FCCF61E.90307@89glass.com> In-Reply-To: <3FCCF61E.90307@89glass.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 02 Dec 2003 17:32:11 -0500 Message-ID: <87ptf697h0.fsf@stark.dyndns.tv> Lines: 62 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/36 X-Sequence-Number: 4896 Jared Carr writes: > Greg Stark wrote: > > > Well it looks like you have something strange going on. What data type is > > car_id in each table? > > > car_id is a varchar(10) in both tables. Huh. The following shows something strange. It seems joining on two varchars no longer works well. Instead the optimizer has to convert both columns to text. I know some inter-type comparisons were removed a while ago, but I would not have thought that would effect varchar-varchar comparisons. I think this is pretty bad. test=# create table a (x varchar primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a" CREATE TABLE test=# create table b (x varchar primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey" for table "b" CREATE TABLE test=# select * from a,b where a.x=b.x; x | x ---+--- (0 rows) test=# explain select * from a,b where a.x=b.x; QUERY PLAN ------------------------------------------------------------------ Merge Join (cost=139.66..159.67 rows=1001 width=64) Merge Cond: ("outer"."?column2?" = "inner"."?column2?") -> Sort (cost=69.83..72.33 rows=1000 width=32) Sort Key: (a.x)::text -> Seq Scan on a (cost=0.00..20.00 rows=1000 width=32) -> Sort (cost=69.83..72.33 rows=1000 width=32) Sort Key: (b.x)::text -> Seq Scan on b (cost=0.00..20.00 rows=1000 width=32) (8 rows) test=# create table a2 (x text primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a2_pkey" for table "a2" CREATE TABLE test=# create table b2 (x text primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b2_pkey" for table "b2" CREATE TABLE test=# explain select * from a2,b2 where a2.x=b2.x; QUERY PLAN ------------------------------------------------------------------- Hash Join (cost=22.50..57.51 rows=1001 width=64) Hash Cond: ("outer".x = "inner".x) -> Seq Scan on a2 (cost=0.00..20.00 rows=1000 width=32) -> Hash (cost=20.00..20.00 rows=1000 width=32) -> Seq Scan on b2 (cost=0.00..20.00 rows=1000 width=32) (5 rows) -- greg