vacuum_cost_delay & VACUUM holding locks on GIST indexes

When you VACUUM a table with postgis indexes (perhaps GIST indexes
in general?) it seems a lock is held on the table. Setting
vacuum_cost_delay seems to make vacuum hold this lock much longer.

Is this true?

If so, I assume that's not desirable behavior, right? It makes
autovacuum harder to use on tables that have these indexes.

Any clever workarounds?

Ron

fli-lin1 /home/pg> 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: 23.355 ms
Total runtime: 32.276 ms [ "vacuum verbose" starts ]
Total runtime: 36.080 ms
Total runtime: 28.373 ms
Total runtime: 114679.281 ms [ bad but not horrible]
Total runtime: 30.823 ms
[...]
Total runtime: 22.867 ms [ "set vacuum_cost_delay=20"]
Total runtime: 22.808 ms
Total runtime: 23.288 ms [ "vacuum vebose" again ]
Total runtime: 23.366 ms
[ dozens of lines ]
Total runtime: 23.337 ms
Total runtime: 764133.163 ms [ YIPES ]
Total runtime: 23.722 ms

fli=# select * from pg_locks;
relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+--------------------------+---------
36677268 | 17230 | | 29039 | AccessShareLock | t
36677268 | 17230 | | 29039 | ShareUpdateExclusiveLock | t
36677268 | 17230 | | 29039 | AccessExclusiveLock | t
33620188 | 17230 | | 29039 | ShareUpdateExclusiveLock | t
| | 66414 | 30758 | ExclusiveLock | t
36677268 | 17230 | | 30731 | AccessShareLock | f
33620188 | 17230 | | 29039 | ShareUpdateExclusiveLock | t
36677269 | 17230 | | 29039 | ShareUpdateExclusiveLock | t
16839 | 17230 | | 30758 | AccessShareLock | t
| | 66412 | 30731 | ExclusiveLock | t
33620188 | 17230 | | 30731 | AccessShareLock | t
| | 66372 | 29039 | ExclusiveLock | t
(12 rows)

fli=#
fli=# select * from pg_class where oid=36677268;
relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
------------------+--------------+---------+----------+-------+-------------+---------------+----------+------------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
tmp_lines2__gist | 2758256 | 0 | 100 | 783 | 36677268 | 0 | 34623 | 6.1128e+06 | 0 | 0 | f | f | i | 1 | 0 | 0 | 0 | 0 | 0 | f | f | f | f |
(1 row)

Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> writes:
> When you VACUUM a table with postgis indexes (perhaps GIST indexes
> in general?) it seems a lock is held on the table.

GIST isn't concurrent-safe.

> Any clever workarounds?

Fix GIST ;-)

You could shorten the intervals for which the lock is held by reducing
vacuum_mem, but this might be counterproductive overall.

regards, tom lane

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=#

Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> writes:
> 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?

[ thinks about it... ] No, probably not; sorry for the misinformation.

Cutting vacuum_mem will reduce the number of index tuples that are to be
deleted during any one scan of the index. But if the index is large,
it's probably the scanning time and not the deletion time that is dominant.

regards, tom lane