Re: [SQL] index file's growing big
От | Karl Denninger |
---|---|
Тема | Re: [SQL] index file's growing big |
Дата | |
Msg-id | 20000306104619.B45015@Denninger.Net обсуждение исходный текст |
Ответ на | Re: [SQL] index file's growing big ("Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>) |
Ответы |
Re: [SQL] index file's growing big
Re: [SQL] index file's growing big |
Список | pgsql-sql |
Uh, why not just "vacuum" the table? You want to do this periodically anyway, as Postgres does not re-use TABLE entries either, and the table file itself will grow without bound if you don't do this once in a while. Further, if the tables are large you want to use "vacuum analyze" to reorder the indices in a fashion that makes queries more efficient. -- -- Karl Denninger (karl@denninger.net) Web: http://childrens-justice.org Isn't it time we started putting KIDS first? See the above URL for a plan to do exactly that! On Mon, Mar 06, 2000 at 10:24:33AM -0600, Ross J. Reedstrom wrote: > Zoltan - > It sounds like this is a very active table in you app, with lots of > inserts and deletes, right? Basically what's happening is that deletes > from the underlying table don't remove entries from the index file, > just marks them unused. And inserts add a new record at the end. > > This monotonic growth of index files is a known problem. I assume you're > using v. 6.5.3 or earlier: I believe the current 7.0 beta will reuse > index file entries, rather than append them, although it still doesn't > 'vacuum' them of unused entries, if I understand correctly. > > The dump->reload solves the problem for you by rebuilding the index. So, > rather than dumping the whole table, just drop and rebuild the index in > question. This can still take a long time if it's a big table, but my > guess is that it'll work for you. > > Ross > > > On Mon, Mar 06, 2000 at 12:23:29PM +0100, zoltan.sebestyen@netvisor.hu wrote: > > Hi, > > > > I wrote a web+commandline based package with PostgreSQL database backend. > > My problem is that as time goes by the index file grows to a huge size > > [80Mb]. If I dump and then reload it the database in question I'll get the > > very same data with a quite small index file. So, there's no inconsistency > > or corrupt database, but it's still annoying that I must handle manually. > > What I think is tha the command-line app I wrote for populating the > > database uses some wrong strategy, I mean there's no erroneous SQL phares, > > just may be it uses wrong tactics that's why it grows the index file to a > > quite big size. > > > > Any ideas? > > > > > Zoltan Sebestyen > > > NETvisor Kft > > > 1111 Muegyetem rkpt.9. Fsz.8. > > > Budapest, Hungary. > > > Fax: (+36-1) 463-4396 > > > Tel: (+36-1) 463-4392, 463-4393 > > > > > > "Programming is an art, not a science, > > > and not all programmers are Picassos." > > > > > > > > <<Sebestyén Zoltán.vcf>> > > > > ************ >
В списке pgsql-sql по дате отправления: