Re: [PERFORM] Reasons and drawbacks for unused item pointers
Tom Lane <[EMAIL PROTECTED]> writes: > Martin Lesser <[EMAIL PROTECTED]> writes: >> What causes this "unused item pointers" and which impact do they have >> regarding performance? > The direct performance impact is really pretty minimal (and none at > all on indexscans, AFAIR). The reason Denis' number drew my attention > was that it implied that the table had gone un-vacuumed for awhile at > some time in the past. [...] To have 1905028 unused pointers in a > table with only 5106307 live entries suggests that at some point there > were 1.9 million (or so) dead but not-yet-vacuumed tuples, which > suggests insufficient vacuuming. Does each update of a single row result in an "unused item pointer"? I.e. if I update one row 10 times between VACUUMing the table the result are 10 unused pointers? Some rows in some of my tables are updated much more frequently than others so I'm not sure whether the number of unused pointers implie that I should VACUUM more often than every 24 hours. Martin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Reasons and drawbacks for unused item pointers (was: Update using primary key slow)
Martin Lesser <[EMAIL PROTECTED]> writes: > What causes this "unused item pointers" and which impact do they have > regarding performance? Those are item pointer slots that were once used but aren't used at the moment. VACUUM leaves an empty slot behind when it removes a dead tuple, and the slot is then available for re-use next time a tuple is created on that page. See http://developer.postgresql.org/docs/postgres/storage-page-layout.html The direct performance impact is really pretty minimal (and none at all on indexscans, AFAIR). The reason Denis' number drew my attention was that it implied that the table had gone un-vacuumed for awhile at some time in the past. His stats were showing about 64000 tuples deleted per vacuum pass, which would have created 64000 unused item pointers --- but in a steady-state situation those would be eaten up again by the time of the next vacuum. To have 1905028 unused pointers in a table with only 5106307 live entries suggests that at some point there were 1.9 million (or so) dead but not-yet-vacuumed tuples, which suggests insufficient vacuuming. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Reasons and drawbacks for unused item pointers (was: Update using primary key slow)
Tom Lane <[EMAIL PROTECTED]> writes: > Denis <[EMAIL PROTECTED]> writes: >> There were 1905028 unused item pointers. > The "unused item pointers" number seems a bit high, but otherwise that > looks pretty reasonable. > > Is it possible that the particular row you were updating has been > updated quite a lot of times since the last vacuum? Or even quite > a few times within a single transaction? What causes this "unused item pointers" and which impact do they have regarding performance? If I understood your last posting correctly more than one update on a single row between two vacuum's would i.e. result in one ore more "unused item pointer". Does this slow down the vacuum process and/or other processes? Until now I could not find an answer what this number implies. Regards Martin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
