_bt_check_unique checks every row in table when doing update?? - Mailing list pgsql-bugs

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