From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Yeu <david.yeu@skype.net>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: ops@groupme.com <ops@groupme.com>
Subject: Re: Performance on large, append-only tables
Date: Fri, 10 Feb 2012 10:35:52 -0500
Message-ID: <13841.1328888152@sss.pgh.pa.us> (raw)
In-Reply-To: <CB582308.955%david.yeu@skype.net>
References: <CB582308.955%david.yeu@skype.net>
David Yeu <david.yeu@skype.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.yeu@skype.net, ops@groupme.com
Subject: Re: Performance on large, append-only tables
In-Reply-To: <13841.1328888152@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox