pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedREINDEX not working for wastedspace
11+ messages / 7 participants
[nested] [flat]
* REINDEX not working for wastedspace
@ 2011-09-21 07:01 AI Rumman <rummandba@gmail.com>
0 siblings, 4 replies; 11+ messages in thread
From: AI Rumman @ 2011-09-21 07:01 UTC (permalink / raw)
To: pgsql-performance
I am using Postgresql 9.0.1.
Using the query http://wiki.postgresql.org/wiki/Show_database_bloat, I got
the following result for a table:
-[ RECORD 1 ]----+-----------------------------------------------
current_database | crm
schemaname | public
tablename | _attachments
tbloat | 0.9
wastedbytes | 0
iname | attachments_description_type_attachmentsid_idx
ibloat | 2.3
wastedibytes | 5439488
-[ RECORD 2 ]----+-----------------------------------------------
current_database | crm
schemaname | public
tablename | _attachments
tbloat | 0.9
wastedbytes | 0
iname | attachments_attachmentsid_idx
ibloat | 0.2
wastedibytes | 0
-[ RECORD 3 ]----+-----------------------------------------------
current_database | crm
schemaname | public
tablename | _attachments
tbloat | 0.9
wastedbytes | 0
iname | _attachments_pkey
ibloat | 0.2
wastedibytes | 0
I REINDEXED both the indexes and table, but I did not find any change in
wastedspace or wastedispace.
Could you please tell me why?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX not working for wastedspace
@ 2011-09-21 07:06 Guillaume Lelarge <guillaume@lelarge.info>
parent: AI Rumman <rummandba@gmail.com>
3 siblings, 1 reply; 11+ messages in thread
From: Guillaume Lelarge @ 2011-09-21 07:06 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-performance
On Wed, 2011-09-21 at 13:01 +0600, AI Rumman wrote:
> I am using Postgresql 9.0.1.
>
> Using the query http://wiki.postgresql.org/wiki/Show_database_bloat, I got
> the following result for a table:
>
> -[ RECORD 1 ]----+-----------------------------------------------
> current_database | crm
> schemaname | public
> tablename | _attachments
> tbloat | 0.9
> wastedbytes | 0
> iname | attachments_description_type_attachmentsid_idx
> ibloat | 2.3
> wastedibytes | 5439488
> -[ RECORD 2 ]----+-----------------------------------------------
> current_database | crm
> schemaname | public
> tablename | _attachments
> tbloat | 0.9
> wastedbytes | 0
> iname | attachments_attachmentsid_idx
> ibloat | 0.2
> wastedibytes | 0
> -[ RECORD 3 ]----+-----------------------------------------------
> current_database | crm
> schemaname | public
> tablename | _attachments
> tbloat | 0.9
> wastedbytes | 0
> iname | _attachments_pkey
> ibloat | 0.2
> wastedibytes | 0
>
> I REINDEXED both the indexes and table, but I did not find any change in
> wastedspace or wastedispace.
> Could you please tell me why?
REINDEX only rebuilds indexes. And you'll obviously have a bit of "lost
space" because of the FILLFACTOR value (90% on indexes IIRC).
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX not working for wastedspace
@ 2011-09-21 07:07 Venkat Balaji <venkat.balaji@verse.in>
parent: AI Rumman <rummandba@gmail.com>
3 siblings, 0 replies; 11+ messages in thread
From: Venkat Balaji @ 2011-09-21 07:07 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-performance
Could you please let us know if you have analyzed after the re-indexing is
done ?
This must show differences for only Indexes not the Tables.
For Tables, you need to do VACUUM FULL to show the difference.
Thanks
Venkat
On Wed, Sep 21, 2011 at 12:31 PM, AI Rumman <rummandba@gmail.com> wrote:
> I am using Postgresql 9.0.1.
>
> Using the query http://wiki.postgresql.org/wiki/Show_database_bloat, I got
> the following result for a table:
>
> -[ RECORD 1 ]----+-----------------------------------------------
> current_database | crm
> schemaname | public
> tablename | _attachments
> tbloat | 0.9
> wastedbytes | 0
> iname | attachments_description_type_attachmentsid_idx
> ibloat | 2.3
> wastedibytes | 5439488
> -[ RECORD 2 ]----+-----------------------------------------------
> current_database | crm
> schemaname | public
> tablename | _attachments
> tbloat | 0.9
> wastedbytes | 0
> iname | attachments_attachmentsid_idx
> ibloat | 0.2
> wastedibytes | 0
> -[ RECORD 3 ]----+-----------------------------------------------
> current_database | crm
> schemaname | public
> tablename | _attachments
> tbloat | 0.9
> wastedbytes | 0
> iname | _attachments_pkey
> ibloat | 0.2
> wastedibytes | 0
>
> I REINDEXED both the indexes and table, but I did not find any change in
> wastedspace or wastedispace.
> Could you please tell me why?
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX not working for wastedspace
@ 2011-09-21 07:10 AI Rumman <rummandba@gmail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 11+ messages in thread
From: AI Rumman @ 2011-09-21 07:10 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: pgsql-performance
Yes I ANALYZE the table, but no change for wastedispace.
On Wed, Sep 21, 2011 at 1:06 PM, Guillaume Lelarge
<guillaume@lelarge.info>wrote:
> On Wed, 2011-09-21 at 13:01 +0600, AI Rumman wrote:
> > I am using Postgresql 9.0.1.
> >
> > Using the query http://wiki.postgresql.org/wiki/Show_database_bloat, I
> got
> > the following result for a table:
> >
> > -[ RECORD 1 ]----+-----------------------------------------------
> > current_database | crm
> > schemaname | public
> > tablename | _attachments
> > tbloat | 0.9
> > wastedbytes | 0
> > iname | attachments_description_type_attachmentsid_idx
> > ibloat | 2.3
> > wastedibytes | 5439488
> > -[ RECORD 2 ]----+-----------------------------------------------
> > current_database | crm
> > schemaname | public
> > tablename | _attachments
> > tbloat | 0.9
> > wastedbytes | 0
> > iname | attachments_attachmentsid_idx
> > ibloat | 0.2
> > wastedibytes | 0
> > -[ RECORD 3 ]----+-----------------------------------------------
> > current_database | crm
> > schemaname | public
> > tablename | _attachments
> > tbloat | 0.9
> > wastedbytes | 0
> > iname | _attachments_pkey
> > ibloat | 0.2
> > wastedibytes | 0
> >
> > I REINDEXED both the indexes and table, but I did not find any change in
> > wastedspace or wastedispace.
> > Could you please tell me why?
>
> REINDEX only rebuilds indexes. And you'll obviously have a bit of "lost
> space" because of the FILLFACTOR value (90% on indexes IIRC).
>
>
> --
> Guillaume
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX not working for wastedspace
@ 2011-09-21 12:21 Reid Thompson <Reid.Thompson@ateb.com>
parent: AI Rumman <rummandba@gmail.com>
3 siblings, 0 replies; 11+ messages in thread
From: Reid Thompson @ 2011-09-21 12:21 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: reid.thompson@ateb.com; pgsql-performance
On Wed, 2011-09-21 at 13:01 +0600, AI Rumman wrote:
> I am using Postgresql 9.0.1.
>
> I REINDEXED both the indexes and table, but I did not find any change
> in wastedspace or wastedispace.
> Could you please tell me why?
you need to
1) either vacuum full or cluster the table
2) analyze the table
3) check bloat again
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX not working for wastedspace
@ 2011-09-21 13:30 Shaun Thomas <sthomas@peak6.com>
parent: AI Rumman <rummandba@gmail.com>
3 siblings, 2 replies; 11+ messages in thread
From: Shaun Thomas @ 2011-09-21 13:30 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-performance
On 09/21/2011 02:01 AM, AI Rumman wrote:
> Using the query http://wiki.postgresql.org/wiki/Show_database_bloat,
> I got the following result for a table:
God. I wish they would erase that Wiki page, or at least add a
disclaimer. That query is no better than a loose estimate. Never, ever,
ever depend on it for critical information about your tables. Ever.
Ever.
With that said, there are a lot of ways which can get the information
you want. One is the pgstattuple contrib, the other is the
pg_freespacemap contrib (though that didn't get really useful until 8.4
and above).
Check out those documentation pages for usage info. More importantly,
ignore the results of that query. It's wrong. If you've just reindexed,
those indexes are about as small as they're ever going to be.
--
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/ for terms and conditions related to this email
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX not working for wastedspace
@ 2011-09-21 14:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Shaun Thomas <sthomas@peak6.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2011-09-21 14:12 UTC (permalink / raw)
To: sthomas@peak6.com; +Cc: AI Rumman <rummandba@gmail.com>; pgsql-performance
Shaun Thomas <sthomas@peak6.com> writes:
> On 09/21/2011 02:01 AM, AI Rumman wrote:
>> Using the query http://wiki.postgresql.org/wiki/Show_database_bloat,
>> I got the following result for a table:
> God. I wish they would erase that Wiki page, or at least add a
> disclaimer. That query is no better than a loose estimate. Never, ever,
> ever depend on it for critical information about your tables. Ever.
The PG wiki is editable by anyone who signs up for an account. Feel
free to put in an appropriate disclaimer, or improve the sample query.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX not working for wastedspace
@ 2011-09-21 14:14 Andy Lester <andy@petdance.com>
parent: Shaun Thomas <sthomas@peak6.com>
1 sibling, 0 replies; 11+ messages in thread
From: Andy Lester @ 2011-09-21 14:14 UTC (permalink / raw)
To: sthomas@peak6.com; +Cc: AI Rumman <rummandba@gmail.com>; pgsql-performance
On Sep 21, 2011, at 8:30 AM, Shaun Thomas wrote:
> I wish they would erase that Wiki page, or at least add a disclaimer.
The "they" that you refer to includes you. It's a wiki. You can write your own disclaimer.
xoa
--
Andy Lester => andy@petdance.com => www.petdance.com => AIM:petdance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX not working for wastedspace
@ 2011-09-21 14:55 Shaun Thomas <sthomas@peak6.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Shaun Thomas @ 2011-09-21 14:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: AI Rumman <rummandba@gmail.com>; pgsql-performance
On 09/21/2011 09:12 AM, Tom Lane wrote:
> The PG wiki is editable by anyone who signs up for an account. Feel
> free to put in an appropriate disclaimer, or improve the sample
> query.
Ah, well then. I do have an account, but thought there were more
granular page restrictions than that. I may have to start wading into
them when I see stuff like this. :)
--
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/ for terms and conditions related to this email
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX not working for wastedspace
@ 2011-09-21 16:20 Venkat Balaji <venkat.balaji@verse.in>
parent: Shaun Thomas <sthomas@peak6.com>
0 siblings, 1 reply; 11+ messages in thread
From: Venkat Balaji @ 2011-09-21 16:20 UTC (permalink / raw)
To: sthomas@peak6.com; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; AI Rumman <rummandba@gmail.com>; pgsql-performance
It is very important to remove it from the WIKI page.
I ran it on production PG9.0 and it does not error out and displays numbered
output.
I noticed that, this works till PG-8.2 (as per the message).
Venkat
On Wed, Sep 21, 2011 at 8:25 PM, Shaun Thomas <sthomas@peak6.com> wrote:
> On 09/21/2011 09:12 AM, Tom Lane wrote:
>
> The PG wiki is editable by anyone who signs up for an account. Feel
>> free to put in an appropriate disclaimer, or improve the sample
>> query.
>>
>
> Ah, well then. I do have an account, but thought there were more granular
> page restrictions than that. I may have to start wading into them when I see
> stuff like this. :)
>
>
> --
> 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/<http://www.peak6.com/email-disclaimer/>for terms and conditions related to this email
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.**
> org <pgsql-performance@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-performance<http://www.postgresql.org/mailpref/pgsql-p...;
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX not working for wastedspace
@ 2011-09-21 16:30 Shaun Thomas <sthomas@peak6.com>
parent: Venkat Balaji <venkat.balaji@verse.in>
0 siblings, 0 replies; 11+ messages in thread
From: Shaun Thomas @ 2011-09-21 16:30 UTC (permalink / raw)
To: Venkat Balaji <venkat.balaji@verse.in>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; AI Rumman <rummandba@gmail.com>; pgsql-performance
On 09/21/2011 11:20 AM, Venkat Balaji wrote:
> It is very important to remove it from the WIKI page.
Removing it is a little premature. :) Definitely going to add a warning
about relying on its output, though. The query itself was created and
integrated into the check_postgres.pl nagios plugin as a very, very
gross estimate of bloated tables.
It wasn't the most accurate thing in the world, but considering what it
had to work with, it did a pretty good job. Generally CLUSTER or VACUUM
FULL would remove a table from the query output, but not always. It's
those edge cases that cause problems.
--
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/ for terms and conditions related to this email
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2011-09-21 16:30 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-21 07:01 REINDEX not working for wastedspace AI Rumman <rummandba@gmail.com>
2011-09-21 07:06 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-09-21 07:10 ` AI Rumman <rummandba@gmail.com>
2011-09-21 07:07 ` Venkat Balaji <venkat.balaji@verse.in>
2011-09-21 12:21 ` Reid Thompson <Reid.Thompson@ateb.com>
2011-09-21 13:30 ` Shaun Thomas <sthomas@peak6.com>
2011-09-21 14:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-09-21 14:55 ` Shaun Thomas <sthomas@peak6.com>
2011-09-21 16:20 ` Venkat Balaji <venkat.balaji@verse.in>
2011-09-21 16:30 ` Shaun Thomas <sthomas@peak6.com>
2011-09-21 14:14 ` Andy Lester <andy@petdance.com>
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