Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 6020B9FB208 for ; Sun, 24 Sep 2006 14:57:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 55542-07 for ; Sun, 24 Sep 2006 14:57:56 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 0333A9FA21B for ; Sun, 24 Sep 2006 14:57:55 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k8OHvtv6008362; Sun, 24 Sep 2006 13:57:55 -0400 (EDT) To: Ben cc: pgsql-performance@postgresql.org Subject: Re: IN not handled very well? In-reply-to: <37536ADC-0D18-485C-9F77-B875D69FBCAA@silentmedia.com> References: <37536ADC-0D18-485C-9F77-B875D69FBCAA@silentmedia.com> Comments: In-reply-to Ben message dated "Sun, 24 Sep 2006 10:12:23 -0700" Date: Sun, 24 Sep 2006 13:57:54 -0400 Message-ID: <8361.1159120674@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.098 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/324 X-Sequence-Number: 21010 Ben writes: > -> Hash IN Join (cost=51.67..31794.72 > rows=218 width=4) > Hash Cond: (("outer".puid)::text = > "inner".name) > -> Seq Scan on puid > (cost=0.00..23495.21 rows=1099421 width=44) > -> Bitmap Heap Scan on puid > (cost=20.03..59.52 rows=10 width=4) > Recheck Cond: ((puid = > 'f68dcf86-992c-2e4a-21fb-2fc8c56edfeb'::bpchar) OR (puid = > '7716dbcf-56ab-623b-ab33-3b2e67a0727c'::bpchar) OR (puid = Apparently you've got a datatype mismatch: name is text while puid is char(N). The comparisons to name can't be converted into indexscans on puid because the semantics aren't the same for text and char comparisons. regards, tom lane