Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am optimising a PostgreSQL query which involves three tables and a ORDER_BY condition. It seems that PostgreSQL prefer to arrange a SORT operation to perform the ORDER_BY condition, rather than using the index.

The raw SQL looks like this:

SELECT activity.provider, activity.provider_id, activity.crawled_at, activity.raw, activity.account_provider_id, activity.created_at
FROM activity 
JOIN friendship ON (activity.provider, activity.account_provider_id) = (friendship.provider, friendship.friend_provider_id) 
JOIN token ON (token.provider, token.account_provider_id) = (friendship.provider, friendship.provider_id) 
WHERE token.uid = 1 
ORDER BY created_at desc NULLS LAST
LIMIT 10 OFFSET 10000;

I have already created an index provider, account_provider_id, created_by on the activity table.

CREATE INDEX activity_provider_account_provider_id_created_at_idx
  ON activity
  USING btree
  (provider COLLATE pg_catalog."default", account_provider_id COLLATE pg_catalog."default", created_at DESC NULLS LAST);

However, the query plan returned by PostgreSQL requires an expensive SORT operation.

default query plan query plan timing

This query would take more than 10 seconds to finish. I also tried set enable_sort = false, but it didn't work. How should I optimise this query?

EDIT: I have added the execution time graph. The SORT operation is the most expensive part of the query. Without the ORDER_BY, the query returns pretty fast.

share|improve this question

migrated from stackoverflow.com Jul 23 at 6:54

This question came from our site for professional and enthusiast programmers.

    
So the query is fast without ORDER BY? –  Explosion Pills Jul 23 at 2:52
    
@ExplosionPills Yes, the query is pretty fast without the ORDER_BY. –  Chunliang Lyu Jul 23 at 2:59
2  
What value does work_mem have? Try setting it to at least 30MB to avoid Sort method: external merge –  FuzzyTree Jul 23 at 3:22
    
What happens when you omit LIMIT 10 OFFSET 10000 clause? –  ntalbs Jul 23 at 3:32
1  
An index on a,b won't help with an ORDER BY b. Unless you have an index with created_at as the first column, the sort is unavoidable. But as @FuzzyTree suggested, the sort time might be acceptable if you can stop it from hitting the disk. –  Nick Barnes Jul 23 at 4:44

2 Answers 2

ORDER BY is not that expensive by itself. The problem here is that it is used together with OFFSET. So postgresql does sorting of all data before it can take needed 10 rows.

You are probably implementing paging using ORDER BY + OFFSET. This is a known postgresql limitation and there is postgresql way to handle this.

share|improve this answer
2  
Side-node: It's not a PostgreSQL limitation. OFFSET is bad in any database. Also: the solution shown in the slides is generally applicable to all databases (that's why I renamed the slides "Pagination done the right way"). Nevertheless, PostgreSQL is the only DB with proper row-values support which makes the "seek method" easy to implement. And thanks for linking my slides :) –  Markus Winand Jul 23 at 9:39

It turns out that I have made a mistake in explaining the problem. The SORT operation is not the most expensive part in this query, as shown in the second figure. The most expensive part is the second nested loop, which is responsible to materialise and join the activity table. The database engine need to fetch all the columns from the activity table. The query can be optimised by deferring the loading of extra columns:

SELECT * FROM activity WHERE (activity.provider, activity.account_provider_id, activity.created_at) IN (
  SELECT activity.provider, activity.account_provider_id, activity.created_at
  FROM activity 
  JOIN friendship ON (activity.provider, activity.account_provider_id) = (friendship.provider, friendship.friend_provider_id) 
  JOIN token ON (token.provider, token.account_provider_id) = (friendship.provider, friendship.provider_id) 
  WHERE token.uid = 2
  ORDER BY created_at desc NULLS LAST
  LIMIT 10 OFFSET 100
)

This can reduce the query time to ~100ms.

Both comment from @FuzzyTree and answer from @Roman Konoval are correct in the sense of the posed question.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.