Re: Race condition in b-tree page deletion

On 11/9/13, 10:02 AM, Heikki Linnakangas wrote:
> 3. Another approach would be to get rid of the "can't delete rightmost child" limitation. We currently have that limitation because it ensures that we never need to change the high-key of a page. If we delete a page that is the rightmost child of its parent, we transfer the deleted keyspace from the parent page to its right sibling. To do that, we need to update the high key of the parent, as well as the downlink of the right sibling at the grandparent level. That's a bit complicated, because updating the high key might require splitting the page.

Is the rightmost child issue likely to affect indexes on increasing values, like a queue table? Because we get significant bloat on our indexes, especially ones that are on things like timestamps in a queue table (FIFO queue, not a LIFO stack):

INFO: index "page_hits_pkey" now contains 6083 row versions in 9363 pages

INFO: vacuuming "cnu_stats.page_hits_queue"
INFO: scanned index "page_hits_pkey" to remove 4329 row versions
DETAIL: CPU 0.07s/0.02u sec elapsed 0.60 sec.
INFO: "page_hits_queue": removed 4329 row versions in 436 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: index "page_hits_pkey" now contains 7891 row versions in 9363 pages
DETAIL: 4329 index row versions were removed.
9338 index pages have been deleted, 9227 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "page_hits_queue": found 4329 removable, 7891 nonremovable row versions in 548 out of 548 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 10210 unused item pointers.
0 pages are entirely empty.
CPU 0.08s/0.02u sec elapsed 0.62 sec.
INFO: vacuuming "pg_toast.pg_toast_25287"
INFO: index "pg_toast_25287_index" now contains 0 row versions in 2 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_25287": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net