[Answered ]-Postgresql slow join even with index?

by

2👍

LIMIT 1000 OFFSET 378000

Looks fast for what you’re doing; you’re generating a fairly large join, then throwing the vast majority of it away.

Instead of using an OFFSET, try doing your pagination by the primary key of the rows of interest if possible. Remember the addressbooks_address.id and whatever the key of addressbooks_recipientaddress is from the last tuple in the prior result and use a WHERE clause like:

WHERE "addressbooks_recipientaddress"."id" > $1
  AND "addressbooks_address"."id" > $2

instead of the OFFSET. That way your index scan can just skip to those records, instead of wasting all that time generating results to throw away.

If your framework can’t do that, well, that’s why I don’t like query generator frameworks.

👤Craig Ringer

Leave a comment