Re: Answering my own question
От | gerry.smit@lombard.ca |
---|---|
Тема | Re: Answering my own question |
Дата | |
Msg-id | OFEB8CCC63.4A3C63D1-ON85256BBB.0057C4F3@lombard.ca обсуждение исходный текст |
Ответ на | Answering my own question (gerry.smit@lombard.ca) |
Список | pgsql-novice |
Well, this is only my second day on the list, but I don't think "NOVICE" could ever have a question to be embarrased about. VACUUM does 3 things (at least in 7.2) : - VACUUM (alone) takes rows marked for deletion, and frees up the space to be re-used by later INSERTs or UPDATEs. - I'm guessing, but it shure looks like DELETE doesn't physically delete, it logically deletes. - similarly UPDATE doesn't seem to physically update in place, but logically update by marking the old record as "deleteable" and INSERTing (in effect) a new record with your changes. - all of which leaves a lot of Logically deleted, but physically still present , records in your file. - VACUUM FULL apparently goes one step further than VACUUM, and actually frees up the disk space for the operating system. - FULL is NOT an option to VACUUM in 7.1.3 so I'm guessing, based on comments from other emails. - In our shop we're running Postgres 7.1.3 on a Solaris 2.(6?7?8) E3500. After a VACUUM, the unix files in pgsql/data/base are no smaller. - eventually these get so big, its worthwhile to pg_dump , DROP, and reload a volatile table. At which point the unix files are smaller, and response time is improved. - presumably in postgres 7.2+ , FULL was added as an option to VACUUM to do this inherently. In IBM mainframe speak, it somewhat analogous to doing a "freespace" and "de-frag". - VACUUM ANALYZE, as I've learned so well these past two days, provides stats on the remaining rows in your tables, allowing the QUERY PLAN OPTIMIZER to best determine whether or not to use any indicies, and if so, which ones. - VACUUM VERBOSE - just dumps the output of the VACUUM to the calling script or command line. A copy of said output is in your server.log file in any case. Interestingly enough VACUUM VERBOSE ANALYZE doesn't get you any stats from the Analyzer, just the line "Analyzing...." I was hoping for crytpic clues for things like "commonly recurring key, index useless" type messages. Or "too few rows, index useless". Gerry Smit, Toronto, Canada. "Balazs Wellisch" <balazs@bwellisch.com> To: pgsql-novice@postgresql.org Sent by: cc: pgsql-novice-owner@pos Fax to: tgresql.org Subject: Re: [NOVICE] Answering my own question 15/05/2002 06:57 PM Hi all, I'm embarrassed to even ask this, but this being a novice list here it goes. What exactly is Vacuum? Is there an equivalent of this in MS SQL Server? Thanks, Balazs Wellisch ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
В списке pgsql-novice по дате отправления: