Message-ID: <4D4C2AB1.3050401@peak6.com>
Date: Fri, 4 Feb 2011 10:34:57 -0600
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: sthomas@peak6.com
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.7) Gecko/20100720 Lightning/1.0b2 Thunderbird/3.1.1
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Really really slow select count(*)
References: <AANLkTinAJcm5A+i1O_R87aiYyu8N2OWgoaL9RfR=tUR9@mail.gmail.com>
	<4D4C14A3.9000409@peak6.com>
	<AANLkTi=uf7J7=7T7KTx3R9QWJZz2U2SYcCufNssqw6gs@mail.gmail.com>
In-Reply-To: <AANLkTi=uf7J7=7T7KTx3R9QWJZz2U2SYcCufNssqw6gs@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"; format=flowed
content-transfer-encoding: 7bit

On 02/04/2011 10:17 AM, felix wrote:

> > How big is this table when it's acting all bloated and ugly?
>
> 458MB

Wow! There's no way a table with 300k records should be that big unless 
it's just full of text. 70-seconds seems like a really long time to read 
half a gig, but that might be because it's fighting for IO with other 
processes.

For perspective, we have several 1-2 million row tables smaller than 
that. Heck, I have a 11-million row table that's only 30% larger.

> are updates of the where id IN (1,2,3,4) generally not efficient ?
> how about for select queries ?

Well, IN is notorious for being inefficient. It's been getting better, 
but even EXISTS is a better bet than using IN. We've got a lot of stuff 
using IN here, and we're slowly phasing it out. Every time I get rid of 
it, things get faster.

> I actually just added most of those yesterday in an attempt to improve
> performance. priority and agent_priority were missing indexes and that
> was a big mistake.

Haha. Well, that can always be true. Ironically one of the things you 
actually did by creating the indexes is create fast lookup values to 
circumvent your table bloat. It would help with anything except sequence 
scans, which you saw with your count query.

> ok,
> built True is in the minority.

Ok, in that case, use a partial index. If a boolean value is only 1% of 
your table or something, why bother indexing the rest anyway?

CREATE INDEX fastadder_fastadderstatus_built
      ON fastadder_fastadderstatus
   WHERE built;

But only if it really is the vast minority. Check this way:

SELECT built, count(1)
    FROM fastadder_fastadderstatus
   GROUP BY 1;

We used one of these to ignore a status that was over 90% of the table, 
where the other statuses combined were less than 10%. The index was 10x 
smaller and much faster than before.

If you know both booleans are used together often, you can combine them 
into a single index, again using a partial where it only indexes if both 
values are true. Much smaller, much faster index if it's more selective 
than the other indexes.

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email