From: Neil Whelchel <neil.whelchel@gmail.com>
Organization: Go-Automation
To: pgsql-performance@postgresql.org
Subject: Re: Slow count(*) again...
Date: Sat, 9 Oct 2010 20:02:12 -0700
User-Agent: KMail/1.13.5 (Linux/2.6.35.5; KDE/4.5.1; i686; ; )
References: <201010091626.18636.neil.whelchel@gmail.com>
	<AANLkTimayTM__hHu9RoiLaNhzfpNY6Vo5y4OkbX_sV=o@mail.gmail.com>
In-Reply-To: <AANLkTimayTM__hHu9RoiLaNhzfpNY6Vo5y4OkbX_sV=o@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201010092002.13537.neil.whelchel@gmail.com>

On Saturday 09 October 2010 18:47:34 Scott Marlowe wrote:
> On Sat, Oct 9, 2010 at 5:26 PM, Neil Whelchel <neil.whelchel@gmail.com>=20
wrote:
> > I know that there haven been many discussions on the slowness of count(=
*)
> > even when an index is involved because the visibility of the rows has to
> > be checked. In the past I have seen many suggestions about using
> > triggers and tables to keep track of counts and while this works fine in
> > a situation where you know what the report is going to be ahead of time,
> > this is simply not an option when an unknown WHERE clause is to be used
> > (dynamically generated). I ran into a fine example of this when I was
> > searching this mailing list, "Searching in 856,646 pages took 13.48202
> > seconds. Site search powered by PostgreSQL 8.3." Obviously at some point
> > count(*) came into play here because the site made a list of pages (1 2
> > 3 4 5 6 > next). I very commonly make a list of pages from search
> > results, and the biggest time killer here is the count(*) portion, even
> > worse yet, I sometimes have to hit the database with two SELECT
> > statements, one with OFFSET and LIMIT to get the page of results I need
> > and another to get the amount of total rows so I can estimate how many
> > pages of results are available. The point I am driving at here is that
> > since building a list of pages of results is such a common thing to do,
> > there need to be some specific high speed ways to do this in one query.
> > Maybe an estimate(*) that works like count but gives an answer from the
> > index without checking visibility? I am sure that this would be good
> > enough to make a page list, it is really no big deal if it errors on the
> > positive side, maybe the list of pages has an extra page off the end. I
> > can live with that. What I can't live with is taking 13 seconds to get a
> > page of results from 850,000 rows in a table.
>=20
> 99% of the time in the situations you don't need an exact measure, and
> assuming analyze has run recently, select rel_tuples from pg_class for
> a given table is more than close enough.  I'm sure wrapping that in a
> simple estimated_rows() function would be easy enough to do.

This is a very good approach and it works very well when you are counting t=
he=20
entire table, but when you have no control over the WHERE clause, it doesn'=
t=20
help. IE: someone puts in a word to look for in a web form.

=46rom my perspective, this issue is the biggest problem there is when usin=
g=20
Postgres to create web pages, and it is so commonly used, I think that ther=
e=20
should be a specific way to deal with it so that you don't have to run the=
=20
same WHERE clause twice.=20
IE: SELECT count(*) FROM <table> WHERE <clause>; to get the total amount of=
=20
items to make page navigation links, then:
SELECT <columns> FROM table WHERE <clause> LIMIT <items_per_page> OFFSET=20
<(page_no-1)*items_per_page>; to get the actual page contents.
=20
It's bad enough that count(*) is slow, then you have to do it all over agai=
n=20
to get the results you need! I have not dug into this much yet, but would i=
t=20
be possible to return the amount of rows that a WHERE clause would actually=
=20
return if the LIMIT and OFFSET were not applied. IE: When a normal query is=
=20
executed, the server returns the number of rows aside from the actual row=20
data. Would it be a big deal to modify this to allow it to return the amoun=
t=20
of rows before the LIMIT and OFFSET is applied as well? This would sure cut=
=20
down on time it takes to do the same WHERE clause twice... I have considere=
d=20
using a cursor to do this, however this requires a transfer of all of the r=
ows=20
to the client to get a total count, then setting the cursor to get the rows=
=20
that you are interested in. Or is there a way around this that I am not awa=
re=20
of?
=2DNeil-