Can too many btree indexes cause page level locking?



I read this...



http://www.postgresql.org/docs/7.4/s...g-indexes.html



The concern is the exclusive page-level locking that occurs on inserts
to the index.



I am experiencing locking related on two tables. Each has several
indexes on it (4 or more). One table is frequently updated (20%),
occasional inserts(10%), and many reads (70%) and the other has many
inserts and reads every 20 secs.



The largest table is with many inserts currently has 2.5 million records
and has inserts of about 200 per second. The read is every 20 seconds.





Thanks,

Brian

On Tue, Oct 26, 2004 at 11:44:42AM -0400, Brian Maguire wrote:
Yes, too many btree indexes can, as can a single btree index.

Most likely, your problem is not related to the indexes. Yes, there is
page-level exclusive locking on the indexes when there's insert or
delete operations going on, but they don't cause deadlocks. The likely
cause of your problem is foreign key relationships. Those are
implemented using row-level exclusive locking, and they can (and often
do) cause deadlocks.

Do you have any foreign keys defined?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Porque Kim no hacia nada, pero, eso sí,
con extraordinario éxito" ("Kim", Kipling)


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster