From: Shaun Thomas <sthomas@peak6.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Really really slow select count(*)
Date: Fri, 4 Feb 2011 10:34:57 -0600
Message-ID: <4D4C2AB1.3050401@peak6.com> (raw)
In-Reply-To: <AANLkTi=uf7J7=7T7KTx3R9QWJZz2U2SYcCufNssqw6gs@mail.gmail.com>
References: <AANLkTinAJcm5A+i1O_R87aiYyu8N2OWgoaL9RfR=tUR9@mail.gmail.com>
<4D4C14A3.9000409@peak6.com>
<AANLkTi=uf7J7=7T7KTx3R9QWJZz2U2SYcCufNssqw6gs@mail.gmail.com>
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
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: sthomas@peak6.com
Subject: Re: Really really slow select count(*)
In-Reply-To: <4D4C2AB1.3050401@peak6.com>
* 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