Re: Really really slow select count(*)

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

 



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