pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedEmbedded VACUUM
2+ messages / 2 participants
[nested] [flat]
* Embedded VACUUM
@ 2011-09-03 00:25 C Pond <cpondwork@yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: C Pond @ 2011-09-03 00:25 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Embedded VACUUM
@ 2011-09-05 09:26 Craig Ringer <ringerc@ringerc.id.au>
parent: C Pond <cpondwork@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: Craig Ringer @ 2011-09-05 09:26 UTC (permalink / raw)
To: C Pond <cpondwork@yahoo.com>; +Cc: pgsql-performance
On 3/09/2011 8:25 AM, C Pond wrote:
> 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?
Not, AFAIK, until the transaction doing the deletes/updates commits and
so do any older SERIALIZABLE transactions as well as any older running
READ COMMITTED statements.
This is one of the areas where Pg's lack of true stored procedures bites
you. You'll need to do the work via an out-of-process helper over a
regular connection, or do your work via dblink to achieve the same effect.
--
Craig Ringer
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-09-05 09:26 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-03 00:25 Embedded VACUUM C Pond <cpondwork@yahoo.com>
2011-09-05 09:26 ` Craig Ringer <ringerc@ringerc.id.au>
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