Re: vacuum_cost_delay & VACUUM holding locks on GIST

On Mon, 28 Feb 2005, Tom Lane wrote:
>
> You could shorten the intervals for which the lock is held by reducing
> vacuum_mem, but this might be counterproductive overall.

Does this work?

I just tried:

setting vacuum_mem=1024
setting vacuum_cost_delay=10

ran a while loop that repeatedly executes a simple
select statement that usually takes 0.03 seconds

and it still gave me a single extremely slow (7 minutes
long instead of 25 milliseconds) query that lasted until
after the ...GIST... line was shown on vacuum verbose output.

It feels like even with the minimal vacuum_mem it spent a very
long time (430 seconds) wihtout releasing the lock on the GIST
index for my pretty large (relpages=94371 pages) table.

Or do I have something else broken? Shown below is the output
of a while loop of a simple query using this index; and the
\d table output.

Ron

=================================================================
== Loop of small select()s
== with vacuum_mem=1024 and vacuum_cost_delay=10
=================================================================
%while (1)
while? echo " explain analyze SELECT * from lines2 WHERE the_geom && setSRID('BOX3D(-84.31043 30.44341,-84.2954 30.45372)'::BOX3D, -1 );" | psql fli fli | grep runtime
while? sleep 5
while? end
Total runtime: 24.375 ms /* set vacuum_mem=1024 */
Total runtime: 24.303 ms
Total runtime: 25.370 ms /* vacuum verbose */
Total runtime: 27.332 ms
Total runtime: 26.628 ms
Total runtime: 26.001 ms
[many more like this]
Total runtime: 27.437 ms
Total runtime: 24.679 ms
Total runtime: 26.628 ms
Total runtime: 431265.868 ms
Total runtime: 24.419 ms /* INFO: index "tmp_lines2__gist" ... */
Total runtime: 24.375 ms
Total runtime: 24.303 ms
Total runtime: 24.294 ms
Total runtime: 24.235 ms

=================================================================
== \d for the table.
=================================================================
fli=# \d lines2;
Table "tmp.lines2"
Column | Type | Modifiers
-----------+----------------------+-----------
tigerfile | integer |
tlid | integer |
cfcc | character varying(3) |
name | text |
the_geom | geometry |
Indexes:
"lines2__tlid" btree (tlid)
"tmp_lines2__gist2" gist (the_geom)
Check constraints:
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'LINESTRING'::text OR the_geom IS NULL)
"enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
"enforce_srid_the_geom" CHECK (srid(the_geom) = -1)

fli=#