Re: Really really slow select count(*)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx

______________________________________________

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

--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux