X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id ABAD9D1B523 for ; Mon, 6 Oct 2003 04:40:49 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 82287-04 for ; Mon, 6 Oct 2003 01:40:04 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 9E3E6D1B52E for ; Mon, 6 Oct 2003 01:40:03 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h964e3hn019627; Mon, 6 Oct 2003 00:40:03 -0400 (EDT) To: ow Cc: pgsql-sql@postgresql.org Subject: Re: FK Constraints, indexes and performance In-reply-to: <20031006002959.13352.qmail@web21401.mail.yahoo.com> References: <20031006002959.13352.qmail@web21401.mail.yahoo.com> Comments: In-reply-to ow message dated "Sun, 05 Oct 2003 17:29:59 -0700" Date: Mon, 06 Oct 2003 00:40:03 -0400 Message-ID: <19626.1065415203@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/63 X-Sequence-Number: 15463 ow writes: > It's understood that FK constraints carry some performance hit. However, the > performance hit I observe is huge. It looks to me like the 7.3 planner will not choose indexscans for the FK check queries in this example, because the comparison operators are misconstrued as shown in this thread: http://archives.postgresql.org/pgsql-hackers/2003-03/msg00997.php The equality operator on your domain is taken to be "oideq" which won't be the same operator associated with the index on the column. This seems to be fixed in 7.4. In 7.3 I'd counsel not introducing domains unnecessarily. regards, tom lane