From: Pierre C <lists@peufeu.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
To: Neil Whelchel <neil.whelchel@gmail.com>
Subject: Re: Slow count(*) again...
Date: Wed, 13 Oct 2010 09:46:25 +0200
Message-ID: <op.vkh7vno8eorkce@apollo13> (raw)
In-Reply-To: <201010121619.34839.neil.whelchel@gmail.com>
References: <201010091626.18636.neil.whelchel@gmail.com>
<201010111936.46207.neil.whelchel@gmail.com>
<op.vkhfkn1yeorkce@apollo13>
<201010121619.34839.neil.whelchel@gmail.com>
> 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...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, neil.whelchel@gmail.com
Subject: Re: Slow count(*) again...
In-Reply-To: <op.vkh7vno8eorkce@apollo13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox