tag:blogger.com,1999:blog-20038672.post606492814630767275..comments2024-03-19T04:09:53.166-04:00Comments on Robert Haas: Index-Only ScansRobert Haashttp://www.blogger.com/profile/08393677427643988650noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-20038672.post-32352164792721650132011-10-21T17:33:02.432-04:002011-10-21T17:33:02.432-04:00See newer post http://rhaas.blogspot.com/2011/10/i...See newer post http://rhaas.blogspot.com/2011/10/index-only-scans-weve-got-em.htmlRobert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-40764559002836111762011-07-07T19:05:06.309-04:002011-07-07T19:05:06.309-04:00Let's take the read-only index a little furthe...Let's take the read-only index a little further... what about a read-only table? This would be a useful baby step toward IOTs and other larger goals:<br /><br />example:<br />CREATE STATIC TABLE COUNTRIES( ........)<br /><br />Now PG should be able to say "this is a static table, no need to validate index lookups"BLovenoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-41593605628816899842011-02-02T22:22:28.635-05:002011-02-02T22:22:28.635-05:00I haven't dug too deep into this but "Tra...I haven't dug too deep into this but "Transactions" are run-time phenomena. Immediately after a server boots up isn't EVERY "current" record visible to all future transactions and all older record no longer necessary? Kinda like a VCS - once your release is deployed the history and concurrent updates, while interesting, are irrelevant since the "TRUE" view of the system has already been chosen.David Johnstonhttp://www.birdofire.comnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-13477714010885524892010-12-01T18:05:03.206-05:002010-12-01T18:05:03.206-05:00It seems as if being able to set just the index in...It seems as if being able to set just the index in question to read only mode would provide one simple way to implement the feature with maximum performance. Methods which monitor the update page flag for the table data would seem a significantly more expensive option which would enable index physical scans in the less common case where a read only lock of the index in question is unacceptable.<br>OTOH it might be nice to mmap() index such that the storage media wouldn't be touched for read only data selects, and the query optimizer could automatically infer the ability to pull fields from the most appropriate index.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-41326925644295750342010-12-01T10:27:56.688-05:002010-12-01T10:27:56.688-05:00A very simple solution:
During crash recovery, ma...A very simple solution:<br /><br />During crash recovery, mark every page as potentially having dead tuples. Vacuum will run a little slower initially, but quickly, things will get back in sync.<br /><br />It is crash safe now, with a minimum of effort, and no chance I see for it to ever do the wrong thing.Grant Johnsonhttps://www.blogger.com/profile/06772023212277928936noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-17399637052554098702010-11-26T01:54:02.979-05:002010-11-26T01:54:02.979-05:00Can't we just log the whole map or fsync it t...Can't we just log the whole map or fsync it to disk only on checkpoints. That way it will be updated with the redo.<br /><br />The wal may not practical for huge databases but disk image would be fine. A 64TB DB would have 1GB visibility map.Unknownhttps://www.blogger.com/profile/14778054840532456513noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-12538077801812657162010-11-23T09:47:58.013-05:002010-11-23T09:47:58.013-05:00I just wrote a blog post on how InnoDB does thisI just wrote a <a href="http://mysqlha.blogspot.com/2010/11/how-are-index-only-scans-implemented-in.html" rel="nofollow">blog post</a> on how InnoDB does thisMark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-2755762466183100102010-11-23T09:10:59.670-05:002010-11-23T09:10:59.670-05:00This is good news to me.
There are a few details...This is good news to me. <br /><br />There are a few details on how InnoDB supports index only scans <a href="http://blogs.innodb.com/wp/2010/09/mysql-5-5-innodb-change-buffering" rel="nofollow">on the InnoDB blog</a>. It doesn't guarantee that scans will be index only but it makes it very likely that they will be.<br /><br />I think I will add code to my MySQL tree to count the number of times when it is and is not index only.Mark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-84976560058649136002010-11-22T17:14:07.334-05:002010-11-22T17:14:07.334-05:00This would be the very significant performance upg...This would be the very significant performance upgrade, especially for analytical queries.<br />In some cases, I have seen query sitting at 100% I/O doing random reads both on index and query.<br />So for DW-type of data when partitions are created by one process and then never (or seldom) modified, could we implement something like "read-only" tables or even read-only tablespaces, like in oracle? Then we wouldn't need to worry about visibility map for these read-only tables and there is still significant advantage for DW-type queries.<br />Thanks.Slavahttps://www.blogger.com/profile/09546623022761909519noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-54149876191354858732010-11-22T14:30:02.991-05:002010-11-22T14:30:02.991-05:00Interesting. How is this done in InnoDB's MVCC...Interesting. How is this done in InnoDB's MVCC architecture?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-5581564288419660832010-11-22T14:04:43.109-05:002010-11-22T14:04:43.109-05:00Robert,
It still doesn't strike me as a big p...Robert,<br /><br />It still doesn't strike me as a big problem compared with the rest of the patch. I suppose I should look at the archives and the code to get more details.Jeff Davishttp://thoughts.j-davis.comnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-71654144889443526612010-11-22T13:09:28.327-05:002010-11-22T13:09:28.327-05:00@Jeff Davis: Any action that can result in a visib...@Jeff Davis: Any action that can result in a visibility map getting cleared is already WAL-logged, and we can and already do clear the bit on redo. We currently don't XLOG setting the bit, so the PD_ALL_VISIBLE flag on the page can get out of sync with the copy in the visibility map, which is where things get sketchy: a subsequent update will see that PD_ALL_VISIBLE is unset on the page and will therefore not clear it in the visibility map either.<br /><br />To escape that, we'll need to WAL-log setting the visibility map bit, but doing that once per page would might be too expensive, and doing it for a range of pages seems complex.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-21319646444183670732010-11-22T11:49:17.051-05:002010-11-22T11:49:17.051-05:00"1. Make the visibility map crash-safe, so we..."1. Make the visibility map crash-safe, so we can rely on it for query execution. This is probably the hard part."<br /><br />Can you clarify?<br /><br />WAL logging the set/unset seems like it would solve that problem. And it doesn't immediately strike me as too expensive, because it's at the page level.Jeff Davishttp://thoughts.j-davis.comnoreply@blogger.com