X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E87F653607 for ; Fri, 29 Apr 2005 00:28:50 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08703-10 for ; Fri, 29 Apr 2005 03:28:42 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 118B953381 for ; Fri, 29 Apr 2005 00:28:37 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j3T3SYlI001532 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK) for ; Thu, 28 Apr 2005 21:28:36 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j3T3SYvK068579 for ; Thu, 28 Apr 2005 21:28:34 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j3T3SYbS068578 for pgsql-performance@postgresql.org; Thu, 28 Apr 2005 21:28:34 -0600 (MDT) (envelope-from mfuhr) Date: Thu, 28 Apr 2005 21:28:34 -0600 From: Michael Fuhr To: postgresql performance list Subject: Re: index on different types Message-ID: <20050429032834.GA68494@winnie.fuhr.org> References: <20050429023512.GB26200@nibiru.borg.metux.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050429023512.GB26200@nibiru.borg.metux.de> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/765 X-Sequence-Number: 12162 On Fri, Apr 29, 2005 at 04:35:13AM +0200, Enrico Weigelt wrote: > > there's often some talk about indices cannot be used if datatypes > dont match. PostgreSQL 8.0 is smarter than previous versions in this respect. It'll use an index if possible even when the types don't match. > On a larger (and long time growed) application I tend to use OID > for references on new tables while old stuff is using integer. If you're using OIDs as primary keys then you might wish to reconsider. See the caveats in the documentation and in the FAQ: http://www.postgresql.org/docs/8.0/interactive/datatype-oid.html http://www.postgresql.org/docs/faqs.FAQ.html#4.12 > Is the planner smart enough to see both as compatible datatype > or is manual casting required ? You can use EXPLAIN to see what the planner will do, but be aware that the planner won't always use an index even if it could: if it thinks a sequential scan would be faster then it won't use an index. To see if using an index is possible, you could set enable_seqscan to off before executing EXPLAIN. In any case, a foreign key column probably ought to have the same type as the column it references -- is there a reason for making them different? -- Michael Fuhr http://www.fuhr.org/~mfuhr/