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 6897BD1B522 for ; Thu, 2 Oct 2003 18:30:51 +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 43380-10 for ; Thu, 2 Oct 2003 15:30:02 -0300 (ADT) Received: from cbnsitar.cbnco.com (unknown [207.164.182.16]) by svr1.postgresql.org (Postfix) with ESMTP id 51666D1B533 for ; Thu, 2 Oct 2003 15:30:01 -0300 (ADT) Received: (from root@localhost) by cbnsitar.cbnco.com (8.11.6/8.11.3/SuSE Linux 8.11.1-0.5) id h92IScV14418 for pgsql-performance@postgresql.org; Thu, 2 Oct 2003 14:28:38 -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 h92ISbi14410 for ; Thu, 2 Oct 2003 14:28:37 -0400 Received: from 207.164.182.8 (SquirrelMail authenticated user rwu.cbnco.com) by mail.cbnco.com with HTTP; Thu, 2 Oct 2003 14:30:01 -0400 (EDT) Message-ID: <33423.207.164.182.8.1065119401.squirrel@mail.cbnco.com> Date: Thu, 2 Oct 2003 14:30:01 -0400 (EDT) Subject: low cardinality column From: "Rong Wu" To: pgsql-performance@postgresql.org Reply-To: rwu@cbnco.com 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=0.4 tagged_above=0.0 required=5.0 tests=PRIORITY_NO_NAME, USER_AGENT X-Spam-Level: X-Archive-Number: 200310/54 X-Sequence-Number: 3802 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 :-)