By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,710 Members | 1,906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,710 IT Pros & Developers. It's quick & easy.

page locking? too many btree indexes...

P: n/a
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


Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Tue, Oct 26, 2004 at 11:44:42AM -0400, Brian Maguire wrote:
Can too many btree indexes cause page level locking?
Yes, too many btree indexes can, as can a single btree 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.


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

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.