Thread: To Do wiki
The To Do wiki says not to add things to the page with discussing here. So here are some things to discuss. Assuming the discussion is a brief yup or nope, it seems to make sense to lump them into one email: Vacuuming a table with a large GIN index is painfully slow, because the index is vacuumed in logical order not physical order. Is making a vacuum in physical order a to-do? Does this belong to vacuuming, or to GIN indexing? Looking at the complexity of how this was done for btree index, I would say this is far from easy. I wonder if there is an easier way that is still good enough, for example every time you split a page, check to see if a vacuum is in the index, and if so only move tuples physically rightward. If the table is so active that there is essentially always a vacuum in the index, this could lead to bloat. But if the table is that large and active, under the current non-physical order the vacuum would likely take approximately forever to finish and so the bloat would be just as bad under that existing system. "Speed up COUNT(*)" is marked as done. While index-only-scans should speed this up in certain cases, it is nothing compared to the speed up that could be obtained by "use a fixed row count and a +/- count to follow MVCC visibility rules", and that speed-up is the one people used to MyISAM are expecting. We might not want to actually implement the fixed row count +/- MVCC count idea, but we probably shouldn't mark the whole thing as done because just one approach to it was implemented. sort_support was implemented for plain tuple sorting only, To Do is extend to index-creation sorts (item 2 from message <1698.1323222387@sss.pgh.pa.us>) Cheers, Jeff
On 10.04.2012 03:32, Jeff Janes wrote: > The To Do wiki says not to add things to the page with discussing here. > > So here are some things to discuss. Assuming the discussion is a > brief yup or nope, it seems to make sense to lump them into one email: > > Vacuuming a table with a large GIN index is painfully slow, because > the index is vacuumed in logical order not physical order. Is making > a vacuum in physical order a to-do? Does this belong to vacuuming, or > to GIN indexing? Looking at the complexity of how this was done for > btree index, I would say this is far from easy. I wonder if there is > an easier way that is still good enough, for example every time you > split a page, check to see if a vacuum is in the index, and if so only > move tuples physically rightward. If the table is so active that > there is essentially always a vacuum in the index, this could lead to > bloat. But if the table is that large and active, under the current > non-physical order the vacuum would likely take approximately forever > to finish and so the bloat would be just as bad under that existing > system. Yup, seems like a todo. It doesn't sound like a good idea to force tuples to be moved right when a vacuum is in progress, that could lead to bloating, but it should be feasible to implement the same cycleid-mechanism in gin that we did in b-tree. > "Speed up COUNT(*)" is marked as done. While index-only-scans should > speed this up in certain cases, it is nothing compared to the speed up > that could be obtained by "use a fixed row count and a +/- count to > follow MVCC visibility rules", and that speed-up is the one people > used to MyISAM are expecting. We might not want to actually implement > the fixed row count +/- MVCC count idea, but we probably shouldn't > mark the whole thing as done because just one approach to it was > implemented. I think the way we'd speed up COUNT(*) further would be to implement materialized views. Then you could define a materialized view on COUNT(*), and essentially get a row counter similar to MyISAM. I think it's fair to mark this as done. > sort_support was implemented for plain tuple sorting only, To Do is > extend to index-creation sorts (item 2 from message > <1698.1323222387@sss.pgh.pa.us>) Index-creation sorts are already handled, Tom is referring to using the new comparator API for index searches in that email. The change would go to _bt_compare(). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Tue, Apr 10, 2012 at 7:27 AM, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote: > I think the way we'd speed up COUNT(*) further would be to implement > materialized views. Then you could define a materialized view on COUNT(*), > and essentially get a row counter similar to MyISAM. I think it's fair to > mark this as done. If only because it comes up so frequently it would be good to have this noted in the TODO, either under materialized views or as a pointer to them. A good materialized views implementation including automatically determining what delta data to keep sure would be nice to have. -- greg
On Mon, Apr 9, 2012 at 11:27 PM, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote: > On 10.04.2012 03:32, Jeff Janes wrote: >> >> The To Do wiki says not to add things to the page with discussing here. ... > >> sort_support was implemented for plain tuple sorting only, To Do is >> extend to index-creation sorts (item 2 from message >> <1698.1323222387@sss.pgh.pa.us>) > > > Index-creation sorts are already handled, Tom is referring to using the new > comparator API for index searches in that email. The change would go to > _bt_compare(). If I do "select count(distinct bid) from pgbench_accounts" I get many calls to btint4fastcmp, but if I do "create index on pgbench_accounts (bid)" I instead get many calls to btint4cmp. If the index build is using SortSupport, shouldn't it also be calling btint4fastcmp like the distinct does? Cheers, Jeff
On 10.04.2012 18:31, Jeff Janes wrote: > On Mon, Apr 9, 2012 at 11:27 PM, Heikki Linnakangas > <heikki.linnakangas@enterprisedb.com> wrote: >> On 10.04.2012 03:32, Jeff Janes wrote: >>> >>> The To Do wiki says not to add things to the page with discussing here. > ... >> >>> sort_support was implemented for plain tuple sorting only, To Do is >>> extend to index-creation sorts (item 2 from message >>> <1698.1323222387@sss.pgh.pa.us>) >> >> Index-creation sorts are already handled, Tom is referring to using the new >> comparator API for index searches in that email. The change would go to >> _bt_compare(). > > If I do "select count(distinct bid) from pgbench_accounts" I get many > calls to btint4fastcmp, but if I do "create index on pgbench_accounts > (bid)" I instead get many calls to btint4cmp. If the index build is > using SortSupport, shouldn't it also be calling btint4fastcmp like the > distinct does? Oh, sorry, you're right. I stand corrected. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On 10 April 2012 16:40, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote: > On 10.04.2012 18:31, Jeff Janes wrote: >> If I do "select count(distinct bid) from pgbench_accounts" I get many >> calls to btint4fastcmp, but if I do "create index on pgbench_accounts >> (bid)" I instead get many calls to btint4cmp. If the index build is >> using SortSupport, shouldn't it also be calling btint4fastcmp like the >> distinct does? > > > Oh, sorry, you're right. I stand corrected. There is an impedance mismatch between tuplesort_begin_heap and tuplesort_begin_index_btree that prevented this from being done with the initial commit. Strangely, the SortSupport commit message didn't comment on this. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services