From: | Niclas Gustafsson <Niclas(dot)Gustafsson(at)codesense(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Indices for both where and order by. |
Date: | 2002-05-23 19:39:38 |
Message-ID: | 6987080184.20020523213938@codesense.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-05-23 20:21:56 | Re: Indices for both where and order by. |
Previous Message | l.malaspina | 2002-05-23 15:43:21 | Réf. : Re: Réf. : Re: File systems backup problem |
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2002-05-23 19:47:23 | Just PR (was: Stability, PR) |
Previous Message | Mike Marconi | 2002-05-23 19:10:06 | Stored Procedure Problem |