From: PFC <lists@boutiquenumerique.com>
To: alex@neteconomist.com, "Andrei Bintintan" <klodoma@ar-sd.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: [SQL] OFFSET impact on Performance???
Date: Tue, 01 Feb 2005 10:16:47 +0100
Message-ID: <opslihd9o0th1vuj@musicbox> (raw)
In-Reply-To: <33c6269f05012605473319ef40@mail.gmail.com>
References: <019f01c4fee1$1b58d600$0b00a8c0@forge>
<41EF9FD3.5020400@archonet.com>
<020d01c4fef6$595bb9d0$0b00a8c0@forge>
<41EFCC5B.5010302@archonet.com>
<33c6269f05012008391490448b@mail.gmail.com>
<87y8eh1hy9.fsf@stark.xeocode.com>
<015a01c5038f$73a70720$0b00a8c0@forge>
<33c6269f05012605473319ef40@mail.gmail.com>
> As I read the docs, a temp table doesn't solve our problem, as it does
> not persist between sessions. With a web page there is no guarentee
> that you will receive the same connection between requests, so a temp
> table doesn't solve the problem. It looks like you either have to
> create a real table (which is undesirable becuase it has to be
> physicaly synced, and TTFB will be very poor) or create an application
> tier in between the web tier and the database tier to allow data to
> persist between requests tied to a unique session id.
>
> Looks like the solutions to this problem is not RDBMS IMHO.
>
> Alex Turner
> NetEconomist
Did you miss the proposal to store arrays of the found rows id's in a
"cache" table ? Is 4 bytes per result row still too large ?
If it's still too large, you can still implement the same cache in the
filesystem !
If you want to fetch 100.000 rows containing just an integer, in my case
(psycopy) it's a lot faster to use an array aggregate. Time to get the
data in the application (including query) :
select id from temp
=> 849 ms
select int_array_aggregate(id) as ids from temp
=> 300 ms
So you can always fetch the whole wuery results (in the form of an
integer per row) and cache it in the filesystem. It won't work if you have
10 million rows though !
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-sql@postgresql.org
Cc: lists@boutiquenumerique.com, klodoma@ar-sd.net, pgsql-performance@postgresql.org
Subject: Re: [SQL] OFFSET impact on Performance???
In-Reply-To: <opslihd9o0th1vuj@musicbox>
* 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