X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E1F3FD1EB5E for ; Mon, 5 Apr 2004 02:08:33 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 47416-06 for ; Sun, 4 Apr 2004 23:08:56 -0300 (ADT) Received: from hotmail.com (bay9-dav30.bay9.hotmail.com [64.4.46.87]) by svr1.postgresql.org (Postfix) with ESMTP id A7883D1E9AE for ; Sun, 4 Apr 2004 23:08:30 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Sun, 4 Apr 2004 19:09:02 -0700 Received: from 67.81.102.201 by bay9-dav30.bay9.hotmail.com with DAV; Mon, 05 Apr 2004 02:09:01 +0000 X-Originating-IP: [67.81.102.201] X-Originating-Email: [awerman@hotmail.com] X-Sender: awerman@hotmail.com From: "Aaron Werman" To: "Leeuw van der, Tim" , References: Subject: Re: single index on more than two coulumns a bad thing? Date: Sun, 4 Apr 2004 22:09:21 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Message-ID: X-OriginalArrivalTime: 05 Apr 2004 02:09:02.0223 (UTC) FILETIME=[F6E9B9F0:01C41AB2] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/48 X-Sequence-Number: 6348 You're absolutely correct that the general rule is to lead a composite index with the highest cardinality index columns for fastest selectivity. Indices and all physical design are based on usage. In this case of unique indices supporting primary keys in a hierarchy, it depends. For selection of small sets of arbitrary rows, your arrangement is best. For hierarchy based queries, such as "for grandparent of foo, and parent of bar, give average age of sons" - the hierarchy based index is often more efficient. Surrogate keys have a role, and can improve performance, but also carry an enormous penalty of intentionally obfuscating logical keys and data semantics, and almost always lead to data errors not being caught because they obscure irrational relationships. I hate them, but use them frequently in high transaction rate operational systems where there is much functional validation outside the dbms (and the apps behave therefore like object databases and surrogate keys are network database pointers) and in data warehousing (where downstream data cannot be corrected anyway). /Aaron ----- Original Message ----- From: "Leeuw van der, Tim" To: Sent: Sunday, April 04, 2004 5:06 PM Subject: Re: [PERFORM] single index on more than two coulumns a bad thing? Hi Aaron, > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of > Aaron Werman > Sent: vrijdag 2 april 2004 13:57 > > > another thing that I have all over the place is a hierarchy: > index on grandfather_table(grandfather) > index on father_table(grandfather, father) > index on son_table(grandfather, father, son) > It depends on your data-distribution, but I find that in almost all cases it's beneficial to have your indexes the other way round in such cases: index on grandfather_table(grandfather) index on father_table(father, grandfather) index on son_table(son, father, grandfather) That usually gives a less common, more selective value at the start of the index, making the initial selection in the index smaller. And AFAIK I don't have to rewrite my queries for that; the planner doesn't care about the order of expressions in the query that are on the same level. That said, I tend to use 'surrogate keys'; keys generated from sequences or auto-number columns for my tables. It makes the tables less readable, but the indexes remain smaller. Greetings, --Tim ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster