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 21CBFD1B4E2 for ; Thu, 2 Oct 2003 17:04:07 +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 32891-07 for ; Thu, 2 Oct 2003 14:03:18 -0300 (ADT) Received: from cbnsitar.cbnco.com (unknown [207.164.182.16]) by svr1.postgresql.org (Postfix) with ESMTP id 98634D1B4FB for ; Thu, 2 Oct 2003 14:03:15 -0300 (ADT) Received: (from root@localhost) by cbnsitar.cbnco.com (8.11.6/8.11.3/SuSE Linux 8.11.1-0.5) id h92H1pD12196 for pgsql-performance@postgresql.org; Thu, 2 Oct 2003 13:01:51 -0400 Received: from mail.cbnco.com (hermes.cbnco.com [207.164.182.47]) by cbnsitar.cbnco.com (8.11.6/8.11.3/SuSE Linux 8.11.1-0.5) with SMTP id h92H1oi12189 for ; Thu, 2 Oct 2003 13:01:50 -0400 From: rwu.cbnco.com@cbnco.com Received: from 207.164.182.8 (SquirrelMail authenticated user rwu.cbnco.com) by mail.cbnco.com with HTTP; Thu, 2 Oct 2003 13:03:13 -0400 (EDT) Message-ID: <33197.207.164.182.8.1065114193.squirrel@mail.cbnco.com> Date: Thu, 2 Oct 2003 13:03:13 -0400 (EDT) Subject: low cardinality column To: pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.1 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 Importance: Normal X-Scanned-By: MIMEDefang 2.24 (www . roaringpenguin . com / mimedefang) X-Virus-Scanned: by AMaViS 0.3.12pre6 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.5 tagged_above=0.0 required=5.0 tests=NO_REAL_NAME, PRIORITY_NO_NAME, USER_AGENT X-Spam-Level: * X-Archive-Number: 200310/86 X-Sequence-Number: 3834 Hi, I have a select like this: SELECT MAX(transactionid) FROM cbntransaction WHERE transactiontypeid=0; in the query: transactionid is the primary key of cbntransaction table, But transactiontypeid is a low cardinality column, there're over 100,000 records has the same trnsactiontypeid. I was trying to create an index on (transactiontypeid, transactionid), but no luck on that, postgresql will still scan the table. I'm wondering if there's solution for this query: Maybe something like if I can partition the table using transactiontypeid, and do a local index on transactionid on each partition, but I couldnt' find any doc on postgresql to do that. Thanks in advance, rong :-)