From: Shaun Thomas <sthomas@peak6.com>
To: felix <crucialfelix@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Really really slow select count(*)
Date: Fri, 4 Feb 2011 09:00:51 -0600
Message-ID: <4D4C14A3.9000409@peak6.com> (raw)
In-Reply-To: <AANLkTinAJcm5A+i1O_R87aiYyu8N2OWgoaL9RfR=tUR9@mail.gmail.com>
References: <AANLkTinAJcm5A+i1O_R87aiYyu8N2OWgoaL9RfR=tUR9@mail.gmail.com>
On 02/04/2011 08:46 AM, felix wrote:
> explain analyze select count(*) from fastadder_fastadderstatus;
>
> Aggregate (cost=62458.73..62458.74 rows=1 width=0) (actual
> time=77130.000..77130.000 rows=1 loops=1)
> -> Seq Scan on fastadder_fastadderstatus (cost=0.00..61701.18
> rows=303018 width=0) (actual time=50.000..76930.000 rows=302479 loops=1)
> Total runtime: *77250.000 ms*
How big is this table when it's acting all bloated and ugly?
SELECT relpages*8/1024 FROM pg_class
WHERE relname='fastadder_fastadderstatus';
That's the number of MB it's taking up that would immediately affect a
count statement.
> directly after REINDEX and ANALYZE:
>
> Aggregate (cost=62348.70..62348.71 rows=1 width=0) (actual
> time=15830.000..15830.000 rows=1 loops=1)
> -> Seq Scan on fastadder_fastadderstatus (cost=0.00..61613.16
> rows=294216 width=0) (actual time=30.000..15570.000 rows=302479 loops=1)
> Total runtime: 15830.000 ms
That probably put it into cache, explaining the difference, but yeah...
that is pretty darn slow. Is this the only thing running when you're
doing your tests? What does your disk IO look like?
> 10k-50k updates per day
> mostly of this sort: set priority=1 where id=12345
Well... that's up to 16% turnover per day, but even then, regular
vacuuming should keep it manageable.
> I could rework the app to be more efficient and do updates using batches
> where id IN (1,2,3,4...)
No. Don't do that. You'd be better off loading everything into a temp
table and doing this:
UPDATE fastadder_fastadderstatus s
SET priority = 1
FROM temp_statuses t
WHERE t.id=s.id;
It's a better practice, but still doesn't really explain your
performance issues.
> "fastadder_fastadderstatus_pkey" PRIMARY KEY, btree (id)
> "fastadder_fastadderstatus_apt_id_key" UNIQUE, btree (apt_id, service_id)
> "fastadder_fastadderstatus_agent_priority" btree (agent_priority)
> "fastadder_fastadderstatus_apt_id" btree (apt_id)
> "fastadder_fastadderstatus_built" btree (built)
> "fastadder_fastadderstatus_last_checked" btree (last_checked)
> "fastadder_fastadderstatus_last_validated" btree (last_validated)
> "fastadder_fastadderstatus_position_in_queue" btree (position_in_queue)
> "fastadder_fastadderstatus_priority" btree (priority)
> "fastadder_fastadderstatus_running_status" btree (running_status)
> "fastadder_fastadderstatus_service_id" btree (service_id)
Whoh! Hold on, here. That looks like *way* too many indexes. Definitely
will slow down your insert/update performance. The index on 'built' for
example, is a boolean. If it's evenly distributed, that's 150k matches
for true or false, rendering it useless, yet still requiring space and
maintenance. I'm guessing the story is similar for quite a few of the
others.
It doesn't really explain your count speed, but it certainly isn't helping.
Something seems fishy, here.
--
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, crucialfelix@gmail.com
Subject: Re: Really really slow select count(*)
In-Reply-To: <4D4C14A3.9000409@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