Skip site navigation (1) Skip section navigation (2)

Re: Indexes with descending date columns

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Theo Kramer <theo(at)flame(dot)co(dot)za>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Indexes with descending date columns
Date: 2006-03-24 10:21:38
Message-ID: 20060324102138.GR90527@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Mar 23, 2006 at 01:09:49PM +0200, Theo Kramer wrote:
> ii If no to i, is it feasible to extend PostgreSQL to allow traversing
>    an index in column descending and column ascending order - assuming
>    an order by on more than one column with column order not 
>    in the same direction and indexes existing? ... if that makes sense.

Yes.

stats=# explain select * from email_contrib order by project_id desc, id desc, date desc limit 10;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..31.76 rows=10 width=24)
   ->  Index Scan Backward using email_contrib_pkey on email_contrib  (cost=0.00..427716532.18 rows=134656656 width=24)
(2 rows)

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

Responses

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2006-03-24 10:25:20
Subject: Re: Problem with query, server totally unresponsive
Previous:From: Jim C. NasbyDate: 2006-03-24 10:16:54
Subject: Re: Postmaster using only 4-5% CPU

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group