Embedded VACUUM

From: C Pond <cpondwork(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Embedded VACUUM
Date: 2011-09-03 00:25:00
Message-ID: 1315009500.78128.YahooMailClassic@web112110.mail.gq1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I'm running a labour-intensive series of queries on a medium-sized dataset (~100,000 rows) with geometry objects and both gist and btree indices.

The queries are embedded in plpgsql, and have multiple updates, inserts and deletes to the tables as well as multiple selects which require the indices to function correctly for any kind of performance.

My problem is that I can't embed a vacuum analyze to reset the indices and speed up processing, and the queries get slower and slower as the un-freed space builds up.

From my understanding, transaction commits within batches are not allowed (so no vacuum embedded within queries). Are there plans to change this? Is there a way to reclaim dead space for tables that have repeated inserts, updates and deletes on them? I have tried a simple analyze, and this does not quite cut it. I'm getting seq-scans after the first round of processing instead of hitting the index correctly.

My apologies if this is directed at the wrong forum, and thank you for your help.

-cris pond

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jayadevan 2011-09-03 04:48:43 Re: Query performance issue
Previous Message Stefan Keller 2011-09-02 22:04:30 Re: Summaries on SSD usage?