X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5A1CCD1CACB for ; Fri, 13 Feb 2004 00:00:37 +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 66071-01 for ; Thu, 12 Feb 2004 20:00:30 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id C121AD1B4DE for ; Thu, 12 Feb 2004 20:00:22 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i1D00PcV024948; Thu, 12 Feb 2004 19:00:25 -0500 (EST) To: ow Cc: pgsql-sql@postgresql.org Subject: Re: 7.4 - FK constraint performance In-reply-to: <20040212214923.98051.qmail@web60803.mail.yahoo.com> References: <20040212214923.98051.qmail@web60803.mail.yahoo.com> Comments: In-reply-to ow message dated "Thu, 12 Feb 2004 13:49:23 -0800" Date: Thu, 12 Feb 2004 19:00:25 -0500 Message-ID: <24947.1076630425@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/123 X-Sequence-Number: 16814 ow writes: > --- Tom Lane wrote: >> I think it must be using a seqscan for the foreign key check query. > 2) prepare foo(my.dint) as > SELECT 1 FROM ONLY "my"."large" x WHERE "small_id" = $1 FOR UPDATE OF x; > explain analyze execute foo(201); > QUERY PLAN > Seq Scan on large x (cost=0.00..1787052.30 rows=7893843 width=6) (actual > time=210566.301..210566.301 rows=0 loops=1) > Filter: ((small_id)::integer = ($1)::integer) > Total runtime: 210566.411 ms Well, there's the smoking gun all right. Why does it think there are going to be 7893843 matching rows!? Could we see the pg_stats row for the large.small_id column? regards, tom lane