On Mon, Aug 5, 2013 at 11:28 PM, Tomas Vondra wrote:
> Hi,
>
> On 5.8.2013 17:55, Tasos Petalas wrote:
> >
> > Seems most of the I/O is caused by SELECT backend processes (READ),
> > whereas (WRITE) requests of wal writer and checkpointer processes do
> > not appear as top IO proceses
Sergey Konoplev-2 wrote
> As an alternative solution for pagination (OFFSET) problem you might
> also use the "prev/next" technique, like
>
> SELECT * FROM table
> WHERE id > :current_last_id
> ORDER BY id LIMIT 10
>
> for "next", and
>
> SELECT * FROM (
> SELECT * FROM table
> WHERE id
On Mon, Aug 5, 2013 at 6:22 PM, Josh Berkus wrote:
>> Or, more generally, is there some set of circumstances under which the
>> catastrophic scenario will happen?
>
> Yes:
>
> SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 10
>
> This is the "high offset" problem, and affects all databas
Ivan,
> Or, more generally, is there some set of circumstances under which the
> catastrophic scenario will happen?
Yes:
SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 10
This is the "high offset" problem, and affects all databases which
support applications with paginated results, in
On Tue, Aug 6, 2013 at 8:25 AM, Claudio Freire wrote:
> On Mon, Aug 5, 2013 at 8:04 PM, Ivan Voras wrote:
> > SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 10
> >
> > SELECT * FROM table WHERE active ORDER BY id DESC LIMIT 10 OFFSET 10
>
> Did you try explain?
>
And did you run ANALYZE on
On Mon, Aug 5, 2013 at 8:04 PM, Ivan Voras wrote:
> SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 10
>
> SELECT * FROM table WHERE active ORDER BY id DESC LIMIT 10 OFFSET 10
Did you try explain?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make change
Hello,
Assuming I have a huge table (doesn't fit in RAM), of which the most
important fields are "id" which is a SERIAL PRIMARY KEY and "active"
which is a boolean, and I'm issuing a query like:
SELECT * FROM table ORDER BY id DESC LIMIT 10
... is pgsql smart enough to use the index to fetch onl
Hi,
On 5.8.2013 17:55, Tasos Petalas wrote:
>
> Seems most of the I/O is caused by SELECT backend processes (READ),
> whereas (WRITE) requests of wal writer and checkpointer processes do
> not appear as top IO proceses (correct me if I am wrong)
>
> E.g. check the follwoing heavy wr
I apologise, I have neglected to mention Postgres versions tested. It occurs
with 9.0 and 9.2
I have typo in my previous message - the sentence about vacuum, reindex and
analyze should be:
"I had also run vacuum, reindex and analyze on the whole database, but it seems to
have had no effect."
