Re: Performance on large, append-only tables

David Yeu <david(dot)yeu(at)skype(dot)net> writes:
> Our queries essentially fall into the following cases:

> * WHERE group_id = ? ORDER BY created_at DESC LIMIT 20;
> * WHERE group_id = ? AND id > ? ORDER BY created_at DESC;
> * WHERE group_id = ? AND id < ? ORDER BY created_at DESC LIMIT 20;
> * WHERE group_id = ? ORDER BY created_at DESC LIMIT 20 OFFSET ?;

All of those should be extremely cheap if you've got the right indexes,
with the exception of the last one. Large OFFSET values are never a
good idea, because Postgres always has to scan and discard that many
rows. If you need to fetch successive pages, consider using a cursor
with a series of FETCH commands. Another possibility, if the data is
sufficiently constrained, is to move the limit point with each new
query, ie instead of OFFSET use something like

WHERE group_id = ? AND created_at < last-previous-value
ORDER BY created_at DESC LIMIT 20;

regards, tom lane