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