RE: Select count(*) takes a long time - Mailing list pgsql-interfaces

From Jeff Johnson
Subject RE: Select count(*) takes a long time
Date
Msg-id B9C9130B5D27D4119D5D00A0C9D3A987109586@SERVER
Whole thread Raw
In response to Select count(*) takes a long time  ("Jeff Johnson" <jeff@jeffjohnson.net>)
List 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




pgsql-interfaces by date:

Previous
From: Tom Lane
Date:
Subject: Re: Select count(*) takes a long time
Next
From: Gerd Scheidhauer
Date:
Subject: pgtcl and umlaut