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 3BCEED1BB80 for ; Wed, 20 Aug 2003 07:04:08 -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 92104-08 for ; Wed, 20 Aug 2003 07:03:59 -0300 (ADT) Received: from Tiger.server100.com (unknown [207.44.158.4]) by svr1.postgresql.org (Postfix) with ESMTP id E4695D1B8BB for ; Wed, 20 Aug 2003 07:03:56 -0300 (ADT) Received: from [195.13.175.104] (helo=Xeon) by Tiger.server100.com with asmtp (Exim 4.20) id 19pPos-00061R-Vj for pgsql-performance@postgresql.org; Wed, 20 Aug 2003 05:03:59 -0500 Message-ID: <004201c36701$fffee930$1401010a@Xeon> Reply-To: From: To: Subject: When NOT to index small tables? Date: Wed, 20 Aug 2003 13:01:16 +0300 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-4" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4807.1700 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - Tiger.server100.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - piladzi-2.biz X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.3 tagged_above=0.0 required=5.0 tests=BAYES_30, NO_REAL_NAME, RCVD_IN_RFCI X-Spam-Level: * X-Archive-Number: 200308/296 X-Sequence-Number: 3112 I have found many reasons not to index small tables (see . But I still have questions. 1. How small is small enough? 2. am I supposed to drop primary key index on so called 'label tables' knowing I am using this pk in join clause? 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? Ilustrative example: table small { id -- primary key colum_for_where_condition -- index or not to index? } table large { ... fk_for_small_table -- indexed ... } create view as select ..... inner join on common_column .. I have migrated from mysql . I have found that complex view expression takes about 10 sec using ordinary planner, but genetic alg takes about 5 sec. Strange? I can give details if someone is interested. Regards, Alvis