From: Ahmad Fajar <fajar@it-indonesia.info>
To: pgsql-performance@postgresql.org
Subject: Re: Multiple Order By Criteria
Date: Wed, 18 Jan 2006 12:54:44 +0700
Message-ID: <001401c61bf3$aec1fef0$7f00a8c0@kicommunication.com> (raw)
In-Reply-To: <003201c61bc5$6652d690$81300d05@fatchubby>
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of J@Planeti.Biz
Sent: Rabu, 18 Januari 2006 07:23
To: Stephan Szabo
Cc: Josh Berkus; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Multiple Order By Criteria
I've read all of this info, closely. I wish when I was searching for an
answer for my problem these pages came up. Oh well.
Well, I think you have to know about btree index. Btree is good enough,
although it's not better. It will perform best, if it doesn't index
too many multiple column.
In your case, you have to consentrate on 2 or 3 fields that will
use frequently. Put the most duplicate value on the front and others
are behind.
Eq:
receipt, carrier_id, batchnum is the most frequently use,
but the most duplicate value are: carrier_id, receipt, and batchnum
so make btree index (carrier_id, receipt, batchnum).
Btree will not suffer, and we also will advantage if the table
have relationship with other table with the same fields order. We have
not to make another index for that relation.
Best regards,
ahmad fajar.
> I am getting an idea of what I need to do to make this work well. I was
> wondering if there is more information to read on how to implement this
> solution in a more simple way. Much of what's written seems to be towards
> audience that should understand certain things automatically.
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: <J@planeti.biz>
Cc: "Josh Berkus" <josh@agliodbs.com>; <pgsql-performance@postgresql.org>
Sent: Tuesday, January 17, 2006 6:39 PM
Subject: Re: [PERFORM] Multiple Order By Criteria
>
> On Tue, 17 Jan 2006 J@Planeti.Biz wrote:
>
>> I created the index like this:
>>
>> CREATE INDEX rcbee_idx
>> ON detail_summary
>> USING btree
>> (receipt, carrier_id, batchnum, encounternum, encounter_id);
>>
>> Is this correct ?
>
> That would work if you were asking for all the columns ascending or
> descending, but we don't currently use it for mixed orders.
>
>> How do I make a reverse opclass ?
>
> There's some information at the following:
> http://archives.postgresql.org/pgsql-novice/2005-10/msg00254.php
> http://archives.postgresql.org/pgsql-general/2005-01/msg00121.php
> http://archives.postgresql.org/pgsql-general/2004-06/msg00565.php
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: fajar@it-indonesia.info
Subject: Re: Multiple Order By Criteria
In-Reply-To: <001401c61bf3$aec1fef0$7f00a8c0@kicommunication.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox