Re: Really really slow select count(*)

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