Re: Really really slow select count(*)

On Fri, Feb 4, 2011 at 7:34 PM, Shaun Thomas <sthomas(at)peak6(dot)com> wrote:

> Why is it asking for the password over and over again? It shouldn't be
> doing that.
>

because I asked it to: -W
on the production server I need to enter password and I'm testing on dev
first.

I just sudo tried it but still no report

and do you agree that I should turn CLUSTER ON ?
>>
>
> Cluster isn't really something you turn on, but something you do.

djns4=# cluster fastadder_fastadderstatus;
ERROR: there is no previously clustered index for table
"fastadder_fastadderstatus"

http://www.postgresonline.com/journal/archives/10-How-does-CLUSTER-ON-improve-index-performance.html

djns4=# alter table fastadder_fastadderstatus CLUSTER ON
fastadder_fastadderstatus_pkey; ALTER TABLE djns4=# CLUSTER
fastadder_fastadderstatus; CLUSTER

ok, that's why I figured I was turning something on. the table has been
altered.

it will be pk ordered, new entries always at the end and no deletes

but this means I have to manually run cluster from time to time, right ? not
that there will be much or any reordering. or it should be fine going
forward with vacuum and enlarging the free space memory map.

> It's like vacuum full, in that it basically rebuilds the table and all
> indexes from scratch. The major issue you'll run into is that it reorders
> the table by the index you chose, so you'd best select the primary key
> unless you have reasons to use something else. And you have to do it table
> by table, which will really suck since we already know your whole db has
> bloated, not just one or two tables.
>

do we know that ? many of the tables are fairly static.

only this one is seriously borked, and yet other related tables seem to be
fine.

> You're going to be doing some scripting, buddy. :) Well, unless you just do
> a dump/restore and start over with sane postgresql.conf settings.

well who knew the defaults were unsane ? :)

scripting this is trivial, I already have the script

I have made the mistake of doing VACUUM FULL in the past. in fact on this
table, and it had to be killed because it took down my entire website !
that may well be the major borking event. a credit to postgres that the
table still functions if that's the case.

scott marlowe:

begin;
> select * into temporaryholdingtable order by somefield;
> truncate oldtable;
> insert into oldtables select * from temporaryholdingtable;
> commit;

that sounds like a good approach.

gentlemen, 300,000 + thanks for your generous time !
(a small number, I know)

-felix