CODEWITHSUNDEEP
sqlpostgresql
zerofuxor
zerofuxor

Reputation: 336

How to improve query performance with OR or IN operators in PostgreSQL?

I have a Item table with 1.8 millons of rows:

Item
-----------
- id
- title
- content
- channel_id

and a Channel table with 8000+ rows:

Channel
-----------
- id
- name

What i need is shows items from a channel called "global" in every result, for example i have the following channels:

id    |    name
________________
1     |    global
2     |    restaurants
3     |    hotels
...

so I have tried the following consults:

SELECT * FROM Item WHERE channel_id = 1 OR channel_id = 2 ORDER BY title ASC LIMIT 10
SELECT * FROM Item WHERE channel_id IN (1, 2) ORDER BY title ASC LIMIT 10

Both of them take around 18 seconds! ...and there are already two indexes for id and channel_id

Update

Looks the problem is the ORDER BY clouse and not the OR or IN operators, there are too many items to order.

Update I have fixed this creating a index for title:

CREATE INDEX item_by_title ON item (title ASC)

Upvotes: 1

Views: 244

Answers (1)

wildplasser
wildplasser

Reputation: 44250

By ordering by title you are forcing a final sort step for 1.8M tuples, just to get the top 10 records. Try sorting on id, for example.

Upvotes: 1

Related Questions