From J@Planeti.Biz Tue Jan  17 22:03:39 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 2D92A9DC841
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 18:03:39 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 37842-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 18:03:38 -0400 (AST)
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id E2C2D9DC80F
	for <pgsql-performance@postgresql.org>;
	Tue, 17 Jan 2006 18:03:33 -0400 (AST)
Received: from plain.rackshack.net (unknown [66.98.130.45])
	by svr4.postgresql.org (Postfix) with ESMTP id 644395AF057
	for <pgsql-performance@postgresql.org>;
	Tue, 17 Jan 2006 22:03:36 +0000 (GMT)
Received: from fatchubby (cpe-071-070-134-248.nc.res.rr.com [71.70.134.248])
	by plain.rackshack.net (8.12.10/8.12.5) with SMTP id k0HLsl3x011535
	for <pgsql-performance@postgresql.org>; Tue, 17 Jan 2006 16:54:47 -0500
From: J@Planeti.Biz
Message-ID: <05c301c61bb1$d88c6a00$681e140a@fatchubby>
To: <pgsql-performance@postgresql.org>
Subject: Multiple Order By Criteria
Date: Tue, 17 Jan 2006 17:03:27 -0500
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_05C0_01C61B87.EF7142B0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.551 required=5 tests=[HTML_MESSAGE=0.001,
	NO_REAL_NAME=0.55]
X-Spam-Score: 0.551
X-Spam-Level: 
X-Archive-Number: 200601/268
X-Sequence-Number: 16746

This is a multi-part message in MIME format.

------=_NextPart_000_05C0_01C61B87.EF7142B0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I'm trying to query a table with 250,000+ rows. My query requires I =
provide 5 colums in my "order by" clause:

select
   column
from table=20
where=20
 column >=3D '2004-3-22 0:0:0'
order by=20
    ds.receipt desc,
    ds.carrier_id asc,
    ds.batchnum asc,
    encounternum asc,
    ds.encounter_id ASC
limit 100 offset 0

I have an index built for each of these columns in my order by clause. =
This query takes an unacceptable amount of time to execute. Here are the =
results of the explain:

Limit  (cost=3D229610.78..229611.03 rows=3D100 width=3D717)
  ->  Sort  (cost=3D229610.78..230132.37 rows=3D208636 width=3D717)
        Sort Key: receipt, carrier_id, batchnum, encounternum, =
encounter_id
        ->  Seq Scan on detail_summary ds  (cost=3D0.00..22647.13 =
rows=3D208636 width=3D717)
              Filter: (receipt >=3D '2004-03-22'::date)


When I have the order by just have 1 criteria, it's fine (just =
ds.receipt DESC)

Limit  (cost=3D0.00..177.71 rows=3D100 width=3D717)
  ->  Index Scan Backward using detail_summary_receipt_id_idx on =
detail_summary ds  (cost=3D0.00..370756.84 rows=3D208636 width=3D717)
        Index Cond: (receipt >=3D '2004-03-22'::date)

I've increased my work_mem to up to 256meg with no speed increase. I =
think there's something here I just don't understand.

How do I make this go fast ?






------=_NextPart_000_05C0_01C61B87.EF7142B0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2802" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I'm trying to query a table with =
250,000+ rows. My=20
query requires I provide 5 colums in my "order by" clause:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial =
size=3D2>select<BR>&nbsp;&nbsp;&nbsp;column</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>from&nbsp;table </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>where </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;column &gt;=3D '2004-3-22 =
0:0:0'<BR>order by=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; ds.receipt =
desc,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; ds.carrier_id =
asc,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; ds.batchnum =
asc,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; encounternum =
asc,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; ds.encounter_id =
ASC</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>limit 100 offset 0</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I have an index built for each of these =
columns in=20
my order by clause. This query takes an unacceptable amount of time to =
execute.=20
Here are the results of the explain:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Limit&nbsp; =
(cost=3D229610.78..229611.03 rows=3D100=20
width=3D717)<BR>&nbsp; -&gt;&nbsp; Sort&nbsp; =
(cost=3D229610.78..230132.37=20
rows=3D208636 width=3D717)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
Sort Key:=20
receipt, carrier_id, batchnum, encounternum,=20
encounter_id<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; =
Seq Scan=20
on detail_summary ds&nbsp; (cost=3D0.00..22647.13 rows=3D208636=20
width=3D717)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;=20
Filter: (receipt &gt;=3D '2004-03-22'::date)<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>When I have the order by just have 1 =
criteria, it's=20
fine (just ds.receipt DESC)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Limit&nbsp; (cost=3D0.00..177.71 =
rows=3D100=20
width=3D717)<BR>&nbsp; -&gt;&nbsp; Index Scan Backward using=20
detail_summary_receipt_id_idx on detail_summary ds&nbsp; =
(cost=3D0.00..370756.84=20
rows=3D208636 width=3D717)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
Index Cond:=20
(receipt &gt;=3D '2004-03-22'::date)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I've increased my work_mem to up to =
256meg with no=20
speed increase. I think there's something here I just don't=20
understand.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>How do I make this go fast ?</DIV>
<DIV><BR></DIV></FONT>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_05C0_01C61B87.EF7142B0--




From josh@agliodbs.com Tue Jan  17 22:19:53 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 04A009DC993
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 18:19:53 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 42249-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 18:19:52 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from davinci.ethosmedia.com (server227.ethosmedia.com
	[209.128.84.227])
	by postgresql.org (Postfix) with ESMTP id 1ECAA9DC9A6
	for <pgsql-performance@postgresql.org>;
	Tue, 17 Jan 2006 18:19:47 -0400 (AST)
X-EthosMedia-Virus-Scanned: no infections found
Received: from [64.81.245.111] (account josh@agliodbs.com HELO [192.168.1.27])
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8)
	with ESMTP id 8836625; Tue, 17 Jan 2006 14:22:36 -0800
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: pgsql-performance@postgresql.org
Subject: Re: Multiple Order By Criteria
Date: Tue, 17 Jan 2006 14:25:38 -0800
User-Agent: KMail/1.8
Cc: J@planeti.biz
References: <05c301c61bb1$d88c6a00$681e140a@fatchubby>
In-Reply-To: <05c301c61bb1$d88c6a00$681e140a@fatchubby>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200601171425.38772.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.076 required=5 tests=[AWL=0.076]
X-Spam-Score: 0.076
X-Spam-Level: 
X-Archive-Number: 200601/269
X-Sequence-Number: 16747

J,

> I have an index built for each of these columns in my order by clause.
> This query takes an unacceptable amount of time to execute. Here are the
> results of the explain:

You need a single index which has all five columns, in order.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



From J@Planeti.Biz Tue Jan  17 22:29:16 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 30FAE9DC841
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 18:29:16 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 46579-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 18:29:14 -0400 (AST)
X-Greylist: delayed 00:25:37.625666 by SQLgrey-
Received: from plain.rackshack.net (unknown [66.98.130.45])
	by postgresql.org (Postfix) with ESMTP id 004D99DC80F
	for <pgsql-performance@postgresql.org>;
	Tue, 17 Jan 2006 18:29:09 -0400 (AST)
Received: from fatchubby (cpe-071-070-134-248.nc.res.rr.com [71.70.134.248])
	by plain.rackshack.net (8.12.10/8.12.5) with SMTP id k0HMKO3x012136;
	Tue, 17 Jan 2006 17:20:24 -0500
From: J@Planeti.Biz
Message-ID: <05d801c61bb5$6c8824d0$681e140a@fatchubby>
To: <josh@agliodbs.com>, <pgsql-performance@postgresql.org>
References: <05c301c61bb1$d88c6a00$681e140a@fatchubby>
	<200601171425.38772.josh@agliodbs.com>
Subject: Re: Multiple Order By Criteria
Date: Tue, 17 Jan 2006 17:29:04 -0500
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.255 required=5 tests=[AWL=-0.464, BIZ_TLD=1.169,
	NO_REAL_NAME=0.55]
X-Spam-Score: 1.255
X-Spam-Level: *
X-Archive-Number: 200601/270
X-Sequence-Number: 16748

I created the index, in order. Did a vacuum analyze on the table and my 
explain still says:

Limit  (cost=229610.78..229611.03 rows=100 width=717)
  ->  Sort  (cost=229610.78..230132.37 rows=208636 width=717)
        Sort Key: receipt, carrier_id, batchnum, encounternum, encounter_id
        ->  Seq Scan on detail_summary ds  (cost=0.00..22647.13 rows=208636 
width=717)
              Filter: (receipt >= '2004-03-22'::date)


So, for fun I did
set enable_seqscan to off

But that didn't help. For some reason, the sort wants to do a seq scan and 
not use my super new index.

Am I doing something wrong ?

----- Original Message ----- 
From: "Josh Berkus" <josh@agliodbs.com>
To: <pgsql-performance@postgresql.org>
Cc: <J@planeti.biz>
Sent: Tuesday, January 17, 2006 5:25 PM
Subject: Re: [PERFORM] Multiple Order By Criteria


> J,
>
>> I have an index built for each of these columns in my order by clause.
>> This query takes an unacceptable amount of time to execute. Here are the
>> results of the explain:
>
> You need a single index which has all five columns, in order.
>

> -- 
> --Josh
>
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
> 




From sszabo@megazone.bigpanda.com Tue Jan  17 22:40:36 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id E3B589DC993
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 18:40:36 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 52681-03
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 18:40:38 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by postgresql.org (Postfix) with ESMTP id D756A9DC99E
	for <pgsql-performance@postgresql.org>;
	Tue, 17 Jan 2006 18:40:34 -0400 (AST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id E2639356DB; Tue, 17 Jan 2006 14:40:36 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id DB3063569C; Tue, 17 Jan 2006 14:40:36 -0800 (PST)
Date: Tue, 17 Jan 2006 14:40:36 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org, J@planeti.biz
Subject: Re: Multiple Order By Criteria
In-Reply-To: <200601171425.38772.josh@agliodbs.com>
Message-ID: <20060117143952.D47156@megazone.bigpanda.com>
References: <05c301c61bb1$d88c6a00$681e140a@fatchubby>
	<200601171425.38772.josh@agliodbs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.084 required=5 tests=[AWL=0.084]
X-Spam-Score: 0.084
X-Spam-Level: 
X-Archive-Number: 200601/271
X-Sequence-Number: 16749


On Tue, 17 Jan 2006, Josh Berkus wrote:

> J,
>
> > I have an index built for each of these columns in my order by clause.
> > This query takes an unacceptable amount of time to execute. Here are the
> > results of the explain:
>
> You need a single index which has all five columns, in order.

I think he'll also need a reverse opclass for the first column in the
index or for the others since he's doing desc, asc, asc, asc, asc.



From fred@arkansaswebs.com Tue Jan  17 22:57:11 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 8A52B9DC99E
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 18:57:11 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 54541-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 18:57:13 -0400 (AST)
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 5F4DE9DC80F
	for <pgsql-performance@postgresql.org>;
	Tue, 17 Jan 2006 18:57:09 -0400 (AST)
Received: from electronsrus.com (adsl-69-154-123-204.dsl.fyvlar.swbell.net
	[69.154.123.204])
	by svr4.postgresql.org (Postfix) with ESMTP id 86A435AF05E
	for <pgsql-performance@postgresql.org>;
	Tue, 17 Jan 2006 22:57:12 +0000 (GMT)
Received: from bits.electronsrus.com (bits.electronsrus.com [70.178.169.136])
	(authenticated bits=0)
	by electronsrus.com (8.13.4/8.13.4/Debian-2) with ESMTP id
	k0HMvEwn023036; Tue, 17 Jan 2006 16:57:15 -0600
From: Fredrick O Jackson <fred@arkansaswebs.com>
To: pgsql-performance@postgresql.org
Subject: Re: Multiple Order By Criteria
Date: Tue, 17 Jan 2006 16:57:06 -0600
User-Agent: KMail/1.8.3
Cc: J@planeti.biz
References: <05c301c61bb1$d88c6a00$681e140a@fatchubby>
	<200601171425.38772.josh@agliodbs.com>
	<05d801c61bb5$6c8824d0$681e140a@fatchubby>
In-Reply-To: <05d801c61bb5$6c8824d0$681e140a@fatchubby>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200601171657.06542@bits.electronsrus.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.585 required=5 tests=[AWL=-0.585, BIZ_TLD=1.169]
X-Spam-Score: 0.585
X-Spam-Level: 
X-Archive-Number: 200601/275
X-Sequence-Number: 16753


try adding the keyword 'date' before the date in your query.
I ran into this quite a while back, but I'm not sure I remember the solution.


 > In Reply to: Tuesday January 17 2006 04:29 pm, J@planeti.biz J@planeti.biz 
wrote:
> I created the index, in order. Did a vacuum analyze on the table and my
> explain still says:
>
> Limit  (cost=229610.78..229611.03 rows=100 width=717)
>   ->  Sort  (cost=229610.78..230132.37 rows=208636 width=717)
>         Sort Key: receipt, carrier_id, batchnum, encounternum, encounter_id
>         ->  Seq Scan on detail_summary ds  (cost=0.00..22647.13 rows=208636
> width=717)
>               Filter: (receipt >= '2004-03-22'::date)
>
>
> So, for fun I did
> set enable_seqscan to off
>
> But that didn't help. For some reason, the sort wants to do a seq scan and
> not use my super new index.
>
> Am I doing something wrong ?
>
> ----- Original Message -----
> From: "Josh Berkus" <josh@agliodbs.com>
> To: <pgsql-performance@postgresql.org>
> Cc: <J@planeti.biz>
> Sent: Tuesday, January 17, 2006 5:25 PM
> Subject: Re: [PERFORM] Multiple Order By Criteria
>
> > J,
> >
> >> I have an index built for each of these columns in my order by clause.
> >> This query takes an unacceptable amount of time to execute. Here are the
> >> results of the explain:
> >
> > You need a single index which has all five columns, in order.
> >
> >
> > --
> > --Josh
> >
> > Josh Berkus
> > Aglio Database Solutions
> > San Francisco
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >       subscribe-nomail command to majordomo@postgresql.org so that your
> >       message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org



From J@Planeti.Biz Tue Jan  17 23:03:19 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 526DA9DC841
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 19:03:19 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 56643-08
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 19:03:18 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from plain.rackshack.net (unknown [66.98.130.45])
	by postgresql.org (Postfix) with ESMTP id 07ACB9DC81C
	for <pgsql-performance@postgresql.org>;
	Tue, 17 Jan 2006 19:03:13 -0400 (AST)
Received: from fatchubby (cpe-071-070-134-248.nc.res.rr.com [71.70.134.248])
	by plain.rackshack.net (8.12.10/8.12.5) with SMTP id k0HMsN3x012824;
	Tue, 17 Jan 2006 17:54:25 -0500
From: J@Planeti.Biz
Message-ID: <063d01c61bba$2d97fc00$681e140a@fatchubby>
To: "Stephan Szabo" <sszabo@megazone.bigpanda.com>,
	"Josh Berkus" <josh@agliodbs.com>
Cc: <pgsql-performance@postgresql.org>
References: <05c301c61bb1$d88c6a00$681e140a@fatchubby>
	<200601171425.38772.josh@agliodbs.com>
	<20060117143952.D47156@megazone.bigpanda.com>
Subject: Re: Multiple Order By Criteria
Date: Tue, 17 Jan 2006 18:03:04 -0500
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.547 required=5 tests=[AWL=-0.172, BIZ_TLD=1.169,
	NO_REAL_NAME=0.55]
X-Spam-Score: 1.547
X-Spam-Level: *
X-Archive-Number: 200601/276
X-Sequence-Number: 16754

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 ?

How do I make a reverse opclass ?

----- Original Message ----- 
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "Josh Berkus" <josh@agliodbs.com>
Cc: <pgsql-performance@postgresql.org>; <J@planeti.biz>
Sent: Tuesday, January 17, 2006 5:40 PM
Subject: Re: [PERFORM] Multiple Order By Criteria


>
> On Tue, 17 Jan 2006, Josh Berkus wrote:
>
>> J,
>>
>> > I have an index built for each of these columns in my order by clause.
>> > This query takes an unacceptable amount of time to execute. Here are 
>> > the
>> > results of the explain:
>>
>> You need a single index which has all five columns, in order.
>
> I think he'll also need a reverse opclass for the first column in the
> index or for the others since he's doing desc, asc, asc, asc, asc.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
> 




From sszabo@megazone.bigpanda.com Tue Jan  17 23:39:02 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 846B89DC841
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 19:39:02 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 66093-03
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 19:39:04 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by postgresql.org (Postfix) with ESMTP id 60D519DC80F
	for <pgsql-performance@postgresql.org>;
	Tue, 17 Jan 2006 19:39:00 -0400 (AST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 9D73435784; Tue, 17 Jan 2006 15:39:03 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 99878356B3; Tue, 17 Jan 2006 15:39:03 -0800 (PST)
Date: Tue, 17 Jan 2006 15:39:03 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: J@Planeti.Biz
Cc: Josh Berkus <josh@agliodbs.com>, pgsql-performance@postgresql.org
Subject: Re: Multiple Order By Criteria
In-Reply-To: <063d01c61bba$2d97fc00$681e140a@fatchubby>
Message-ID: <20060117152915.T50406@megazone.bigpanda.com>
References: <05c301c61bb1$d88c6a00$681e140a@fatchubby>
	<200601171425.38772.josh@agliodbs.com>
	<20060117143952.D47156@megazone.bigpanda.com>
	<063d01c61bba$2d97fc00$681e140a@fatchubby>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.67 required=5 tests=[AWL=-0.499, BIZ_TLD=1.169]
X-Spam-Score: 0.67
X-Spam-Level: 
X-Archive-Number: 200601/278
X-Sequence-Number: 16756


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



From J@Planeti.Biz Wed Jan  18 00:23:33 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 8B6C39DC9AD
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 20:23:33 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 74454-04
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 20:23:36 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from bob.planeti.biz (unknown [66.98.130.45])
	by postgresql.org (Postfix) with ESMTP id 75F3D9DC80F
	for <pgsql-performance@postgresql.org>;
	Tue, 17 Jan 2006 20:23:31 -0400 (AST)
Received: from fatchubby (cpe-071-070-134-248.nc.res.rr.com [71.70.134.248])
	by bob.planeti.biz (8.12.10/8.12.5) with SMTP id k0I0EifR014288;
	Tue, 17 Jan 2006 19:14:44 -0500
From: J@Planeti.Biz
Message-ID: <003201c61bc5$6652d690$81300d05@fatchubby>
To: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
Cc: "Josh Berkus" <josh@agliodbs.com>, <pgsql-performance@postgresql.org>
References: <05c301c61bb1$d88c6a00$681e140a@fatchubby>
	<200601171425.38772.josh@agliodbs.com>
	<20060117143952.D47156@megazone.bigpanda.com>
	<063d01c61bba$2d97fc00$681e140a@fatchubby>
	<20060117152915.T50406@megazone.bigpanda.com>
Subject: Re: Multiple Order By Criteria
Date: Tue, 17 Jan 2006 19:23:25 -0500
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.644 required=5 tests=[AWL=-0.075, BIZ_TLD=1.169,
	NO_REAL_NAME=0.55]
X-Spam-Score: 1.644
X-Spam-Level: *
X-Archive-Number: 200601/279
X-Sequence-Number: 16757

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.

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 an
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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 




From fajar@it-indonesia.info Wed Jan  18 05:51:34 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 54F6E9DC884
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 18 Jan 2006 01:51:34 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 43128-02
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 18 Jan 2006 01:51:31 -0400 (AST)
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id BA7B79DC833
	for <pgsql-performance@postgresql.org>;
	Wed, 18 Jan 2006 01:51:29 -0400 (AST)
Received: from ki-communication.com (unknown [202.147.194.21])
	by svr4.postgresql.org (Postfix) with ESMTP id 05CDA5AF07E
	for <pgsql-performance@postgresql.org>;
	Wed, 18 Jan 2006 05:51:28 +0000 (GMT)
Received: from edp01wks ([192.168.0.127]) by ki-communication.com with
	Microsoft SMTPSVC(6.0.3790.1830); Wed, 18 Jan 2006 12:54:53 +0700
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
Keywords: Postgresql
Message-ID: <001401c61bf3$aec1fef0$7f00a8c0@kicommunication.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 11
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
Thread-Index: AcYbxX5pEf+aCeStTbS7W2LJDBqN1QALCNdg
In-Reply-To: <003201c61bc5$6652d690$81300d05@fatchubby>
X-OriginalArrivalTime: 18 Jan 2006 05:54:53.0281 (UTC)
	FILETIME=[B3B7D510:01C61BF3]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.169 required=5 tests=[BIZ_TLD=1.169]
X-Spam-Score: 1.169
X-Spam-Level: *
X-Archive-Number: 200601/285
X-Sequence-Number: 16763



-----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
>




From J@Planeti.Biz Wed Jan  18 14:06:08 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 7F78C9DC833
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 18 Jan 2006 10:06:08 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 50828-03
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 18 Jan 2006 10:06:12 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from bob.planeti.biz (unknown [66.98.130.45])
	by postgresql.org (Postfix) with ESMTP id 244619DC81D
	for <pgsql-performance@postgresql.org>;
	Wed, 18 Jan 2006 10:06:05 -0400 (AST)
Received: from fatchubby (cpe-071-070-134-248.nc.res.rr.com [71.70.134.248])
	by bob.planeti.biz (8.12.10/8.12.5) with SMTP id k0IDvJfR018003
	for <pgsql-performance@postgresql.org>; Wed, 18 Jan 2006 08:57:19 -0500
From: J@Planeti.Biz
Message-ID: <00d801c61c38$52863030$81300d05@fatchubby>
To: <pgsql-performance@postgresql.org>
References: <001401c61bf3$aec1fef0$7f00a8c0@kicommunication.com>
Subject: Re: Multiple Order By Criteria
Date: Wed, 18 Jan 2006 09:06:05 -0500
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.108 required=5 tests=[AWL=0.558, NO_REAL_NAME=0.55]
X-Spam-Score: 1.108
X-Spam-Level: *
X-Archive-Number: 200601/291
X-Sequence-Number: 16769

I have the answer I've been looking for and I'd like to share with all. 
After help from you guys, it appeared that the real issue was using an index 
for my order by X DESC clauses. For some reason that doesn't make good 
sense, postgres doesn't support this, when it kinda should automatically.

Take the following end of an SQL statement.

order by
    col1 DESC
    col2 ASC
    col3 ASC

The first thing I learned is that you need an index that contains all these 
columns in it, in this order. If one of them has DESC then you have to 
create a function / operator class for each data type, in this case let's 
assume it's an int4. So, first thing you do is create a function that you're 
going to use in your operator:

create function
    int4_revcmp(int4,int4)  // --> cal the function whatever you want
    returns int4
    as 'select $2 - $1'
language sql;

Then you make your operator class.
CREATE OPERATOR CLASS int4_revop
      FOR TYPE int4 USING btree AS
          OPERATOR        1       > ,
          OPERATOR        2       >= ,
          OPERATOR        3       = ,
          OPERATOR        4       <= ,
          OPERATOR        5       < ,
          FUNCTION        1       int4_revcmp(int4, int4); // --> must be 
the name of your function you created.

Then when you make your index

create index rev_idx on table
    using btree(
        col1 int4_revop,    // --> must be name of operator class you 
defined.
        col2,
        col3
);

What I don't understand is how to make this function / operator class work 
with a text datatype. I tried interchanging the int4 with char and text and 
postgres didn't like the (as 'select $2 - $1') in the function, which I can 
kinda understand. Since I'm slighlty above my head at this point, I don't 
really know how to do it. Does any smart people here know how ?




From sszabo@megazone.bigpanda.com Wed Jan  18 15:10:28 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 3E5879DC984
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 18 Jan 2006 11:10:28 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 62687-02
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 18 Jan 2006 11:10:32 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by postgresql.org (Postfix) with ESMTP id EFCED9DC81D
	for <pgsql-performance@postgresql.org>;
	Wed, 18 Jan 2006 11:10:25 -0400 (AST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 2D4E635A57; Wed, 18 Jan 2006 07:10:30 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 2B5FC35A49; Wed, 18 Jan 2006 07:10:30 -0800 (PST)
Date: Wed, 18 Jan 2006 07:10:30 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: J@Planeti.Biz
Cc: pgsql-performance@postgresql.org
Subject: Re: Multiple Order By Criteria
In-Reply-To: <00d801c61c38$52863030$81300d05@fatchubby>
Message-ID: <20060118065824.K33528@megazone.bigpanda.com>
References: <001401c61bf3$aec1fef0$7f00a8c0@kicommunication.com>
	<00d801c61c38$52863030$81300d05@fatchubby>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.698 required=5 tests=[AWL=-0.471, BIZ_TLD=1.169]
X-Spam-Score: 0.698
X-Spam-Level: 
X-Archive-Number: 200601/295
X-Sequence-Number: 16773

On Wed, 18 Jan 2006 J@Planeti.Biz wrote:

> I have the answer I've been looking for and I'd like to share with all.
> After help from you guys, it appeared that the real issue was using an index
> for my order by X DESC clauses. For some reason that doesn't make good
> sense, postgres doesn't support this, when it kinda should automatically.

Well, the problem is that we do order with the index simply by through
following index order.  Standard index order is going to give you a sorted
order only in some particular order and its inverse.  IIRC, there are ways
to use an index in all ascending order to do mixed orders, but I think
those may involve traversing parts of the index multiple times and hasn't
been implemented.

> The first thing I learned is that you need an index that contains all these
> columns in it, in this order. If one of them has DESC then you have to
> create a function / operator class for each data type, in this case let's
> assume it's an int4. So, first thing you do is create a function that you're
> going to use in your operator:
>
> create function
>     int4_revcmp(int4,int4)  // --> cal the function whatever you want
>     returns int4
>     as 'select $2 - $1'
> language sql;
>
> Then you make your operator class.
> CREATE OPERATOR CLASS int4_revop
>       FOR TYPE int4 USING btree AS
>           OPERATOR        1       > ,
>           OPERATOR        2       >= ,
>           OPERATOR        3       = ,
>           OPERATOR        4       <= ,
>           OPERATOR        5       < ,
>           FUNCTION        1       int4_revcmp(int4, int4); // --> must be
> the name of your function you created.
>
> Then when you make your index
>
> create index rev_idx on table
>     using btree(
>         col1 int4_revop,    // --> must be name of operator class you
> defined.
>         col2,
>         col3
> );
>
> What I don't understand is how to make this function / operator class work
> with a text datatype. I tried interchanging the int4 with char and text and
> postgres didn't like the (as 'select $2 - $1') in the function, which I can
> kinda understand. Since I'm slighlty above my head at this point, I don't
> really know how to do it. Does any smart people here know how ?

I think having the function call the helper function for the normal
operator class for the type function with the arguments in reverse order
may work (or negating its output).

If you have any interest, there's an outstanding call for C versions of
the helper functions that we could then package up in contrib with the
operator class definitions.



From J@Planeti.Biz Wed Jan  18 19:37:43 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id D1A2D9DC9F7
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 18 Jan 2006 15:37:43 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 11945-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 18 Jan 2006 15:37:43 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from bob.planeti.biz (unknown [66.98.130.45])
	by postgresql.org (Postfix) with ESMTP id 5E78D9DC87F
	for <pgsql-performance@postgresql.org>;
	Wed, 18 Jan 2006 15:37:41 -0400 (AST)
Received: from fatchubby (cpe-071-070-134-248.nc.res.rr.com [71.70.134.248])
	by bob.planeti.biz (8.12.10/8.12.5) with SMTP id k0IJSifR027142;
	Wed, 18 Jan 2006 14:28:45 -0500
From: J@Planeti.Biz
Message-ID: <021e01c61c66$a0899190$81300d05@fatchubby>
To: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
Cc: <pgsql-performance@postgresql.org>
References: <001401c61bf3$aec1fef0$7f00a8c0@kicommunication.com>
	<00d801c61c38$52863030$81300d05@fatchubby>
	<20060118065824.K33528@megazone.bigpanda.com>
	<018a01c61c45$a49bc170$81300d05@fatchubby>
	<20060118090826.Y44833@megazone.bigpanda.com>
	<01a101c61c54$0c484010$81300d05@fatchubby>
	<20060118111040.J54519@megazone.bigpanda.com>
Subject: Re: Multiple Order By Criteria
Date: Wed, 18 Jan 2006 14:36:09 -0500
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.605 required=5 tests=[AWL=-0.114, BIZ_TLD=1.169,
	NO_REAL_NAME=0.55]
X-Spam-Score: 1.605
X-Spam-Level: *
X-Archive-Number: 200601/310
X-Sequence-Number: 16788

Here's some C to use to create the operator classes, seems to work ok.
---

#include "postgres.h"
#include <string.h>
#include "fmgr.h"
#include "utils/date.h"

/* For date sorts */

PG_FUNCTION_INFO_V1(ddd_date_revcmp);

Datum   ddd_date_revcmp(PG_FUNCTION_ARGS){
        DateADT         arg1=PG_GETARG_DATEADT(0);
        DateADT         arg2=PG_GETARG_DATEADT(1);

        PG_RETURN_INT32(arg2 - arg1);
}

/* For integer sorts */

PG_FUNCTION_INFO_V1(ddd_int_revcmp);

Datum   ddd_int_revcmp(PG_FUNCTION_ARGS){
        int32           arg1=PG_GETARG_INT32(0);
        int32           arg2=PG_GETARG_INT32(1);

        PG_RETURN_INT32(arg2 - arg1);
}

/* For string sorts */

PG_FUNCTION_INFO_V1(ddd_text_revcmp);

Datum   ddd_text_revcmp(PG_FUNCTION_ARGS){
        text*           arg1=PG_GETARG_TEXT_P(0);
        text*           arg2=PG_GETARG_TEXT_P(1);

        PG_RETURN_INT32(strcmp((char*)VARDATA(arg2),(char*)VARDATA(arg1)));
}


/*
create function ddd_date_revcmp(date,date) returns int4 as 
'/data/postgres/contrib/cmplib.so', 'ddd_date_revcmp' LANGUAGE C STRICT;
create function ddd_int_revcmp(int4,int4) returns int4 as 
'/data/postgres/contrib/cmplib.so', 'ddd_int_revcmp' LANGUAGE C STRICT;
create function ddd_text_revcmp(text,text) returns int4 as 
'/data/postgres/contrib/cmplib.so', 'ddd_text_revcmp' LANGUAGE C STRICT;
 */

----- Original Message ----- 
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: <J@Planeti.Biz>
Sent: Wednesday, January 18, 2006 2:24 PM
Subject: Re: [PERFORM] Multiple Order By Criteria


> On Wed, 18 Jan 2006 J@Planeti.Biz wrote:
>
>> Could you explain to me how do create this operator class for a text data
>> type ? I think it will give me more of an understanding of what's going 
>> on
>> if I could see this example.
>
> Using an SQL function (mostly because I'm too lazy to look up the C call
> syntax) I think it'd be something like:
>
> create function bttextrevcmp(text, text) returns int4 as
> 'select bttextcmp($2, $1)' language 'sql';
>
> CREATE OPERATOR CLASS text_revop
>      FOR TYPE text USING btree AS
>          OPERATOR        1       > ,
>          OPERATOR        2       >= ,
>          OPERATOR        3       = ,
>          OPERATOR        4       <= ,
>          OPERATOR        5       < ,
>          FUNCTION        1       bttextrevcmp(text,text);
>
> I believe bttextcmp is the standard text btree operator class helper
> function, so we call it with reverse arguments to try to flip its results
> (I think -bttextcmp($1,$2) would also work).
>