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 6FF4A8B9E61 for ; Wed, 23 Feb 2005 13:42:28 +0000 (GMT) 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 99432-08 for ; Wed, 23 Feb 2005 13:42:19 +0000 (GMT) Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by svr1.postgresql.org (Postfix) with ESMTP id E983B8B9E2F for ; Wed, 23 Feb 2005 13:42:20 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-31.mail.demon.net with esmtp (Exim 4.42) id 1D3wkn-000IN5-4T; Wed, 23 Feb 2005 13:40:37 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 7F3BE17B88; Wed, 23 Feb 2005 13:42:18 +0000 (GMT) Message-ID: <421C883A.20708@archonet.com> Date: Wed, 23 Feb 2005 13:42:18 +0000 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Butkus_Charles@emc.com Cc: pgsql-performance@postgresql.org Subject: Re: Joins, Deletes and Indexes References: <18C6A5982F816D47BFEF7DF47F7B03232E8BD4@corpmx2.corp.emc.com> In-Reply-To: <18C6A5982F816D47BFEF7DF47F7B03232E8BD4@corpmx2.corp.emc.com> Content-Type: text/plain; charset=ISO-8859-15; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.062 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200502/318 X-Sequence-Number: 10647 Butkus_Charles@emc.com wrote: > > The cluster table only has 11 rows, so I'm not sure an index would > help. The sensorreport table has 15,000,000 rows so that's why I've > got the index there. Ah - only 11? >>>on the foreign key from sensortable. >>>Again, is there any way to get the delete to use the >>>idx_sensorreport_clusterid index? >> >>No, because this is the cluster table, not sensorreport :-) > > True, but the foreign key constraint on the sensorreport table forces > Postgres to check if there are any sensorreport's that are currently > using this cluster before allowing the cluster to be deleted. If you only have 11 distinct values in the large table then it's debatable whether it's always quicker to use the index. Since your first example (clusterid = 25000114) returned so few rows, I'm guessing that some other values represent a sizeable percentage of the table. That'd explain the difference between PG's estimates and the actual number of matching rows. You can try "SET enable_seqscan =false;" before running the query and see whether using the index helps things. -- Richard Huxton Archonet Ltd