Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The table I want to query has only about ~3000 rows with 3 columns:

int/varchar(100)/varchar(100) 

yet it takes ~30 sec to query them all!

Then I saw that the table size is 1331MB but the indexes size is 25GB!

The table has only one index and that is the pkey.

VACCUM FULL is running every 24hours and the table sould be pretty static anyway

My question is why are the indexes so big (I guess thats why the querys are so slow) and what can I do to fix that?

Hope you have any ideas, because I have none and I haven't found anything on google.

EDIT: Postgres Version is 8.1.18

share|improve this question

migrated from stackoverflow.com Jun 21 '13 at 11:54

This question came from our site for professional and enthusiast programmers.

4  
You should have mentioned your Postgres version (8.4, 9.0, ...) VACUUM FULL is known to bloat the indexes (in versions before 9.0). It is a last ressort thing to do and should not be done on a regular basis. Stop the VACUUM FULL (only do a regular VACUUM). Do a REINDEX once and you should be fine. –  a_horse_with_no_name Jun 21 '13 at 8:16
5  
8.1 has been discontinued a long time ago. Newer versions have made many enhancements in the VACUUM area and you should upgrade to a supported version now! –  a_horse_with_no_name Jun 21 '13 at 8:25
3  
Make sure to prominently mention that you're on 8.1.18 in any future questions. Almost everything requires a different answer for 8.1. Though that answer will usually be "omfg, upgrade". –  Craig Ringer Jun 21 '13 at 8:36
1  
i will and "omfg, upgrade" was my first reaction too ;-) but i'll be over soon enough. –  Patrick Jun 21 '13 at 8:43

2 Answers 2

up vote 11 down vote accepted

Pre-9.0 VACUUM FULL

You're on PostgreSQL 8.4 or older, where VACUUM FULL tends to bloat indexes. See this wiki page for details.

Don't run VACUUM FULL as a periodic maintenance task. It's unnecessary and inefficient. This remains true on current versions, it's just not as bad on 9.0 and above. If you feel the need to run VACUUM FULL regularly then you probably don't have autovacuum turned up far enough and are having table bloat issues. In fact, unless you've changed the FILLFACTOR on the table from its default 100 a VACUUM FULL is quite counter-productive; it'll compact away all the free space in the table, so following UPDATEs will have to extend the table.

Table extensions are currently one of the poorer performing operations in PostgreSQL, as they're controlled by a single global lock. So if you have tables that fluctuate in size, you really want to avoid constantly compacting and truncating them only to extend them again.

On some unusual workloads it can be worth running a periodic CLUSTER, which orders the table based on an index and effectively REINDEXes it. If you do many UPDATEs on the table should set a lower FILLFACTOR for efficiency.

If this table is being emptied and re-populated regularly, you should generally using TRUNCATE followed by COPY to fill it back up. If it's big, drop the indexes before the COPY then re-create them afterwards to produce indexes that are more compact and faster and to speed up the data load.

For one-off mitigation, CLUSTER the table or REINDEX it.

8.1?!?!

After edit added version: Holy bleepazoids, batman. 8.1.18? Forget what I said about autovacuum, autovacuum in 8.1 was way too ineffective. Upgrade to a sane version ASAP. You're not even on the current point release of 8.1, 8.1.23, from December 2010. 8.1.18 was released in September 2009! You need to begin your upgrade planning ... well, about two years ago, preferably. Read the release notes for every .0 version between 8.1 and the current release, focusing on the upgrade notes and compatibility notes. Then plan and execute your upgrade. If you don't feel up managing that on your own there are people who'll help you with it (I work for one of them) but honestly, the release notes and docs are quite sufficient for most people to do an upgrade themselves without undue pain.

Moving from 8.1 to 8.3 or newer will be your biggest pain point, as PostgreSQL 8.3 dropped a whole bunch of implicit casts that lots of potentially buggy SQL relied on. You'll need to test your application carefully on the newer version. Other changes to be aware of are:

  • The removal of implicit FROM and in later versions removal of the backwards compatibility parameter for it;
  • UTF-8 validation improvements in newer versions that can cause older dumps to fail to load until the data is corrected;
  • The change to standard_conforming_strings by default;
  • The change of bytea_output to hex
share|improve this answer
    
first off all big Thanks to all, but i'll have to stick to this version for now .. soo besides an REINDEX and Normal VACCUMs anything i should do? –  Patrick Jun 21 '13 at 8:31
1  
@Patrick The main thing you should do is upgrade. First, upgrade to 8.1.23. You're missing over a year of bug fixes before the final end-of-life version of 8.1. Per the version policy it's a safe and well-tested update. Then start planning an upgrade to a modern version. You can work around it with a REINDEX and switching to normal VACUUM as a temporary measure while you plan and test your upgrade, but do not just do that and forget about it. Also, mention your version in future questions or you'll get grumped at. –  Craig Ringer Jun 21 '13 at 8:40
    
i know that this version is insane and i certainly don't like it, but i'll only have to maintain it for 2 or 3 months. –  Patrick Jun 21 '13 at 8:42
2  
@Patrick These are the kinds of details you should put in your original questions. "I'm on 8.1.18; I know it's ancient and unsupported, but I can't upgrade since we're retiring the system a few months, so I'm looking for workaround/mitigation options". –  Craig Ringer Jun 21 '13 at 8:43
    
Excellent answer, +1. –  0xCAFEBABE Jun 21 '13 at 8:57

Same thing happened to me once on 9.1.

I had a script that would process large size of data and then insert in the db. The issue showed up in the initial data import.

I didn't find the cause of this but a workaround was to drop the index and recreate it.

You could also pg_dump and then pg_restore the whole db, and this will also recreate the index.

share|improve this answer
1  
REINDEX would be rather better than drop and re-create if you can't avoid the root cause. –  Craig Ringer Jun 21 '13 at 8:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.