Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
	"Neil Whelchel" <neil.whelchel@gmail.com>
Subject: Re: Slow count(*) again...
References: <201010091626.18636.neil.whelchel@gmail.com>
	<201010111936.46207.neil.whelchel@gmail.com>
	<op.vkhfkn1yeorkce@apollo13>
	<201010121619.34839.neil.whelchel@gmail.com>
Date: Wed, 13 Oct 2010 09:46:25 +0200
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.vkh7vno8eorkce@apollo13>
In-Reply-To: <201010121619.34839.neil.whelchel@gmail.com>
User-Agent: Opera Mail/10.62 (Linux)


> I guess I have to comment here again and point out that while I am  
> having this
> issue with text searches, I avoid using count(*) in such cases, I just  
> use
> next and previous links.

Unfortunately sometimes you got to do an ORDER BY on search results, and  
then all the rows got to be read...

> Where the real problem (for me) is that when someone
> searches a date or time range. My application keeps track of huge

Have you tried CLUSTER ?

Also, it is sad to say, but if you need an engine able to use index-only  
scans which would fit this type of query, replicate the table to MyISAM.  
Unfortunately, the MySQL optimizer is really not so smart about complex  
reporting queries (no hash joins, no hash aggregates) so if you don't have  
a multicolumn index covering that you can use for index-only scan in your  
query, you'll get either a really huge sort or a really nasty nested loop  
index scan...