Thread
-
_bt_check_unique checks every row in table when doing update??
Mats Lofkvist <mal@algonet.se> — 2002-09-09T14:48:27Z
Hi, I'm running 7.2.1 on a sparc/solaris box. I get performance problems when doing updates in one of my tables (inserts are faster), so I tried to run it with profiling enabled. With reservations for my non-existent knowledge of the code, the results do look like the bad performance is because of some bug. When doing ~1000 inserts into the testdata table, gprof says this about _bt_check_unique which looks reasonable: ----------------------------------------------- 0.00 0.01 1002/1002 _bt_doinsert [86] [264] 0.1 0.00 0.01 1002 _bt_check_unique [264] 0.00 0.01 1006/1006 _bt_isequal [271] 0.00 0.00 1002/67941 _bt_binsrch <cycle 1> [4417] 0.00 0.00 6/429 heap_fetch [331] 0.00 0.00 2/857310 ReleaseBuffer [50] ----------------------------------------------- But when doing ~1000 updates (i.e. setting val0 and val1 with a where on an existing key0/key1/key2 triplet), I get this which seems very strange to me: ----------------------------------------------- 0.10 6.43 1002/1002 _bt_doinsert [21] [22] 17.3 0.10 6.43 1002 _bt_check_unique [22] 0.40 3.21 505436/505436 _bt_isequal [26] 0.57 2.22 500509/1000450 heap_fetch [23] 0.00 0.01 4926/27777 _bt_getbuf [157] 0.00 0.00 4926/25330 _bt_relbuf [262] 0.00 0.00 1002/2213707 _bt_binsrch <cycle 1> [4425] 0.00 0.00 1001/1878622 ReleaseBuffer [41] ----------------------------------------------- The schema is as follows: mats=# \d testdata Table "testdata" Column | Type | Modifiers --------+-----------------------+----------- key0 | character varying(32) | not null key1 | character varying(32) | not null key2 | character varying(32) | not null val0 | character varying(64) | not null val1 | text | not null Indexes: testdataval0index Unique keys: testdataindex mats=# \d testdataindex Index "testdataindex" Column | Type --------+----------------------- key0 | character varying(32) key1 | character varying(32) key2 | character varying(32) unique btree mats=# \d testdataval0index Index "testdataval0index" Column | Type --------+----------------------- val0 | character varying(64) btree mats=# select count(*) from testdata; count -------- 435614 (1 row) Note that he number of calls to _bt_isequal is very close to the number of rows in the table plus the number of dead rows. Vacuum analyze and/or recreating the unique index makes no difference as far as I can tell. (The fact that _bt_check_unique is called at all when doing an update seems strange by itself, but shouldn't be a problem if it was just done as efficiently as when doing an insert.) _ Mats Lofkvist mal@algonet.se -
Re: _bt_check_unique checks every row in table when doing update??
Tom Lane <tgl@sss.pgh.pa.us> — 2002-09-09T20:21:50Z
Mats Lofkvist <mal@algonet.se> writes: > But when doing ~1000 updates (i.e. setting val0 and val1 with > a where on an existing key0/key1/key2 triplet), I get this which > seems very strange to me: I suppose you repeatedly updated the same row 1000 times? That creates an O(N^2) behavior because the dead tuples have to be rechecked again and again. 7.3 will be smarter about this. regards, tom lane
-
Re: _bt_check_unique checks every row in table when doing update??
Mats Lofkvist <mal@algonet.se> — 2002-09-10T07:36:29Z
tgl@sss.pgh.pa.us (Tom Lane) writes: > Mats Lofkvist <mal@algonet.se> writes: > > But when doing ~1000 updates (i.e. setting val0 and val1 with > > a where on an existing key0/key1/key2 triplet), I get this which > > seems very strange to me: > > I suppose you repeatedly updated the same row 1000 times? That creates > an O(N^2) behavior because the dead tuples have to be rechecked again > and again. > > 7.3 will be smarter about this. > > regards, tom lane Yes, I did update the same row so your explanation is correct. (Note that I also read the data wrong, for some reason I read it as 500k checks per 1 update, not per 1000 which made it look a lot worse than it is.) Thanks for the explanation, I'll try 7.3 beta to see how it works there. _ Mats Lofkvist mal@algonet.se -
Re: _bt_check_unique checks every row in table when doing update??
Mats Lofkvist <mal@algonet.se> — 2002-09-12T08:40:25Z
tgl@sss.pgh.pa.us (Tom Lane) writes: > Mats Lofkvist <mal@algonet.se> writes: > > But when doing ~1000 updates (i.e. setting val0 and val1 with > > a where on an existing key0/key1/key2 triplet), I get this which > > seems very strange to me: > > I suppose you repeatedly updated the same row 1000 times? That creates > an O(N^2) behavior because the dead tuples have to be rechecked again > and again. > > 7.3 will be smarter about this. > Seems like I get the same behaviour with 7.3 beta1, updating the same row ~20k times and then 1k times more with profiling enabled (and with no vacuum in between) gives: ----------------------------------------------- 2.72 166.12 1002/1002 _bt_doinsert [17] [18] 53.7 2.72 166.12 1002 _bt_check_unique [18] 15.81 149.01 21721926/21721926 _bt_isequal [19] 0.05 1.00 221414/412979 _bt_getbuf [40] 0.01 0.21 221414/409772 _bt_relbuf [91] 0.01 0.02 2709/6241 heap_fetch [187] 0.00 0.00 5418/2726620 LockBuffer [50] 0.00 0.00 1002/65406369 _bt_binsrch <cycle 1> [270] 0.00 0.00 2709/1333460 ReleaseBuffer [76] 0.00 0.00 2709/4901 HeapTupleSatisfiesVacuum [519] 0.00 0.00 1707/4910 SetBufferCommitInfoNeedsSave [652] ----------------------------------------------- (In my case, I think the call to _bt_check_unique could be avoided altogether since the update isn't changing any of the columns present in the unique key. But doing this optimization maybe is much harder than just trying to avoid checking the dead tuples over and over again?) _ Mats Lofkvist mal@algonet.se -
Re: _bt_check_unique checks every row in table when doing update??
Tom Lane <tgl@sss.pgh.pa.us> — 2002-09-12T13:32:55Z
Mats Lofkvist <mal@algonet.se> writes: > tgl@sss.pgh.pa.us (Tom Lane) writes: >> 7.3 will be smarter about this. > Seems like I get the same behaviour with 7.3 beta1, updating > the same row ~20k times and then 1k times more with profiling > enabled (and with no vacuum in between) gives: > 2.72 166.12 1002/1002 _bt_doinsert [17] > [18] 53.7 2.72 166.12 1002 _bt_check_unique [18] > 15.81 149.01 21721926/21721926 _bt_isequal [19] > 0.05 1.00 221414/412979 _bt_getbuf [40] > 0.01 0.21 221414/409772 _bt_relbuf [91] > 0.01 0.02 2709/6241 heap_fetch [187] Yeah, but you'll notice there is no heap_fetch for most of 'em, unlike before... The loop in _bt_check_unique tests _bt_isequal before checking ItemIdDeleted, so the short-circuit for deleted items doesn't serve to reduce the number of key comparisons, only the number of heap tuple fetches. I do not think that reversing this logic would be a net improvement in typical cases: we want to fall out of the loop as soon as we've run off the set of equal keys, whether the current index entry is dead or alive. If we switched the test order then we'd not get out of the loop until we found a live entry that's bigger than the insertion key. > (In my case, I think the call to _bt_check_unique could be > avoided altogether since the update isn't changing any of > the columns present in the unique key. It's fairly difficult for the index AM to know that; in general we don't have access to the old tuple to check, at the time the index update is running. regards, tom lane
-
Re: _bt_check_unique checks every row in table when doing update??
Mats Lofkvist <mal@algonet.se> — 2002-09-12T14:23:11Z
tgl@sss.pgh.pa.us (Tom Lane) writes: > Mats Lofkvist <mal@algonet.se> writes: > > tgl@sss.pgh.pa.us (Tom Lane) writes: > >> 7.3 will be smarter about this. > > > Seems like I get the same behaviour with 7.3 beta1, updating > > the same row ~20k times and then 1k times more with profiling > > enabled (and with no vacuum in between) gives: > > > 2.72 166.12 1002/1002 _bt_doinsert [17] > > [18] 53.7 2.72 166.12 1002 _bt_check_unique [18] > > 15.81 149.01 21721926/21721926 _bt_isequal [19] > > 0.05 1.00 221414/412979 _bt_getbuf [40] > > 0.01 0.21 221414/409772 _bt_relbuf [91] > > 0.01 0.02 2709/6241 heap_fetch [187] > > Yeah, but you'll notice there is no heap_fetch for most of 'em, unlike > before... Ah, missed that one. > > The loop in _bt_check_unique tests _bt_isequal before checking > ItemIdDeleted, so the short-circuit for deleted items doesn't serve to > reduce the number of key comparisons, only the number of heap tuple > fetches. I do not think that reversing this logic would be a net > improvement in typical cases: we want to fall out of the loop as soon as > we've run off the set of equal keys, whether the current index entry is > dead or alive. If we switched the test order then we'd not get out of > the loop until we found a live entry that's bigger than the insertion > key. In my case the problem is that my unique index consists of three varchar(32) columns which I suppose is making the comparison rather expensive. But I agree that it is not obvious that changing the logic would be an improvement in general. > > > (In my case, I think the call to _bt_check_unique could be > > avoided altogether since the update isn't changing any of > > the columns present in the unique key. > > It's fairly difficult for the index AM to know that; in general we don't > have access to the old tuple to check, at the time the index update is > running. But you really shouldn't need the old tuple to know this since none of the columns present in the unique index are 'set' by the update? I.e. the 'not changing the unique key part' is not data dependent, it is guarantied by the form of the update statement. Isn't it a very common case to do updates with sets only on non-key columns (only selecting on the key columns) ? > > regards, tom lane _ Mats Lofkvist mal@algonet.se -
Re: _bt_check_unique checks every row in table when doing update??
Tom Lane <tgl@sss.pgh.pa.us> — 2002-09-12T14:47:15Z
Mats Lofkvist <mal@algonet.se> writes: > tgl@sss.pgh.pa.us (Tom Lane) writes: >>> (In my case, I think the call to _bt_check_unique could be >>> avoided altogether since the update isn't changing any of >>> the columns present in the unique key. >> >> It's fairly difficult for the index AM to know that; in general we don't >> have access to the old tuple to check, at the time the index update is >> running. > But you really shouldn't need the old tuple to know this since none > of the columns present in the unique index are 'set' by the update? > I.e. the 'not changing the unique key part' is not data dependent, > it is guarantied by the form of the update statement. (a) that's even further upstream from the index AM, and (b) what about BEFORE triggers that change the tuple contents? regards, tom lane
-
Re: _bt_check_unique checks every row in table when doing update??
Mats Lofkvist <mal@algonet.se> — 2002-09-12T15:09:09Z
tgl@sss.pgh.pa.us (Tom Lane) writes: > > But you really shouldn't need the old tuple to know this since none > > of the columns present in the unique index are 'set' by the update? > > I.e. the 'not changing the unique key part' is not data dependent, > > it is guarantied by the form of the update statement. > > (a) that's even further upstream from the index AM, and (b) what about > BEFORE triggers that change the tuple contents? > > regards, tom lane Ok, I rest my case. I obviously don't know enough of the postgres internals to suggest a usable solution for this problem. Thanks for your quick answers. _ Mats Lofkvist mal@algonet.se -
Re: _bt_check_unique checks every row in table when doing update??
Bruce Momjian <pgman@candle.pha.pa.us> — 2002-09-12T16:26:47Z
Tom Lane wrote: > > (In my case, I think the call to _bt_check_unique could be > > avoided altogether since the update isn't changing any of > > the columns present in the unique key. > > It's fairly difficult for the index AM to know that; in general we don't > have access to the old tuple to check, at the time the index update is > running. We do have a TODO item: * Prevent index uniqueness checks when UPDATE does not modifying column -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073