From: Shaun Thomas <sthomas@peak6.com>
To: felix <crucialfelix@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Really really slow select count(*)
Date: Fri, 4 Feb 2011 13:40:19 -0600
Message-ID: <4D4C5623.3020105@peak6.com> (raw)
In-Reply-To: <AANLkTi=JC_qesUkZNZBRHVRtRZjR3JwSv9L7fLdskU-8@mail.gmail.com>
References: <AANLkTinAJcm5A+i1O_R87aiYyu8N2OWgoaL9RfR=tUR9@mail.gmail.com>
<4D4C138C.7010304@2ndquadrant.com>
<4D4C1555.40209@peak6.com>
<AANLkTikrS5JkJDMjAXpcRWRnuLbX+MHB=CWb_mFa9qb-@mail.gmail.com>
<4D4C2AC3.5020804@peak6.com>
<AANLkTikN75hcCL5Nhgh8Z9c7ZLtduMdjsn07BgO_D_yZ@mail.gmail.com>
<AANLkTikpH2ws_iiyqNE-o-TWB0-5pMbrS0V5SymXBo2X@mail.gmail.com>
<AANLkTi=3n_W-OFVuVHx3Tthn_KiMN42BturJUS5BuWVz@mail.gmail.com>
<4D4C3BCA.5010808@peak6.com>
<AANLkTikjw46g=VD5vbkOfDz93JMX7vBijj7dTEs+LN4U@mail.gmail.com>
<4D4C46C2.1080108@peak6.com>
<AANLkTi=JC_qesUkZNZBRHVRtRZjR3JwSv9L7fLdskU-8@mail.gmail.com>
On 02/04/2011 01:26 PM, felix wrote:
> because I asked it to: -W on the production server I need to enter
> password and I'm testing on dev first.
Right. I'm just surprised it threw up the prompt so many times.
> I just sudo tried it but still no report
Nono... you have to run the vacuum command with the -U for a superuser
in the database. Like the postgres user.
> 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 should be fine going forward. You only need to re-cluster if you want
to force the table to remain in the order you chose, since it doesn't
maintain the order for updates and new inserts. Since you're only doing
it as a cleanup, that's not a concern for you.
> 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.
Probably not in your case. I just mean that any non-static table is
going to have this problem. If you know what those are, great. I don't
usually have that luxury, so I err on the side of assuming the whole DB
is borked. :)
Also, here's a query you may find useful in the future. It reports the
top 20 tables by size, but also reports the row counts and what not.
It's a good way to find possibly bloated tables, or tables you could
archive:
SELECT n.nspname AS schema_name, c.relname AS table_name,
c.reltuples AS row_count,
c.relpages*8/1024 AS mb_used,
pg_total_relation_size(c.oid)/1024/1024 AS total_mb_used
FROM pg_class c
JOIN pg_namespace n ON (n.oid=c.relnamespace)
WHERE c.relkind = 'r'
ORDER BY total_mb_used DESC
LIMIT 20;
The total_mb_used column is the table + all of the indexes and toast
table space. The mb_used is just for the table itself. This will also
help you see index bloat, or if a table has too much toasted data.
> well who knew the defaults were unsane ? :)
Not really "unsane," but for any large database, they're not ideal. This
also goes for the default_statistics_target setting. If you haven't
already, you may want to bump this up to 100 from the default of 10. Not
enough stats can make the planner ignore indexes and other bad things,
and it sounds like your DB is big enough to benefit from that.
Later versions have made 100 the default, so you'd just be catching up. :)
--
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@peak6.com, crucialfelix@gmail.com
Subject: Re: Really really slow select count(*)
In-Reply-To: <4D4C5623.3020105@peak6.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox