By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,590 Members | 1,025 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,590 IT Pros & Developers. It's quick & easy.

Indexes and sorting

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
On Fri, 6 Feb 2004, John Siracusa wrote:
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);
If you're really doing the above alot, you probably really want (b,a,c)
which can probably avoid the sort as well (unless of course you're also
doing frequent sorts on a, etc...)
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?
Yes. Queries searching on just b won't use key_idx.
Can it be used to speed up the sort step? If so, how? If not, why not?


Not really at least right now.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #2

P: n/a
John Siracusa wrote:
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?


I believe that if you want to do ORDER BY b, then INDEX ON t(b) is
needed. Or ON t(b,c). If you want to ORDER BY a, then either INDEX ON
t(a) or ON t(a,b,c) or ON t(a,c) etc. is needed. See the manual on
CREATE INDEX for more details.

--
dave
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.