| 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: | Whole Thread | Raw Message | 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? |