current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
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.
up vote 0 down vote favorite

This query is very slow:

EXPLAIN (ANALYZE, buffers) SELECT *
FROM
    "Follows" AS "Follow"
INNER JOIN "Users" AS "followee" ON "Follow"."followeeId" = "followee"."id"
WHERE
    "Follow"."followerId" = 169368
ORDER BY
    "Follow"."createdAt" DESC
LIMIT 1000;

Here is the explain:

Limit  (cost=0.86..2120.08 rows=141 width=814) (actual time=0.776..239.289 rows=262 loops=1)
  Buffers: shared hit=750 read=673 dirtied=1
  ->  Nested Loop  (cost=0.86..2120.08 rows=141 width=814) (actual time=0.774..239.148 rows=262 loops=1)
        Buffers: shared hit=750 read=673 dirtied=1
        ->  Index Scan using follows_followinglist_followerid_createdat_idx on "Follows" "Follow"  (cost=0.43..681.88 rows=170 width=41) (actual time=0.377..52.687 rows=262 loops=1)
              Index Cond: ("followerId" = 169368)
              Buffers: shared hit=149 read=115
        ->  Index Scan using "Users_pkey" on "Users" followee  (cost=0.43..8.45 rows=1 width=773) (actual time=0.559..0.709 rows=1 loops=262)
              Index Cond: (id = "Follow"."followeeId")
              Buffers: shared hit=601 read=558 dirtied=1
Total runtime: 239.545 ms

I have the following indexes (currently, it is using the first one):

CREATE INDEX follows_followinglist_followerid_createdat_idx ON "Follows"
  ("followerId", "createdAt" DESC)
CREATE INDEX follows_followinglist_followerid_createdat_idx2 ON "Follows"
  ("createdAt" DESC, "followerId")
CREATE INDEX follows_followerId_fk_index ON "Follows" ("followerId");
CREATE INDEX  "follows_createdat_index" ON "public"."Follows" USING btree("createdAt" DESC);

Before I added the first two, the cost was slightly lower (~2000) using follows_followerId_fk_index, but still too slow.

I'm wondering if there's a way to optimize this further.

share|improve this question
    
Do you need SELECT * for this query? –  John M Mar 4 at 18:43
    
I changed it to that to shorten the query for this question, but I do need many properties. –  Garrett Mar 4 at 18:49
    
We need more information. As always, your version of Postgres. Table definitions and cardinalities. Consider instruction in the tag info for postgresql-performance. –  Erwin Brandstetter Mar 5 at 10:06

1 Answer 1

up vote 2 down vote accepted

Simplified query to make it readable:

SELECT *
FROM   "Follows" f
JOIN   "Users"   u ON f."followeeId" = u."id"
WHERE  f."followerId" = 169368
ORDER  BY f."createdAt" DESC
LIMIT  1000;

Your index follows_followinglist_followerid_createdat_idx looks good for the job. In Postgres 9.2+ it might get a bit faster if you append "followeeId" to the index - if and only if you can get an index-only scan out of this. Maybe not possible in your case.

CREATE INDEX follows_foo_idx ON "Follows"("followerId", "createdAt" DESC, "followeeId");

Generally, this query won't get lighning fast, since you have to fetch up to 1000 random rows from "Users". The key to performance will be:

  • Optimized table layout of "Users" to keep the table as small as possible, so fewer blocks have to be read and more of it can stay in cache.
  • Plenty of RAM and cache settings accordingly if your table "Users" is big.

It would also help to cluster the "Follows" table from time to time. Details:

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.