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

Indices for both where and order by.

From: "Niclas Gustafsson" <niclas(dot)gustafsson(at)codesense(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Indices for both where and order by.
Date: 2002-05-17 10:54:00
Message-ID: 007801c1fd91$270f6490$b700a8c0@gmg (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-general
Hi all, 

A quick question about indices...

I have a table with a couple of column, two of which I use in 
a query, one for the where and one for the order by.

Table look something like:
                 Table "hs"
  Attribute|         Type          |                          Modifier

-----------+-----------------------+------------------------------------
------------------------
 id        | integer               | not null default
nextval('"hs_id_seq"'::text)
 timeid    | integer               | 
 code      | character varying(12) | 
 tcode     | character varying(10) | 
 inst      | character varying(10) | 
 timestamp | integer               | 
.
.
.

The query I use is like this:

Select * from hs where tcode = 'XXZZ' order by timestamp
The table above contains something like 500 000 rows

If I create an index on the tcode I can speed the query up using an
index scan, however the 
Sorting is taking some time.

An idea was to create an Index on both tcode and timestamp, like this
Create index hs_tcode_timestamp_idx on hs (tcode, timestamp)

Am I completely wrong here, or could the planner not use it for both the
where and the sorting?


Regards,

Niclas Gustafsson

 



Responses

pgsql-admin by date

Next:From: Jean-Michel POUREDate: 2002-05-18 16:53:19
Subject: UTF-8 safe ascii() function
Previous:From: Rajesh Kumar Mallah.Date: 2002-05-17 07:34:29
Subject: Re: Data has got Successfully Recovered !

pgsql-general by date

Next:From: Wm. G. UrquhartDate: 2002-05-17 11:54:42
Subject: What am I doing wrong!
Previous:From: arnaud.mlist1Date: 2002-05-17 07:25:26
Subject: Re: GiST, R-TREE, Lexicographic index ?

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