Indexes and sorting

From: John Siracusa <siracusa(at)mindspring(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Indexes and sorting
Date: 2004-02-06 18:19:51
Message-ID: BC4944F7.2A72A%siracusa@mindspring.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Are indexes useful for speeding up ORDER BY clauses? Example:

CREATE TABLE t
(
a INT,
b INT,
c INT,
d INT
);

SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3 ORDER BY b;

Let's say the table just has one index:

CREATE INDEX b_idx ON t (b);

In this case, obviously the b_idx will be used and no sorting after the fact
will be required. Now let's add an index:

CREATE INDEX key_idx ON t (a, b, c);

On the same query, now the key_idx will be used and there'll be a sort
wrapped around it all. The question is, is the b_idx useful at all anymore?
Can it be used to speed up the sort step? If so, how? If not, why not?

-John

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Wei Wang 2004-02-06 18:21:04 Re: Run 2 versions of Postgresql on one machine?
Previous Message Franco Bruno Borghesi 2004-02-06 17:33:37 Re: newbie question... how do I get table structure?