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>


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