Re: Really really slow select count(*)
От | Shaun Thomas |
---|---|
Тема | Re: Really really slow select count(*) |
Дата | |
Msg-id | 4D4C14A3.9000409@peak6.com обсуждение исходный текст |
Ответ на | Really really slow select count(*) (felix <crucialfelix@gmail.com>) |
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: