---------- Forwarded message ----------
From: felix <crucialfelix@xxxxxxxxx>
Date: Fri, Feb 4, 2011 at 5:17 PM
Subject: Re: Really really slow select count(*)
To: sthomas@xxxxxxxxx
From: felix <crucialfelix@xxxxxxxxx>
Date: Fri, Feb 4, 2011 at 5:17 PM
Subject: Re: Really really slow select count(*)
To: sthomas@xxxxxxxxx
On Fri, Feb 4, 2011 at 4:00 PM, Shaun Thomas <sthomas@xxxxxxxxx> wrote:
How big is this table when it's acting all bloated and ugly?
458MB
ÂIs this the only thing running when you're doing your tests? What does your disk IO look like?
this is on a live site. Âbest not to scare the animals.
I have the same config on the dev environment but not the same table size.
Well... that's up to 16% turnover per day, but even then, regular vacuuming should keep it manageable.
10k-50k updates per day
mostly of this sort: Â set priority=1 where id=12345
something is definitely amiss with this table.Â
I'm not sure if its something that happened at one point when killing an task that was writing to it or if its something about the way the app is updating. Âit SHOULDN'T be that much of a problem, though I can find ways to improve it.
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;
ok, that is one the solutions I was thinking about.
are updates of the where id IN (1,2,3,4) generally not efficient ?
how about for select queries ?
Whoh! Hold on, here. That looks like *way* too many indexes."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)
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.
overall performance went way up on my primary selects
Â
Definitely will slow down your insert/update performance.
there are a lot more selects happening throughout the day
Â
The index on 'built' for example, is a boolean. If it's evenly distributed, that's 150k matches for true or false,
ok,
built True is in the minority.
here is the test query that caused me to add indices to the booleans. Âthis is a 30k table which is doing selects on two booleans constantly. Âagain: True is the minority
explain analyze SELECT "nsproperties_apt"."id", "nsproperties_apt"."display_address", "nsproperties_apt"."apt_num", "nsproperties_apt"."bldg_id", "nsproperties_apt"."is_rental", "nsproperties_apt"."is_furnished", "nsproperties_apt"."listing_type", "nsproperties_apt"."list_on_web", "nsproperties_apt"."is_approved", "nsproperties_apt"."status", "nsproperties_apt"."headline", "nsproperties_apt"."slug", "nsproperties_apt"."cross_street", "nsproperties_apt"."show_apt_num", "nsproperties_apt"."show_building_name", "nsproperties_apt"."external_url", "nsproperties_apt"."listed_on", "nsproperties_bldg"."id", "nsproperties_bldg"."name" FROM "nsproperties_apt" LEFT OUTER JOIN "nsproperties_bldg" ON ("nsproperties_apt"."bldg_id" = "nsproperties_bldg"."id") WHERE ("nsproperties_apt"."list_on_web" = True AND "nsproperties_apt"."is_available" = True ) ;
ÂÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â QUERY PLAN Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â ÂÂ
--------------------------------------------------------------------------------------------------------------------------------
ÂHash Left Join Â(cost=408.74..10062.18 rows=3344 width=152) (actual time=12.688..2442.542 rows=2640 loops=1)
ÂÂ Hash Cond: (nsproperties_apt.bldg_id = nsproperties_bldg.id)
ÂÂ -> ÂSeq Scan on nsproperties_apt Â(cost=0.00..9602.52 rows=3344 width=139) (actual time=0.025..2411.644 rows=2640 loops=1)
ÂÂ Â Â Â Filter: (list_on_web AND is_available)
ÂÂ -> ÂHash Â(cost=346.66..346.66 rows=4966 width=13) (actual time=12.646..12.646 rows=4966 loops=1)
ÂÂ Â Â Â -> ÂSeq Scan on nsproperties_bldg Â(cost=0.00..346.66 rows=4966 width=13) (actual time=0.036..8.236 rows=4966 loops=1)
ÂTotal runtime: 2444.067 ms
(7 rows)
=>
ÂHash Left Join Â(cost=1232.45..9784.18 rows=5690 width=173) (actual time=30.000..100.000 rows=5076 loops=1)
ÂÂ Hash Cond: (nsproperties_apt.bldg_id = nsproperties_bldg.id)
ÂÂ -> ÂBitmap Heap Scan on nsproperties_apt Â(cost=618.23..9075.84 rows=5690 width=157) (actual time=10.000..60.000 rows=5076 loops=1)
ÂÂ Â Â Â Filter: (list_on_web AND is_available)
ÂÂ Â Â Â -> ÂBitmapAnd Â(cost=618.23..618.23 rows=5690 width=0) (actual time=10.000..10.000 rows=0 loops=1)
ÂÂ Â Â Â Â Â Â -> ÂBitmap Index Scan on nsproperties_apt_is_available Â(cost=0.00..131.81 rows=6874 width=0) (actual time=0.000..0.000 rows=6545 loops=1)
ÂÂ Â Â Â Â Â Â Â Â Â Index Cond: (is_available = true)
ÂÂ Â Â Â Â Â Â -> ÂBitmap Index Scan on nsproperties_apt_list_on_web Â(cost=0.00..483.32 rows=25476 width=0) (actual time=10.000..10.000 rows=26010 loops=1)
ÂÂ Â Â Â Â Â Â Â Â Â Index Cond: (list_on_web = true)
ÂÂ -> ÂHash Â(cost=537.99..537.99 rows=6099 width=16) (actual time=20.000..20.000 rows=6099 loops=1)
ÂÂ Â Â Â -> ÂSeq Scan on nsproperties_bldg Â(cost=0.00..537.99 rows=6099 width=16) (actual time=0.000..10.000 rows=6099 loops=1)
ÂTotal runtime: 100.000 ms
(12 rows)
Â
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.
it shouldn't affect count speed at all
it will affect the updates of course.
Â
Something seems fishy, here.
indeed
Â
--
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
