On 08/25/2016 01:45 AM, Tom Lane wrote:
Over in the thread about the SP-GiST inet opclass, I threatened to post a patch like this, and here it is. The basic idea is to track more than just the very latest page we've used in each of the page categories that SP-GiST works with. I started with an arrangement that gave an equal number of cache slots to each category, but soon realized that that was dumb, because there are usually way more leaf pages than anything else. So this version has a little table of how many slots to give to each category. The constants could maybe use a bit more fiddling, if we have some more test data sets to try this on. On the IRRExplorer data set we discussed in the other thread, this reduces the index size from 132MB to 120MB. Poking into that more closely with pg_filedump, the total free space within the index drops from 42MB to 28MB. If you think those numbers don't add up, you're right --- this seems to result in more non-leaf tuples than before. I'm not sure why; maybe more aggressive sucking up of free space results in more splits. (Maybe adjustment of the default spgist fillfactor would be in order to counteract that?) But the index search time doesn't seem to be hurt, so perhaps there's nothing to worry about. As coded, this makes no attempt to preferentially select pages with the most or least free space. I don't know if it'd be worth any cycles to do that. I'll put this in the commitfest queue. It could use review from someone with the time and motivation to do performance testing/tuning.
I can do a bit of benchmarking on this, I guess - possibly next week, but I can't promise that 100%. I'm not a spgist-expert and I won't have time to dive into the code, so it'll be mostly blackbox testing. Any hints what would be worth/interesting to test?
ISTM it'd be interesting to test both index creation, maintenance and querying, while varying the fillfactor. What other parameters would be interesting to tweak, and what datasets might be useful?
regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
