CODEWITHSUNDEEP
sqlperformancepostgresqlindexing
Nicolas
Nicolas

Reputation: 2367

Increase PostgreSQL query performance using indices

I have a Rails app on a Postgres database which relies heavily on queries like this:

SELECT DISTINCT client_id FROM orders WHERE orders.total>100

I need, essentially, the ids of all the clients who have orders which meet a certain condition. I only need the id, so I figured this is way faster than using joins.

Would I benefit from adding an index to the column "total"? I don't mind insert speed, I just need the query to run extremely fast.

Upvotes: 1

Views: 1504

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658767

I would expect the following multicolumn index to be fastest:

CREATE INDEX orders_foo_idx ON orders (total DESC, client_id);

PostgreSQL 9.2 could benefit even more. With it's "index-only tuples" feature, it could serve the query without hitting the table under favorable circumstances: no writes since the last VACUUM.

DESC or ASC hardly matters in this case. A B-tree index can be searched in both directions almost equally efficient.

Upvotes: 4

Dave Markle
Dave Markle

Reputation: 97821

>  I only need the id, so I figured this is way faster than using joins.

True, though I'm not sure why you would consider using joins in the first place in this case.

As cmotley said, you're going to require an index on the total column for this query. However, optimal performance is going to depend on exactly which queries you're running. For example, for this query, with this table structure, the fastest you're going to get is to create an index like so:

CREATE INDEX IX_OrderTotals ON orders (total, client_id)

By including the client_id in the index, you create something called a covered index on the client_id column, so the database engine won't have to look up the row behind the scenes in order to fetch your data.

Upvotes: 1

user1172023
user1172023

Reputation:

Absolutely. With no index on the total column, this query will require a table scan. With an index on the total column, it will require an index seek and key lookup. This will provide your query with huge performance gains as the size of the table grows.

Upvotes: 1

Related Questions