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                       j...@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to