Re: Paged Query

Hi Andrew,

Sure... We are sending data in Json to clients

{
total:6784,
data:[50 rows for desired page]
}

SELECT count(*) FROM table - without where, without joins used to have
bad performance... However, in real scenario we have never had the case
without joins and where... Join columns are always indexed, and we
always try to put indexes on columns what will mostly be used in where
in usual queries...

So far we haven't met performance problems...

But to be honest with you, total info very rarely in our responses is
bigger then 10k, and mainly is less then 1k... what is really small
number todays.. (even tables have few million rows, but restrictions
always reduce "desired" total data on less then 1000...)

When users want to work on something on every day basis... Usually they
want "immediatly", things, what are just for them...draft things on
what they worked in last few days, or assigned just to them etc etc...

When they need to pass trough some process once a month... And
performance is "slow" - usually they don't bother... Every day tasks is
what is important and what we care about to have good performance...

In very rarely cases, when we know, performance must be slow from many
reasons - we are lying :) - return first page, (hopefully with data
what user looking for), and return 1000 as total... Return result to
user, and async run CalculateTotalForThisCaseAndCache it... On first
next request for the same thing (but other page) if calculation is
done, return results from cache (with real total number)... But it is
really on very exceptional basis then on regular...

Cheers

Misa

Sent from my Windows Phone
From: Andrew Dunstan
Sent: 09/07/2012 19:47
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Paged Query

On 07/09/2012 01:41 PM, Misa Simic wrote:
>
>
> From my experience users even very rare go to ending pages... easier
> to them would be to sort data by field to get those rows in very first
> pages...
>
>

Yeah, the problem really is that most client code wants to know how many
pages there are, even if it only wants one page right now.

cheers

andrew

On 07/10/2012 06:24 AM, Misa Simic wrote:
> Hi Andrew,
>
> Sure... We are sending data in Json to clients
>
> {
> total:6784,
> data:[50 rows for desired page]
> }
>
> SELECT count(*) FROM table - without where, without joins used to have
> bad performance... However, in real scenario we have never had the case
> without joins and where... Join columns are always indexed, and we
> always try to put indexes on columns what will mostly be used in where
> in usual queries...

When/if you do need a count of a single table without any filters, a
common trick is to use table statistics to return an approximation. If
your autovaccum is running regularly it's usually a very good
approximation, too.

Sounds like this hack may become unnecessary in 9.2 though.

--
Craig Ringer

On Mon, Jul 9, 2012 at 4:50 PM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:
>
>
> When/if you do need a count of a single table without any filters, a common
> trick is to use table statistics to return an approximation. If your
> autovaccum is running regularly it's usually a very good approximation, too.
>
> Sounds like this hack may become unnecessary in 9.2 though.

Index only scans in 9.2 are nice, but they don't fundamentally change
this type of thing.

Cheers,

Jeff