Re: Really really slow select count(*)

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