Skip site navigation (1) Skip section navigation (2)

Re: [SQL] OFFSET impact on Performance???

From: Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>
To: Richard Huxton <dev(at)archonet(dot)com>
Subject: Re: [SQL] OFFSET impact on Performance???
Date: 2005-01-20 16:49:39
Message-ID: 41EFE123.9020209@cheapcomplexdevices.com (view raw or flat)
Thread:
Lists: pgsql-performancepgsql-sql
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?

In response to

Responses

pgsql-performance by date

Next:From: Randolf RichardsonDate: 2005-01-20 16:49:55
Subject: Re: PostgreSQL vs. Oracle vs. Microsoft
Previous:From: Greg StarkDate: 2005-01-20 16:44:20
Subject: Re: PostgreSQL clustering VS MySQL clustering

pgsql-sql by date

Next:From: Richard HuxtonDate: 2005-01-20 16:53:14
Subject: Re: [SQL] OFFSET impact on Performance???
Previous:From: Michael FuhrDate: 2005-01-20 16:48:30
Subject: Re: OID's

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group