X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3F159D1BAA3 for ; Wed, 20 Aug 2003 10:30:46 -0300 (ADT) 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 50055-10 for ; Wed, 20 Aug 2003 10:30:38 -0300 (ADT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 1B0EBD1B92B for ; Wed, 20 Aug 2003 10:30:34 -0300 (ADT) Received: (qmail 6720 invoked by uid 500); 20 Aug 2003 13:36:24 -0000 Date: Wed, 20 Aug 2003 08:36:24 -0500 From: Bruno Wolff III To: alvis@piladzi-2.biz Cc: pgsql-performance@postgresql.org Subject: Re: When NOT to index small tables? Message-ID: <20030820133624.GB5221@wolff.to> Mail-Followup-To: alvis@piladzi-2.biz, pgsql-performance@postgresql.org References: <004201c36701$fffee930$1401010a@Xeon> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <004201c36701$fffee930$1401010a@Xeon> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/297 X-Sequence-Number: 3113 On Wed, Aug 20, 2003 at 13:01:16 +0300, alvis@piladzi-2.biz wrote: > I have found many reasons not to index small tables (see . > But I still have questions. > > 1. How small is small enough? Unless you think maintaining the indexes is a significant overhead, you shouldn't worry about it as the planner will choose whether or not to use them according to what it thinks is faster. > 2. am I supposed to drop primary key index on so called 'label tables' > knowing I am using this pk in join clause? I don't think you want to drop any indexes that are used to enforce constraints. > 3. Is it good to drop index on small table If I know I have created VIEW and > joined large table and the small one > and I have where condition on that particular colum? If you have some other reason for creating the index, you probably don't want to drop it to try to speed up queries. If there isn't some reason you have for keeping the index, then you might want to drop it if it isn't helping your queries as maintaining the index will slow down queries that modify the table.