Indices for both where and order by.

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

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

Niclas Gustafsson <Niclas(dot)Gustafsson(at)codesense(dot)com> writes:
> Select * from hs where tcode = 'XXZZ' order by timestamp

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

You'd have to write
Select * from hs where tcode = 'XXZZ' order by tcode, timestamp
to get the planner to realize that the index ordering is what's wanted.

regards, tom lane