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

RE: Select count(*) takes a long time

From: "Jeff Johnson" <jeff(at)jeffjohnson(dot)net>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-interfaces(at)postgresql(dot)org>
Subject: RE: Select count(*) takes a long time
Date: 2001-08-09 16:52:20
Message-ID: B9C9130B5D27D4119D5D00A0C9D3A987109586@SERVER (view raw or flat)
Thread:
Lists: pgsql-interfaces
> Doesn't seem like count(*) is an essential component of a solution
to
> this problem.  What are the available article identifiers and
indexes?
> For example, if you had a timestamp column with an index, you could
> define "a random article" as "the first one after a randomly chosen
> time", which could be retrieved efficiently with
>
> select * from articles where timestamp >= 'targeted time' limit 1;
>
> The target time could be chosen as some random fraction between the
> start of your database and now().  This'd be skewed by variations in
> the rate of article posting, but it'd probably do for your purposes.
>
> If there is a serial number column then it's even easier, since the
> range of article numbers is from 1 to the sequence's last_value.

I think that might work well for me, thanks!

> > I thought I came up with a good solution, now I "select ... from
...
> > order by random() limit 1", which is nice because it only
> requires one
> > query to get what I want but it's still slow.
>
> That is most definitely *not* going to be fast, since it requires an
> explicit sort of all the rows.

Good point :)

Thanks,
Jeff



Responses

pgsql-interfaces by date

Next:From: Rene PijlmanDate: 2001-08-09 20:05:50
Subject: Re: Select count(*) takes a long time
Previous:From: Tom LaneDate: 2001-08-09 16:36:15
Subject: Re: Select count(*) takes a long time

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