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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rene Pijlman | 2001-08-09 20:05:50 | Re: Select count(*) takes a long time |
Previous Message | Tom Lane | 2001-08-09 16:36:15 | Re: Select count(*) takes a long time |