pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOFFSET impact on Performance???
22+ messages / 11 participants
[nested] [flat]
* OFFSET impact on Performance???
@ 2005-01-20 11:13 Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-27 03:50 ` Re: OFFSET impact on Performance??? David Brown <time@bigpond.net.au>
0 siblings, 2 replies; 22+ messages in thread
From: Andrei Bintintan @ 2005-01-20 11:13 UTC (permalink / raw)
To: pgsql-sql; pgsql-performance@postgresql.org
Hi to all,
I have the following 2 examples. Now, regarding on the offset if it is small(10) or big(>50000) what is the impact on the performance of the query?? I noticed that if I return more data's(columns) or if I make more joins then the query runs even slower if the OFFSET is bigger. How can I somehow improve the performance on this?
Best regards,
Andy.
explain analyze
SELECT o.id
FROM report r
INNER JOIN orders o ON o.id=r.id_order AND o.id_status=6
ORDER BY 1 LIMIT 10 OFFSET 10
Limit (cost=44.37..88.75 rows=10 width=4) (actual time=0.160..0.275 rows=10 loops=1)
-> Merge Join (cost=0.00..182150.17 rows=41049 width=4) (actual time=0.041..0.260 rows=20 loops=1)
Merge Cond: ("outer".id_order = "inner".id)
-> Index Scan using report_id_order_idx on report r (cost=0.00..157550.90 rows=42862 width=4) (actual time=0.018..0.075 rows=20 loops=1)
-> Index Scan using orders_pkey on orders o (cost=0.00..24127.04 rows=42501 width=4) (actual time=0.013..0.078 rows=20 loops=1)
Filter: (id_status = 6)
Total runtime: 0.373 ms
explain analyze
SELECT o.id
FROM report r
INNER JOIN orders o ON o.id=r.id_order AND o.id_status=6
ORDER BY 1 LIMIT 10 OFFSET 1000000
Limit (cost=31216.85..31216.85 rows=1 width=4) (actual time=1168.152..1168.152 rows=0 loops=1)
-> Sort (cost=31114.23..31216.85 rows=41049 width=4) (actual time=1121.769..1152.246 rows=42693 loops=1)
Sort Key: o.id
-> Hash Join (cost=2329.99..27684.03 rows=41049 width=4) (actual time=441.879..925.498 rows=42693 loops=1)
Hash Cond: ("outer".id_order = "inner".id)
-> Seq Scan on report r (cost=0.00..23860.62 rows=42862 width=4) (actual time=38.634..366.035 rows=42864 loops=1)
-> Hash (cost=2077.74..2077.74 rows=42501 width=4) (actual time=140.200..140.200 rows=0 loops=1)
-> Seq Scan on orders o (cost=0.00..2077.74 rows=42501 width=4) (actual time=0.059..96.890 rows=42693 loops=1)
Filter: (id_status = 6)
Total runtime: 1170.586 ms
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
@ 2005-01-20 12:10 ` Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
1 sibling, 1 reply; 22+ messages in thread
From: Richard Huxton @ 2005-01-20 12:10 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; +Cc: pgsql-sql; pgsql-performance@postgresql.org
Andrei Bintintan wrote:
> Hi to all,
>
> I have the following 2 examples. Now, regarding on the offset if it
> is small(10) or big(>50000) what is the impact on the performance of
> the query?? I noticed that if I return more data's(columns) or if I
> make more joins then the query runs even slower if the OFFSET is
> bigger. How can I somehow improve the performance on this?
There's really only one way to do an offset of 1000 and that's to fetch
1000 rows and then some and discard the first 1000.
If you're using this to provide "pages" of results, could you use a cursor?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
@ 2005-01-20 13:45 ` Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 15:20 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 16:59 ` Re: [SQL] OFFSET impact on Performance??? Greg Stark <gsstark@mit.edu>
0 siblings, 2 replies; 22+ messages in thread
From: Andrei Bintintan @ 2005-01-20 13:45 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql; pgsql-performance@postgresql.org
> If you're using this to provide "pages" of results, could you use a
> cursor?
What do you mean by that? Cursor?
Yes I'm using this to provide "pages", but If I jump to the last pages it
goes very slow.
Andy.
----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Cc: <pgsql-sql@postgresql.org>; <pgsql-performance@postgresql.org>
Sent: Thursday, January 20, 2005 2:10 PM
Subject: Re: [SQL] OFFSET impact on Performance???
> Andrei Bintintan wrote:
>> Hi to all,
>>
>> I have the following 2 examples. Now, regarding on the offset if it
>> is small(10) or big(>50000) what is the impact on the performance of
>> the query?? I noticed that if I return more data's(columns) or if I
>> make more joins then the query runs even slower if the OFFSET is
>> bigger. How can I somehow improve the performance on this?
>
> There's really only one way to do an offset of 1000 and that's to fetch
> 1000 rows and then some and discard the first 1000.
>
> If you're using this to provide "pages" of results, could you use a
> cursor?
>
> --
> Richard Huxton
> Archonet Ltd
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
@ 2005-01-20 15:20 ` Richard Huxton <dev@archonet.com>
2005-01-20 16:39 ` Re: [SQL] OFFSET impact on Performance??? Alex Turner <armtuk@gmail.com>
2005-01-20 16:49 ` Re: [SQL] OFFSET impact on Performance??? Ron Mayer <rm_pg@cheapcomplexdevices.com>
1 sibling, 2 replies; 22+ messages in thread
From: Richard Huxton @ 2005-01-20 15:20 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; +Cc: pgsql-sql; pgsql-performance@postgresql.org
Andrei Bintintan wrote:
>> If you're using this to provide "pages" of results, could you use a
>> cursor?
>
> What do you mean by that? Cursor?
>
> Yes I'm using this to provide "pages", but If I jump to the last pages
> it goes very slow.
DECLARE mycursor CURSOR FOR SELECT * FROM ...
FETCH FORWARD 10 IN mycursor;
CLOSE mycursor;
Repeated FETCHes would let you step through your results. That won't
work if you have a web-app making repeated connections.
If you've got a web-application then you'll probably want to insert the
results into a cache table for later use.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 15:20 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
@ 2005-01-20 16:39 ` Alex Turner <armtuk@gmail.com>
2005-01-20 16:53 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-25 18:28 ` Re: [SQL] OFFSET impact on Performance??? Greg Stark <gsstark@mit.edu>
1 sibling, 2 replies; 22+ messages in thread
From: Alex Turner @ 2005-01-20 16:39 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Andrei Bintintan <klodoma@ar-sd.net>; pgsql-sql; pgsql-performance@postgresql.org
I am also very interesting in this very question.. Is there any way to
declare a persistant cursor that remains open between pg sessions?
This would be better than a temp table because you would not have to
do the initial select and insert into a fresh table and incur those IO
costs, which are often very heavy, and the reason why one would want
to use a cursor.
Alex Turner
NetEconomist
On Thu, 20 Jan 2005 15:20:59 +0000, Richard Huxton <dev@archonet.com> wrote:
> Andrei Bintintan wrote:
> >> If you're using this to provide "pages" of results, could you use a
> >> cursor?
> >
> > What do you mean by that? Cursor?
> >
> > Yes I'm using this to provide "pages", but If I jump to the last pages
> > it goes very slow.
>
> DECLARE mycursor CURSOR FOR SELECT * FROM ...
> FETCH FORWARD 10 IN mycursor;
> CLOSE mycursor;
>
> Repeated FETCHes would let you step through your results. That won't
> work if you have a web-app making repeated connections.
>
> If you've got a web-application then you'll probably want to insert the
> results into a cache table for later use.
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 15:20 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 16:39 ` Re: [SQL] OFFSET impact on Performance??? Alex Turner <armtuk@gmail.com>
@ 2005-01-20 16:53 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 22+ messages in thread
From: Richard Huxton @ 2005-01-20 16:53 UTC (permalink / raw)
To: alex@neteconomist.com; +Cc: Andrei Bintintan <klodoma@ar-sd.net>; pgsql-sql; pgsql-performance@postgresql.org
Alex Turner wrote:
> I am also very interesting in this very question.. Is there any way
> to declare a persistant cursor that remains open between pg sessions?
Not sure how this would work. What do you do with multiple connections?
Only one can access the cursor, so which should it be?
> This would be better than a temp table because you would not have to
> do the initial select and insert into a fresh table and incur those
> IO costs, which are often very heavy, and the reason why one would
> want to use a cursor.
I'm pretty sure two things mean there's less difference than you might
expect:
1. Temp tables don't fsync
2. A cursor will spill to disk beyond a certain size
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 15:20 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 16:39 ` Re: [SQL] OFFSET impact on Performance??? Alex Turner <armtuk@gmail.com>
@ 2005-01-25 18:28 ` Greg Stark <gsstark@mit.edu>
2005-01-26 10:11 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
1 sibling, 1 reply; 22+ messages in thread
From: Greg Stark @ 2005-01-25 18:28 UTC (permalink / raw)
To: alex@neteconomist.com; +Cc: Richard Huxton <dev@archonet.com>; Andrei Bintintan <klodoma@ar-sd.net>; pgsql-sql; pgsql-performance@postgresql.org
Alex Turner <armtuk@gmail.com> writes:
> I am also very interesting in this very question.. Is there any way to
> declare a persistant cursor that remains open between pg sessions?
> This would be better than a temp table because you would not have to
> do the initial select and insert into a fresh table and incur those IO
> costs, which are often very heavy, and the reason why one would want
> to use a cursor.
TANSTAAFL. How would such a persistent cursor be implemented if not by
building a temporary table somewhere behind the scenes?
There could be some advantage if the data were stored in a temporary table
marked as not having to be WAL logged. Instead it could be automatically
cleared on every database start.
--
greg
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 15:20 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 16:39 ` Re: [SQL] OFFSET impact on Performance??? Alex Turner <armtuk@gmail.com>
2005-01-25 18:28 ` Re: [SQL] OFFSET impact on Performance??? Greg Stark <gsstark@mit.edu>
@ 2005-01-26 10:11 ` Andrei Bintintan <klodoma@ar-sd.net>
2005-01-26 13:47 ` Re: [SQL] OFFSET impact on Performance??? Alex Turner <armtuk@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Andrei Bintintan @ 2005-01-26 10:11 UTC (permalink / raw)
To: alex@neteconomist.com; Greg Stark <gsstark@mit.edu>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql; pgsql-performance@postgresql.org
The problems still stays open.
The thing is that I have about 20 - 30 clients that are using that SQL query
where the offset and limit are involved. So, I cannot create a temp table,
because that means that I'll have to make a temp table for each session...
which is a very bad ideea. Cursors somehow the same. In my application the
Where conditions can be very different for each user(session) apart.
The only solution that I see in the moment is to work at the query, or to
write a more complex where function to limit the results output. So no
replace for Offset/Limit.
Best regards,
Andy.
----- Original Message -----
From: "Greg Stark" <gsstark@mit.edu>
To: <alex@neteconomist.com>
Cc: "Richard Huxton" <dev@archonet.com>; "Andrei Bintintan"
<klodoma@ar-sd.net>; <pgsql-sql@postgresql.org>;
<pgsql-performance@postgresql.org>
Sent: Tuesday, January 25, 2005 8:28 PM
Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance???
>
> Alex Turner <armtuk@gmail.com> writes:
>
>> I am also very interesting in this very question.. Is there any way to
>> declare a persistant cursor that remains open between pg sessions?
>> This would be better than a temp table because you would not have to
>> do the initial select and insert into a fresh table and incur those IO
>> costs, which are often very heavy, and the reason why one would want
>> to use a cursor.
>
> TANSTAAFL. How would such a persistent cursor be implemented if not by
> building a temporary table somewhere behind the scenes?
>
> There could be some advantage if the data were stored in a temporary table
> marked as not having to be WAL logged. Instead it could be automatically
> cleared on every database start.
>
> --
> greg
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 15:20 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 16:39 ` Re: [SQL] OFFSET impact on Performance??? Alex Turner <armtuk@gmail.com>
2005-01-25 18:28 ` Re: [SQL] OFFSET impact on Performance??? Greg Stark <gsstark@mit.edu>
2005-01-26 10:11 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
@ 2005-01-26 13:47 ` Alex Turner <armtuk@gmail.com>
2005-01-26 13:57 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-02-01 09:16 ` Re: [SQL] OFFSET impact on Performance??? PFC <lists@boutiquenumerique.com>
0 siblings, 2 replies; 22+ messages in thread
From: Alex Turner @ 2005-01-26 13:47 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; +Cc: Greg Stark <gsstark@mit.edu>; Richard Huxton <dev@archonet.com>; pgsql-sql; pgsql-performance@postgresql.org
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
On Wed, 26 Jan 2005 12:11:49 +0200, Andrei Bintintan <klodoma@ar-sd.net> wrote:
> The problems still stays open.
>
> The thing is that I have about 20 - 30 clients that are using that SQL query
> where the offset and limit are involved. So, I cannot create a temp table,
> because that means that I'll have to make a temp table for each session...
> which is a very bad ideea. Cursors somehow the same. In my application the
> Where conditions can be very different for each user(session) apart.
>
> The only solution that I see in the moment is to work at the query, or to
> write a more complex where function to limit the results output. So no
> replace for Offset/Limit.
>
> Best regards,
> Andy.
>
>
> ----- Original Message -----
> From: "Greg Stark" <gsstark@mit.edu>
> To: <alex@neteconomist.com>
> Cc: "Richard Huxton" <dev@archonet.com>; "Andrei Bintintan"
> <klodoma@ar-sd.net>; <pgsql-sql@postgresql.org>;
> <pgsql-performance@postgresql.org>
> Sent: Tuesday, January 25, 2005 8:28 PM
> Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance???
>
>
> >
> > Alex Turner <armtuk@gmail.com> writes:
> >
> >> I am also very interesting in this very question.. Is there any way to
> >> declare a persistant cursor that remains open between pg sessions?
> >> This would be better than a temp table because you would not have to
> >> do the initial select and insert into a fresh table and incur those IO
> >> costs, which are often very heavy, and the reason why one would want
> >> to use a cursor.
> >
> > TANSTAAFL. How would such a persistent cursor be implemented if not by
> > building a temporary table somewhere behind the scenes?
> >
> > There could be some advantage if the data were stored in a temporary table
> > marked as not having to be WAL logged. Instead it could be automatically
> > cleared on every database start.
> >
> > --
> > greg
> >
> >
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 15:20 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 16:39 ` Re: [SQL] OFFSET impact on Performance??? Alex Turner <armtuk@gmail.com>
2005-01-25 18:28 ` Re: [SQL] OFFSET impact on Performance??? Greg Stark <gsstark@mit.edu>
2005-01-26 10:11 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-26 13:47 ` Re: [SQL] OFFSET impact on Performance??? Alex Turner <armtuk@gmail.com>
@ 2005-01-26 13:57 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 22+ messages in thread
From: Richard Huxton @ 2005-01-26 13:57 UTC (permalink / raw)
To: alex@neteconomist.com; +Cc: Andrei Bintintan <klodoma@ar-sd.net>; Greg Stark <gsstark@mit.edu>; pgsql-sql; pgsql-performance@postgresql.org
Alex Turner wrote:
> 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.
It's less the RDBMS than the web application. You're trying to mix a
stateful setup (the application) with a stateless presentation layer
(the web). If you're using PHP (which doesn't offer a "real" middle
layer) you might want to look at memcached.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 15:20 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 16:39 ` Re: [SQL] OFFSET impact on Performance??? Alex Turner <armtuk@gmail.com>
2005-01-25 18:28 ` Re: [SQL] OFFSET impact on Performance??? Greg Stark <gsstark@mit.edu>
2005-01-26 10:11 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-26 13:47 ` Re: [SQL] OFFSET impact on Performance??? Alex Turner <armtuk@gmail.com>
@ 2005-02-01 09:16 ` PFC <lists@boutiquenumerique.com>
1 sibling, 0 replies; 22+ messages in thread
From: PFC @ 2005-02-01 09:16 UTC (permalink / raw)
To: alex@neteconomist.com, "Andrei Bintintan" <klodoma@ar-sd.net>; +Cc: pgsql-performance@postgresql.org
> 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 !
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 15:20 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
@ 2005-01-20 16:49 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2005-01-20 17:04 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 22+ messages in thread
From: Ron Mayer @ 2005-01-20 16:49 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>
Richard Huxton wrote:
>
> If you've got a web-application then you'll probably want to insert the
> results into a cache table for later use.
>
If I have quite a bit of activity like this (people selecting 10000 out
of a few million rows and paging through them in a web browser), would
it be good to have a single table with a userid column shared by all
users, or a separate table for each user that can be truncated/dropped?
I started out with one table; but with people doing 10s of thousand
of inserts and deletes per session, I had a pretty hard time figuring
out a reasonable vacuum strategy.
Eventually I started doing a whole bunch of create table tmp_XXXX
tables where XXXX is a userid; and a script to drop these tables - but
that's quite ugly in a different way.
With 8.0 I guess I'll try the single table again - perhaps what I
want may be to always have a I/O throttled vacuum running... hmm.
Any suggestions?
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 15:20 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 16:49 ` Re: [SQL] OFFSET impact on Performance??? Ron Mayer <rm_pg@cheapcomplexdevices.com>
@ 2005-01-20 17:04 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 22+ messages in thread
From: Richard Huxton @ 2005-01-20 17:04 UTC (permalink / raw)
To: Ron Mayer <rm_pg@cheapcomplexdevices.com>; +Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Ron Mayer wrote:
> Richard Huxton wrote:
>
>>
>> If you've got a web-application then you'll probably want to insert
>> the results into a cache table for later use.
>>
>
> If I have quite a bit of activity like this (people selecting 10000 out
> of a few million rows and paging through them in a web browser), would
> it be good to have a single table with a userid column shared by all
> users, or a separate table for each user that can be truncated/dropped?
>
> I started out with one table; but with people doing 10s of thousand
> of inserts and deletes per session, I had a pretty hard time figuring
> out a reasonable vacuum strategy.
As often as you can, and make sure your config allocates enough
free-space-map for them. Unless, of course, you end up I/O saturated.
> Eventually I started doing a whole bunch of create table tmp_XXXX
> tables where XXXX is a userid; and a script to drop these tables - but
> that's quite ugly in a different way.
>
> With 8.0 I guess I'll try the single table again - perhaps what I
> want may be to always have a I/O throttled vacuum running... hmm.
Well, there have been some tweaks, but I don't know if they'll help in
this case.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
@ 2005-01-20 16:59 ` Greg Stark <gsstark@mit.edu>
2005-01-20 17:24 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 19:12 ` Re: [SQL] OFFSET impact on Performance??? Ragnar Hafstað <gnari@simnet.is>
1 sibling, 2 replies; 22+ messages in thread
From: Greg Stark @ 2005-01-20 16:59 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql; pgsql-performance@postgresql.org
"Andrei Bintintan" <klodoma@ar-sd.net> writes:
> > If you're using this to provide "pages" of results, could you use a cursor?
> What do you mean by that? Cursor?
>
> Yes I'm using this to provide "pages", but If I jump to the last pages it goes
> very slow.
The best way to do pages for is not to use offset or cursors but to use an
index. This only works if you can enumerate all the sort orders the
application might be using and can have an index on each of them.
To do this the query would look something like:
SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50
Then you take note of the last value used on a given page and if the user
selects "next" you pass that as the starting point for the next page.
This query takes the same amount of time no matter how many records are in the
table and no matter what page of the result set the user is on. It should
actually be instantaneous even if the user is on the hundredth page of
millions of records because it uses an index both for the finding the right
point to start and for the ordering.
It also has the advantage that it works even if the list of items changes as
the user navigates. If you use OFFSET and someone inserts a record in the
table then the "next" page will overlap the current page. Worse, if someone
deletes a record then "next" will skip a record.
The disadvantages of this are a) it's hard (but not impossible) to go
backwards. And b) it's impossible to give the user a list of pages and let
them skip around willy nilly.
(If this is for a web page then specifically don't recommend cursors. It will
mean you'll have to have some complex session management system that
guarantees the user will always come to the same postgres session and has some
garbage collection if the user disappears. And it means the URL is only good
for a limited amount of time. If they bookmark it it'll break if they come
back the next day.)
--
greg
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 16:59 ` Re: [SQL] OFFSET impact on Performance??? Greg Stark <gsstark@mit.edu>
@ 2005-01-20 17:24 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 22+ messages in thread
From: Richard Huxton @ 2005-01-20 17:24 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Andrei Bintintan <klodoma@ar-sd.net>; pgsql-sql; pgsql-performance@postgresql.org
Greg Stark wrote:
> "Andrei Bintintan" <klodoma@ar-sd.net> writes:
>
>
>>>If you're using this to provide "pages" of results, could you use a cursor?
>>
>>What do you mean by that? Cursor?
>>
>>Yes I'm using this to provide "pages", but If I jump to the last pages it goes
>>very slow.
>
>
> The best way to do pages for is not to use offset or cursors but to use an
> index. This only works if you can enumerate all the sort orders the
> application might be using and can have an index on each of them.
>
> To do this the query would look something like:
>
> SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50
>
> Then you take note of the last value used on a given page and if the user
> selects "next" you pass that as the starting point for the next page.
Greg's is the most efficient, but you need to make sure you have a
suitable key available in the output of your select.
Also, since you are repeating the query you could get different results
as people insert/delete rows. This might or might not be what you want.
A similar solution is to partition by date/alphabet or similar, then
page those results. That can reduce your resultset to a manageable size.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 16:59 ` Re: [SQL] OFFSET impact on Performance??? Greg Stark <gsstark@mit.edu>
@ 2005-01-20 19:12 ` Ragnar Hafstað <gnari@simnet.is>
2005-01-20 19:23 ` Re: [SQL] OFFSET impact on Performance??? Ragnar Hafstað <gnari@simnet.is>
1 sibling, 1 reply; 22+ messages in thread
From: Ragnar Hafstað @ 2005-01-20 19:12 UTC (permalink / raw)
To: pgsql-performance@postgresql.org; +Cc: Andrei Bintintan <klodoma@ar-sd.net>; pgsql-sql
On Thu, 2005-01-20 at 11:59 -0500, Greg Stark wrote:
> The best way to do pages for is not to use offset or cursors but to use an
> index. This only works if you can enumerate all the sort orders the
> application might be using and can have an index on each of them.
>
> To do this the query would look something like:
>
> SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50
>
> Then you take note of the last value used on a given page and if the user
> selects "next" you pass that as the starting point for the next page.
this will only work unchanged if the index is unique. imagine , for
example if you have more than 50 rows with the same value of col.
one way to fix this is to use ORDER BY col,oid
gnari
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 16:59 ` Re: [SQL] OFFSET impact on Performance??? Greg Stark <gsstark@mit.edu>
2005-01-20 19:12 ` Re: [SQL] OFFSET impact on Performance??? Ragnar Hafstað <gnari@simnet.is>
@ 2005-01-20 19:23 ` Ragnar Hafstað <gnari@simnet.is>
2005-01-21 09:20 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
0 siblings, 1 reply; 22+ messages in thread
From: Ragnar Hafstað @ 2005-01-20 19:23 UTC (permalink / raw)
To: pgsql-performance@postgresql.org; +Cc: Andrei Bintintan <klodoma@ar-sd.net>; pgsql-sql
On Thu, 2005-01-20 at 19:12 +0000, Ragnar Hafstað wrote:
> On Thu, 2005-01-20 at 11:59 -0500, Greg Stark wrote:
>
> > The best way to do pages for is not to use offset or cursors but to use an
> > index. This only works if you can enumerate all the sort orders the
> > application might be using and can have an index on each of them.
> >
> > To do this the query would look something like:
> >
> > SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50
> >
> > Then you take note of the last value used on a given page and if the user
> > selects "next" you pass that as the starting point for the next page.
>
> this will only work unchanged if the index is unique. imagine , for
> example if you have more than 50 rows with the same value of col.
>
> one way to fix this is to use ORDER BY col,oid
and a slightly more complex WHERE clause as well, of course
gnari
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Re: [SQL] OFFSET impact on Performance??? Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Re: [SQL] OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 16:59 ` Re: [SQL] OFFSET impact on Performance??? Greg Stark <gsstark@mit.edu>
2005-01-20 19:12 ` Re: [SQL] OFFSET impact on Performance??? Ragnar Hafstað <gnari@simnet.is>
2005-01-20 19:23 ` Re: [SQL] OFFSET impact on Performance??? Ragnar Hafstað <gnari@simnet.is>
@ 2005-01-21 09:20 ` Andrei Bintintan <klodoma@ar-sd.net>
0 siblings, 0 replies; 22+ messages in thread
From: Andrei Bintintan @ 2005-01-21 09:20 UTC (permalink / raw)
To: Ragnar Hafstað <gnari@simnet.is>; pgsql-performance@postgresql.org; +Cc: pgsql-sql
Now I read all the posts and I have some answers.
Yes, I have a web aplication.
I HAVE to know exactly how many pages I have and I have to allow the user to
jump to a specific page(this is where I used limit and offset). We have this
feature and I cannot take it out.
>> > SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50
Now this solution looks very fast, but I cannot implement it, because I
cannot jump from page 1 to page xxxx only to page 2. Because I know with
this type where did the page 1 ended. And we have some really complicated
where's and about 10 tables are involved in the sql query.
About the CURSOR I have to read more about them because this is my first
time when I hear about.
I don't know if temporary tables are a solution, really I don't think so,
there are a lot of users that are working in the same time at the same page.
So... still DIGGING for solutions.
Andy.
----- Original Message -----
From: "Ragnar Hafstað" <gnari@simnet.is>
To: <pgsql-performance@postgresql.org>
Cc: "Andrei Bintintan" <klodoma@ar-sd.net>; <pgsql-sql@postgresql.org>
Sent: Thursday, January 20, 2005 9:23 PM
Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance???
> On Thu, 2005-01-20 at 19:12 +0000, Ragnar Hafstað wrote:
>> On Thu, 2005-01-20 at 11:59 -0500, Greg Stark wrote:
>>
>> > The best way to do pages for is not to use offset or cursors but to use
>> > an
>> > index. This only works if you can enumerate all the sort orders the
>> > application might be using and can have an index on each of them.
>> >
>> > To do this the query would look something like:
>> >
>> > SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50
>> >
>> > Then you take note of the last value used on a given page and if the
>> > user
>> > selects "next" you pass that as the starting point for the next page.
>>
>> this will only work unchanged if the index is unique. imagine , for
>> example if you have more than 50 rows with the same value of col.
>>
>> one way to fix this is to use ORDER BY col,oid
>
> and a slightly more complex WHERE clause as well, of course
>
> gnari
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: OFFSET impact on Performance???
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
@ 2005-01-27 03:50 ` David Brown <time@bigpond.net.au>
1 sibling, 0 replies; 22+ messages in thread
From: David Brown @ 2005-01-27 03:50 UTC (permalink / raw)
To: Andrei Bintintan <pgsql-performance@postgresql.org>
Although larger offsets have some effect, your real problem is the sort
(of 42693 rows).
Try:
SELECT r.id_order
FROM report r
WHERE r.id_order IN
(SELECT id
FROM orders
WHERE id_status = 6
ORDER BY 1
LIMIT 10 OFFSET 1000)
ORDER BY 1
The subquery doesn't *have* to sort because the table is already ordered
on the primary key.
You can still add a join to orders outside the subselect without
significant cost.
Incidentally, I don't know how you got the first plan - it should
include a sort as well.
Andrei Bintintan wrote:
> explain analyze
> SELECT o.id
> FROM report r
> INNER JOIN orders o ON o.id=r.id_order AND o.id_status=6
> ORDER BY 1 LIMIT 10 OFFSET 10
>
> Limit (cost=44.37..88.75 rows=10 width=4) (actual time=0.160..0.275
rows=10 loops=1)
> -> Merge Join (cost=0.00..182150.17 rows=41049 width=4) (actual
time=0.041..0.260 rows=20 loops=1)
> Merge Cond: ("outer".id_order = "inner".id)
> -> Index Scan using report_id_order_idx on report r
(cost=0.00..157550.90 rows=42862 width=4) (actual time=0.018..0.075
rows=20 loops=1)
> -> Index Scan using orders_pkey on orders o
(cost=0.00..24127.04 rows=42501 width=4) (actual time=0.013..0.078
rows=20 loops=1)
> Filter: (id_status = 6)
> Total runtime: 0.373 ms
>
> explain analyze
> SELECT o.id
> FROM report r
> INNER JOIN orders o ON o.id=r.id_order AND o.id_status=6
> ORDER BY 1 LIMIT 10 OFFSET 1000000
> Limit (cost=31216.85..31216.85 rows=1 width=4) (actual
time=1168.152..1168.152 rows=0 loops=1)
> -> Sort (cost=31114.23..31216.85 rows=41049 width=4) (actual
time=1121.769..1152.246 rows=42693 loops=1)
> Sort Key: o.id
> -> Hash Join (cost=2329.99..27684.03 rows=41049 width=4)
(actual time=441.879..925.498 rows=42693 loops=1)
> Hash Cond: ("outer".id_order = "inner".id)
> -> Seq Scan on report r (cost=0.00..23860.62
rows=42862 width=4) (actual time=38.634..366.035 rows=42864 loops=1)
> -> Hash (cost=2077.74..2077.74 rows=42501 width=4)
(actual time=140.200..140.200 rows=0 loops=1)
> -> Seq Scan on orders o (cost=0.00..2077.74
rows=42501 width=4) (actual time=0.059..96.890 rows=42693 loops=1)
> Filter: (id_status = 6)
> Total runtime: 1170.586 ms
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
@ 2005-01-20 18:04 Spiegelberg, Greg <gspiegelberg@cranel.com>
0 siblings, 0 replies; 22+ messages in thread
From: Spiegelberg, Greg @ 2005-01-20 18:04 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; Andrei Bintintan <klodoma@ar-sd.net>; +Cc: pgsql-sql; pgsql-performance@postgresql.org
Isn't this a prime example of when to use a servlet or something similar
in function? It will create the cursor, maintain it, and fetch against
it for a particular page.
Greg
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Thursday, January 20, 2005 10:21 AM
To: Andrei Bintintan
Cc: pgsql-sql@postgresql.org; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance???
Andrei Bintintan wrote:
>> If you're using this to provide "pages" of results, could you use a
>> cursor?
>
> What do you mean by that? Cursor?
>
> Yes I'm using this to provide "pages", but If I jump to the last pages
> it goes very slow.
DECLARE mycursor CURSOR FOR SELECT * FROM ...
FETCH FORWARD 10 IN mycursor;
CLOSE mycursor;
Repeated FETCHes would let you step through your results. That won't
work if you have a web-app making repeated connections.
If you've got a web-application then you'll probably want to insert the
results into a cache table for later use.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
@ 2005-01-26 10:36 Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
2005-02-01 11:38 ` Re: [SQL] OFFSET impact on Performance??? Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 1 reply; 22+ messages in thread
From: Leeuw van der, Tim @ 2005-01-26 10:36 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; alex@neteconomist.com; Greg Stark <gsstark@mit.edu>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql; pgsql-performance@postgresql.org
Hi,
What you could do is create a table containing all the fields from your SELECT, plus a per-session unique ID. Then you can store the query results in there, and use SELECT with OFFSET / LIMIT on that table. The WHERE clause for this temp-results table only needs to contain the per-session unique id.
This of course gives you a new problem: cleaning stale data out of the temp-results table. And another new problem is that users will not see new data appear on their screen until somehow the query is re-run (... but that might even be desirable, actually, depending on how your users do their work and what their work is).
And of course better performance cannot be guaranteed until you try it.
Would such a scheme give you any hope of improved performance, or would it be too much of a nightmare?
cheers,
--Tim
-----Original Message-----
From: pgsql-performance-owner@postgresql.org on behalf of Andrei Bintintan
Sent: Wed 1/26/2005 11:11 AM
To: alex@neteconomist.com; Greg Stark
Cc: Richard Huxton; pgsql-sql@postgresql.org; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance???
The problems still stays open.
The thing is that I have about 20 - 30 clients that are using that SQL query
where the offset and limit are involved. So, I cannot create a temp table,
because that means that I'll have to make a temp table for each session...
which is a very bad ideea. Cursors somehow the same. In my application the
Where conditions can be very different for each user(session) apart.
The only solution that I see in the moment is to work at the query, or to
write a more complex where function to limit the results output. So no
replace for Offset/Limit.
Best regards,
Andy.
----- Original Message -----
From: "Greg Stark" <gsstark@mit.edu>
To: <alex@neteconomist.com>
Cc: "Richard Huxton" <dev@archonet.com>; "Andrei Bintintan"
<klodoma@ar-sd.net>; <pgsql-sql@postgresql.org>;
<pgsql-performance@postgresql.org>
Sent: Tuesday, January 25, 2005 8:28 PM
Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance???
>
> Alex Turner <armtuk@gmail.com> writes:
>
>> I am also very interesting in this very question.. Is there any way to
>> declare a persistant cursor that remains open between pg sessions?
>> This would be better than a temp table because you would not have to
>> do the initial select and insert into a fresh table and incur those IO
>> costs, which are often very heavy, and the reason why one would want
>> to use a cursor.
>
> TANSTAAFL. How would such a persistent cursor be implemented if not by
> building a temporary table somewhere behind the scenes?
>
> There could be some advantage if the data were stored in a temporary table
> marked as not having to be WAL logged. Instead it could be automatically
> cleared on every database start.
>
> --
> greg
>
>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] OFFSET impact on Performance???
2005-01-26 10:36 Re: [SQL] OFFSET impact on Performance??? Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
@ 2005-02-01 11:38 ` Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 0 replies; 22+ messages in thread
From: Sean Davis @ 2005-02-01 11:38 UTC (permalink / raw)
To: Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>; +Cc: pgsql-performance@postgresql.org; Greg Stark <gsstark@mit.edu>; Andrei Bintintan <klodoma@ar-sd.net>; alex@neteconomist.com; Richard Huxton <dev@archonet.com>; pgsql-sql
On Jan 26, 2005, at 5:36 AM, Leeuw van der, Tim wrote:
> Hi,
>
> What you could do is create a table containing all the fields from
> your SELECT, plus a per-session unique ID. Then you can store the
> query results in there, and use SELECT with OFFSET / LIMIT on that
> table. The WHERE clause for this temp-results table only needs to
> contain the per-session unique id.
>
This is what I do, but I use two columns for indexing the original
query, a user_id (not session-id) and an index to the "query_id" that
is unique within user. This "query_id" is a foreign key to another
table that describes the query (often just a name). I allow the user
only a fixed number of "stored" queries and recycle after hitting the
maximum. You can timestamp your queries so that when you recycle you
drop the oldest one first. If you don't need multiple stored query
results, then using the user_id is probably adequate (assuming the user
is not logged on in several locations simultaneously).
> This of course gives you a new problem: cleaning stale data out of the
> temp-results table. And another new problem is that users will not see
> new data appear on their screen until somehow the query is re-run (...
> but that might even be desirable, actually, depending on how your
> users do their work and what their work is).
>
See above. The query refresh issue remains.
> And of course better performance cannot be guaranteed until you try it.
>
For the standard operating procedure of perform query===>view results,
I have found this to be a nice system. The user is accustomed to
queries taking a bit of time to perform, but then wants to be able to
manipulate and view data rather quickly; this paradigm is pretty well
served by making a separate table of results, particularly if the
original query is costly.
>
> Would such a scheme give you any hope of improved performance, or
> would it be too much of a nightmare?
>
This question still applies....
Sean
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org on behalf of Andrei
> Bintintan
> Sent: Wed 1/26/2005 11:11 AM
> To: alex@neteconomist.com; Greg Stark
> Cc: Richard Huxton; pgsql-sql@postgresql.org;
> pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance???
>
> The problems still stays open.
>
> The thing is that I have about 20 - 30 clients that are using that SQL
> query
> where the offset and limit are involved. So, I cannot create a temp
> table,
> because that means that I'll have to make a temp table for each
> session...
> which is a very bad ideea. Cursors somehow the same. In my application
> the
> Where conditions can be very different for each user(session) apart.
>
> The only solution that I see in the moment is to work at the query, or
> to
> write a more complex where function to limit the results output. So no
> replace for Offset/Limit.
>
> Best regards,
> Andy.
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2005-02-01 11:38 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-20 11:13 OFFSET impact on Performance??? Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 12:10 ` Richard Huxton <dev@archonet.com>
2005-01-20 13:45 ` Andrei Bintintan <klodoma@ar-sd.net>
2005-01-20 15:20 ` Richard Huxton <dev@archonet.com>
2005-01-20 16:39 ` Alex Turner <armtuk@gmail.com>
2005-01-20 16:53 ` Richard Huxton <dev@archonet.com>
2005-01-25 18:28 ` Greg Stark <gsstark@mit.edu>
2005-01-26 10:11 ` Andrei Bintintan <klodoma@ar-sd.net>
2005-01-26 13:47 ` Alex Turner <armtuk@gmail.com>
2005-01-26 13:57 ` Richard Huxton <dev@archonet.com>
2005-02-01 09:16 ` PFC <lists@boutiquenumerique.com>
2005-01-20 16:49 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2005-01-20 17:04 ` Richard Huxton <dev@archonet.com>
2005-01-20 16:59 ` Greg Stark <gsstark@mit.edu>
2005-01-20 17:24 ` Richard Huxton <dev@archonet.com>
2005-01-20 19:12 ` Ragnar Hafstað <gnari@simnet.is>
2005-01-20 19:23 ` Ragnar Hafstað <gnari@simnet.is>
2005-01-21 09:20 ` Andrei Bintintan <klodoma@ar-sd.net>
2005-01-27 03:50 ` David Brown <time@bigpond.net.au>
2005-01-20 18:04 Re: [SQL] OFFSET impact on Performance??? Spiegelberg, Greg <gspiegelberg@cranel.com>
2005-01-26 10:36 Re: [SQL] OFFSET impact on Performance??? Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
2005-02-01 11:38 ` Sean Davis <sdavis2@mail.nih.gov>
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