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 1D1CED1DA62 for ; Wed, 3 Dec 2003 01:33:44 +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 34464-04 for ; Tue, 2 Dec 2003 21:33:19 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 098B2D1D400 for ; Tue, 2 Dec 2003 21:33:12 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 44A4F36D1B; Tue, 2 Dec 2003 20:33:16 -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 1ARLtE-00072x-00; Tue, 02 Dec 2003 20:33:16 -0500 To: Tom Lane Cc: Greg Stark , Jared Carr , 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> <87ptf697h0.fsf@stark.dyndns.tv> <25476.1070409047@sss.pgh.pa.us> In-Reply-To: <25476.1070409047@sss.pgh.pa.us> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 02 Dec 2003 20:33:15 -0500 Message-ID: <87znea7kis.fsf@stark.dyndns.tv> Lines: 34 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/41 X-Sequence-Number: 4901 Tom Lane writes: > Define "no longer works well". varchar doesn't have its own comparison > operators anymore, but AFAIK that makes no difference. Well it seems to completely bar the use of a straight merge join between two index scans: test=# set enable_seqscan = off; SET test=# explain select * from a,b where a.x=b.x; QUERY PLAN --------------------------------------------------------------------------- Nested Loop (cost=100000000.00..100002188.86 rows=1001 width=64) -> Seq Scan on a (cost=100000000.00..100000020.00 rows=1000 width=32) -> Index Scan using b_pkey on b (cost=0.00..2.16 rows=1 width=32) Index Cond: (("outer".x)::text = (b.x)::text) (4 rows) test=# explain select * from a2,b2 where a2.x=b2.x; QUERY PLAN ----------------------------------------------------------------------------- Merge Join (cost=0.00..63.04 rows=1001 width=64) Merge Cond: ("outer".x = "inner".x) -> Index Scan using a2_pkey on a2 (cost=0.00..24.00 rows=1000 width=32) -> Index Scan using b2_pkey on b2 (cost=0.00..24.00 rows=1000 width=32) (4 rows) -- greg