pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBig index sizes
4+ messages / 4 participants
[nested] [flat]
* Big index sizes
@ 2008-12-30 09:05 Laszlo Nagy <gandalf@shopzeus.com>
2008-12-30 09:21 ` Re: Big index sizes Guillaume Lelarge <guillaume@lelarge.info>
2008-12-30 14:43 ` Re: Big index sizes Robert Haas <robertmhaas@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Laszlo Nagy @ 2008-12-30 09:05 UTC (permalink / raw)
To: pgsql-performance
We have serveral table where the index size is much bigger than the
table size.
Example:
select count(*) from product_price -- 2234244
Table size: 400 MB
Index size: 600 MB
After executing "reindex table product_price", index size reduced to 269MB.
I believe this affects performance.
Vacuuming a table does not rebuild the indexes, am I right? I'm not sure
if I need to do this manually, or is this the result of another problem?
(For example, too many open transactions, frequent updates?)
Thanks
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Big index sizes
2008-12-30 09:05 Big index sizes Laszlo Nagy <gandalf@shopzeus.com>
@ 2008-12-30 09:21 ` Guillaume Lelarge <guillaume@lelarge.info>
2008-12-30 15:39 ` Re: Big index sizes Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 4+ messages in thread
From: Guillaume Lelarge @ 2008-12-30 09:21 UTC (permalink / raw)
To: Laszlo Nagy <gandalf@shopzeus.com>; +Cc: pgsql-performance
Laszlo Nagy a écrit :
> We have serveral table where the index size is much bigger than the
> table size.
>
> Example:
>
> select count(*) from product_price -- 2234244
>
> Table size: 400 MB
> Index size: 600 MB
>
> After executing "reindex table product_price", index size reduced to 269MB.
>
> I believe this affects performance.
>
> Vacuuming a table does not rebuild the indexes, am I right?
Neither VACUUM nor VACUUM FULL rebuild the indexes. CLUSTER and REINDEX do.
> I'm not sure
> if I need to do this manually, or is this the result of another problem?
You need to do this manually.
> (For example, too many open transactions, frequent updates?)
>
Regards.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Big index sizes
2008-12-30 09:05 Big index sizes Laszlo Nagy <gandalf@shopzeus.com>
2008-12-30 09:21 ` Re: Big index sizes Guillaume Lelarge <guillaume@lelarge.info>
@ 2008-12-30 15:39 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2008-12-30 15:39 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Laszlo Nagy <gandalf@shopzeus.com>; pgsql-performance
Guillaume Lelarge <guillaume@lelarge.info> writes:
> Laszlo Nagy a écrit :
>> We have serveral table where the index size is much bigger than the
>> table size.
>> ...
>> Vacuuming a table does not rebuild the indexes, am I right?
> Neither VACUUM nor VACUUM FULL rebuild the indexes. CLUSTER and REINDEX do.
In fact, VACUUM FULL tends to make indexes *more* bloated not less so.
One fairly likely explanation for how you got into this situation is
overeager use of VACUUM FULL.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Big index sizes
2008-12-30 09:05 Big index sizes Laszlo Nagy <gandalf@shopzeus.com>
@ 2008-12-30 14:43 ` Robert Haas <robertmhaas@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Robert Haas @ 2008-12-30 14:43 UTC (permalink / raw)
To: Laszlo Nagy <gandalf@shopzeus.com>; +Cc: pgsql-performance
On Tue, Dec 30, 2008 at 4:05 AM, Laszlo Nagy <gandalf@shopzeus.com> wrote:
> We have serveral table where the index size is much bigger than the table
> size.
You'll usually get index bloat in roughly the same measure that you
get table bloat. If you always (auto)vacuum regularly, then the
amount of bloat in your indexes probably reflects the amount of bloat
that your tables typically accumulate between vacuums, so reindexing
won't help much. The indexes will just re-bloat back to about the
same point over the next vacuum cycle or two.
On the other hand, if your table has shrunk considerably, or if you've
just removed a lot of bloat by vacuuming, REINDEX is often warranted.
It would be nice if the system could automatically notice and correct
situations that currently require VACUUM FULL or REINDEX, but it
doesn't.
...Robert
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-12-30 15:39 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-30 09:05 Big index sizes Laszlo Nagy <gandalf@shopzeus.com>
2008-12-30 09:21 ` Guillaume Lelarge <guillaume@lelarge.info>
2008-12-30 15:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-12-30 14:43 ` Robert Haas <robertmhaas@gmail.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