Re: Really really slow select count(*)

On Mon, Feb 7, 2011 at 6:05 AM, Shaun Thomas <sthomas(at)peak6(dot)com> wrote:

>
> That’s one of the things I talked about. To be safe, PG will start to shut
> down but disallow new connections, and **that’s all**. Old connections are
> grandfathered in until they disconnect, and when they all go away, it shuts
> down gracefully.
>

Well.... it said "Failed to shutdown ..............." and then returned
control.
and then proceeded to run for about an hour.

I'm not sure how graceful that is.

I generally take programs at their word. "Failed" is clearly past tense.

So far as your Django install, have you activated the memcache contrib.
> module? Your pages should be lazy-caching and rarely depend on the DB, if
> they can.
>

yes thanks my web app is very finely tuned and is working splendidly.
I've been working on very large sites sites since 1998 and this client has
been with me for 10 years already. its a fairly high traffic site.

I've only been using postgres since we migrated in May

but it is one particular table on postgres that has shit the sock drawer.

> You should also rarely be doing count(*) on a 300k row table, even if
> everything is cached and speedy.
>

I'm not

this is a test query that is obviously way out of bounds for acceptable
response.

there is something very very wrong with this table and I need to solve it
ASAP.
other tables that have less updates but similar sizes are not having this
problem.

there are foreign keys pointing to this table so its a bit tricky to just
refill it, but I can think of one way. I'll have to do that.

its only conjecture that the issue is file space bloat or free map problems.
those are overall issues that I will get to as soon as I can. but this is
table specific.

That’s an application design issue you need to address before it’s too
> late, or you have to rush and implement a hasty fix.
>

it is not an application design issue, though there are always improvements
being made.

Being a DBA sucks sometimes. J
>

I am not a DBA, I'm just trying to query a 300k row table.

though I am happy to learn more. I know an awful lot about a lot of things.
but you can't specialize in everything