Re: [HACKERS] the big picture for index-only scans

2011-09-27 Thread Robert Haas
On Sun, Aug 21, 2011 at 3:13 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: PS. Robert, the LOCKING section in the header comment of visibilitymap.c is out-of-date: it claims that the VM bit is cleared after releasing the lock on the heap page. Fixed, along with your other

Re: [HACKERS] the big picture for index-only scans

2011-08-24 Thread Gokulakannan Somasundaram
There are extensive comments on this in visibilitymap.c and, in heapam.c, heap_xlog_visible(). I went through the design again and again. I am convinced that this should not have any functional bugs and should not cause much performance issues. Nice thoughts on bypassing the WAL Logging.

Re: [HACKERS] the big picture for index-only scans

2011-08-21 Thread Heikki Linnakangas
On 21.08.2011 07:10, Gokulakannan Somasundaram wrote: d) In addition, currently there is no WAL Logging, while the bit is cleared, which would not be the case in future and hence the exclusive lock held on the visibility map is going to be held for a longer time. This is false and has

Re: [HACKERS] the big picture for index-only scans

2011-08-21 Thread Heikki Linnakangas
On 21.08.2011 07:41, Gokulakannan Somasundaram wrote: On Sat, Aug 20, 2011 at 4:57 AM, Gokulakannan Somasundaram gokul...@gmail.com wrote: by your argument, if WALInserLock is held for 't' seconds, you should definitely be holding visibility map lock for more than time frame 't'. Nope,

Re: [HACKERS] the big picture for index-only scans

2011-08-21 Thread Gokulakannan Somasundaram
The all_visible_cleared flag is included in the WAL record of the insert (or update or delete). Partial page writes are not a problem, because we always fetch the VM page and clear the bit, regardless of the LSN on the VM page. Two things a) First, my understanding of checkpoint is that it

Re: [HACKERS] the big picture for index-only scans

2011-08-21 Thread Gokulakannan Somasundaram
a) First, my understanding of checkpoint is that it flushes all the pages, that got changed below a particular LSN. If we are not having a LSN in the visibility map, how we will be sure, that a visibility map page is getting flushed/not? Please ignore this statement. I confused between the

Re: [HACKERS] the big picture for index-only scans

2011-08-21 Thread Robert Haas
On Sun, Aug 21, 2011 at 12:10 AM, Gokulakannan Somasundaram gokul...@gmail.com wrote: Consider the TPC-C benchmark. Currently on a four core box, Oracle does 29 transactions per minute. Say we are doing something around half of this. So a page should get quickly filled. If a vacuum runs and

Re: [HACKERS] the big picture for index-only scans

2011-08-20 Thread Gokulakannan Somasundaram
The above could already happen in 8.4, where the visibility map was introduced. The contention on the VM buffer would be just as bad whether you hold the heap page lock at the same time or not. I have not heard any complaints of contention on VM buffers. -- Heikki Linnakangas a) First

Re: [HACKERS] the big picture for index-only scans

2011-08-20 Thread Gokulakannan Somasundaram
Hmm, you have a point. If 100 backends simultaneously write to 100 different pages, and all of those pages are all-visible, then it's possible that they could end up fighting over the buffer content lock on the visibility map page. But why would you expect that to matter? In a heavily

Re: [HACKERS] the big picture for index-only scans

2011-08-20 Thread Gokulakannan Somasundaram
On Sat, Aug 20, 2011 at 4:48 PM, Gokulakannan Somasundaram gokul...@gmail.com wrote: The above could already happen in 8.4, where the visibility map was introduced. The contention on the VM buffer would be just as bad whether you hold the heap page lock at the same time or not. I have not

Re: [HACKERS] the big picture for index-only scans

2011-08-20 Thread Robert Haas
On Sat, Aug 20, 2011 at 4:48 AM, Gokulakannan Somasundaram gokul...@gmail.com wrote: a) First of all, it(Visibility Map) should have definitely affected the scalability of postgres in scenarios where in updates occur during a time batch window. May be the increase in speed of vacuums negate

Re: [HACKERS] the big picture for index-only scans

2011-08-20 Thread Robert Haas
On Sat, Aug 20, 2011 at 4:57 AM, Gokulakannan Somasundaram gokul...@gmail.com wrote: by your argument, if WALInserLock is held for 't' seconds, you should definitely be holding visibility map lock for more than time frame 't'. Nope, that's not how it works. Perhaps you should read the code.

Re: [HACKERS] the big picture for index-only scans

2011-08-20 Thread Robert Haas
On Sat, Aug 20, 2011 at 5:06 AM, Gokulakannan Somasundaram gokul...@gmail.com wrote: By your argument, we can say that no-one will create a index with a function like random(), time(), date(), broken operators etc. Its hard to imagine a index in which a a user will only want to insert and never

Re: [HACKERS] the big picture for index-only scans

2011-08-20 Thread Gokulakannan Somasundaram
I think that you have switched gears here and are in this paragraph talking about the 8.4-era visibility map changes rather than my recent work. There is zero evidence that those changes caused any performance problem. I've spent a large chunk of the last four months looking for

Re: [HACKERS] the big picture for index-only scans

2011-08-20 Thread Gokulakannan Somasundaram
On Sat, Aug 20, 2011 at 4:57 AM, Gokulakannan Somasundaram gokul...@gmail.com wrote: by your argument, if WALInserLock is held for 't' seconds, you should definitely be holding visibility map lock for more than time frame 't'. Nope, that's not how it works. Perhaps you should read the

Re: [HACKERS] the big picture for index-only scans

2011-08-20 Thread Gokulakannan Somasundaram
By your argument, we can say that no-one will create a index with a function like random(), time(), date(), broken operators etc. Its hard to imagine a index in which a a user will only want to insert and never select on it. The whole point of this optimization is to make index access

Re: [HACKERS] the big picture for index-only scans

2011-08-19 Thread Gokulakannan Somasundaram
Note that we already have the visibility map, and the accesses needed to update it are already there. Granted, we'll have to change the logic slightly to make it crash safe, but I don't expect that to add any meaningful overhead - the changes are going to be where the bits are set, ie.

Re: [HACKERS] the big picture for index-only scans

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 9:19 AM, Gokulakannan Somasundaram gokul...@gmail.com wrote: The fact that the proposal is for crash safe visibility map, to become a default package of any Postgresql table will definitely have wide ranging implications on OLTP performance. Well, that would certainly

Re: [HACKERS] the big picture for index-only scans

2011-08-19 Thread Bruce Momjian
Robert Haas wrote: I am happy to have pg_upgrade skip upgrading visibility map files --- it already has code to conditionally process them because they only exist in = 8.4: ? ? ? ?/* fsm/vm files added in PG 8.4 */ ? ? ? ?if (GET_MAJOR_VERSION(old_cluster.major_version) = 804) ? ? ?

Re: [HACKERS] the big picture for index-only scans

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 11:22 AM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: I am happy to have pg_upgrade skip upgrading visibility map files --- it already has code to conditionally process them because they only exist in = 8.4: ? ? ? ?/* fsm/vm files added in PG 8.4 */

Re: [HACKERS] the big picture for index-only scans

2011-08-19 Thread Gokulakannan Somasundaram
Well, that would certainly be alarming if true, but I don't think it is. As far as I can see, the overhead of making the visibility map crash-safe is just (1) a very small percentage increase in the work being done by VACUUM and (2) a slight possibility of extra work done by a foreground

Re: [HACKERS] the big picture for index-only scans

2011-08-19 Thread Gokulakannan Somasundaram
Well, that would certainly be alarming if true, but I don't think it is. As far as I can see, the overhead of making the visibility map crash-safe is just (1) a very small percentage increase in the work being done by VACUUM and (2) a slight possibility of extra work done by a foreground

Re: [HACKERS] the big picture for index-only scans

2011-08-19 Thread Heikki Linnakangas
On 19.08.2011 21:06, Gokulakannan Somasundaram wrote: If you are following the same design that Heikki put forward, then there is a problem with it in maintaining the bits in page and the bits in visibility map in sync, which we have already discussed. Are you referring to this:

Re: [HACKERS] the big picture for index-only scans

2011-08-19 Thread Gokulakannan Somasundaram
On Sat, Aug 20, 2011 at 2:25 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 19.08.2011 21:06, Gokulakannan Somasundaram wrote: If you are following the same design that Heikki put forward, then there is a problem with it in maintaining the bits in page and the bits in

Re: [HACKERS] the big picture for index-only scans

2011-08-19 Thread Gokulakannan Somasundaram
On Sat, Aug 20, 2011 at 2:51 AM, Gokulakannan Somasundaram gokul...@gmail.com wrote: On Sat, Aug 20, 2011 at 2:25 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 19.08.2011 21:06, Gokulakannan Somasundaram wrote: If you are following the same design that Heikki put

Re: [HACKERS] the big picture for index-only scans

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 2:51 PM, Gokulakannan Somasundaram gokul...@gmail.com wrote: On Sat, Aug 20, 2011 at 2:25 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 19.08.2011 21:06, Gokulakannan Somasundaram wrote: If you are following the same design that Heikki put

Re: [HACKERS] the big picture for index-only scans

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 4:02 PM, Robert Haas robertmh...@gmail.com wrote: Hmm, you have a point.  If 100 backends simultaneously write to 100 different pages, and all of those pages are all-visible, then it's possible that they could end up fighting over the buffer content lock on the

Re: [HACKERS] the big picture for index-only scans

2011-08-19 Thread Heikki Linnakangas
On 19.08.2011 23:02, Robert Haas wrote: On Fri, Aug 19, 2011 at 2:51 PM, Gokulakannan Somasundaram gokul...@gmail.com wrote: won't it make the 99 sessions wait for that visibility map while holding the exclusive lock on the 99 heap pages? Hmm, you have a point. If 100 backends

Re: [HACKERS] the big picture for index-only scans

2011-08-19 Thread Heikki Linnakangas
On 19.08.2011 23:17, Robert Haas wrote: On Fri, Aug 19, 2011 at 4:02 PM, Robert Haasrobertmh...@gmail.com wrote: Hmm, you have a point. If 100 backends simultaneously write to 100 different pages, and all of those pages are all-visible, then it's possible that they could end up fighting over

Re: [HACKERS] the big picture for index-only scans

2011-08-18 Thread Robert Haas
On Tue, May 10, 2011 at 8:19 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: Any thoughts welcome. ?Incidentally, if anyone else feels like working on this, feel free to let me know and I'm happy to step away, from all of it or from whatever part someone else wants to tackle.

Re: [HACKERS] the big picture for index-only scans

2011-06-19 Thread Cédric Villemain
2011/5/14 Robert Haas robertmh...@gmail.com: On Fri, May 13, 2011 at 6:34 PM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: If reviewers agree it is safe and benchmarks make evidences that no basic performance  issue are raised, then let's see if next items have blockers or can be

Re: [HACKERS] the big picture for index-only scans

2011-06-19 Thread Cédric Villemain
2011/5/11 Bruce Momjian br...@momjian.us: Cédric Villemain wrote: 2011/5/10 Kevin Grittner kevin.gritt...@wicourts.gov: Simon Riggs si...@2ndquadrant.com wrote: The typical speed up for non-covered indexes will come when we access a very large table (not in cache) via an index scan that is

Re: [HACKERS] the big picture for index-only scans

2011-06-19 Thread Robert Haas
On Sun, Jun 19, 2011 at 10:44 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: and recent stuff on VM will allow us to move forward it seems ! Yep, looks promising. The heap_hot_search_buffer refactoring patch is related as well. -- Robert Haas EnterpriseDB:

Re: [HACKERS] the big picture for index-only scans

2011-06-19 Thread Robert Haas
On Sun, Jun 19, 2011 at 11:12 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: Good point --- we would be making plan decisions based on the visibility map coverage.  The big question is whether visibility map changes are more dynamic than the values we already plan against, like

Re: [HACKERS] the big picture for index-only scans

2011-06-19 Thread Cédric Villemain
2011/6/19 Robert Haas robertmh...@gmail.com: On Sun, Jun 19, 2011 at 11:12 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: Good point --- we would be making plan decisions based on the visibility map coverage.  The big question is whether visibility map changes are more dynamic

Re: [HACKERS] the big picture for index-only scans

2011-06-19 Thread Florian Pflug
On Jun19, 2011, at 20:40 , Robert Haas wrote: 2. Since VACUUM and ANALYZE often run together, we will be estimating the percentage of rows on all-visible pages just at the time when that percentage is highest. This is not exactly wonderful, either... Hm, doesn't autovacuum run ANALYZE quite a

Re: [HACKERS] the big picture for index-only scans

2011-06-19 Thread Robert Haas
On Sun, Jun 19, 2011 at 5:10 PM, Florian Pflug f...@phlo.org wrote: On Jun19, 2011, at 20:40 , Robert Haas wrote: 2. Since VACUUM and ANALYZE often run together, we will be estimating the percentage of rows on all-visible pages just at the time when that percentage is highest.  This is not

Re: [HACKERS] the big picture for index-only scans

2011-06-19 Thread Florian Pflug
On Jun19, 2011, at 23:16 , Robert Haas wrote: On Sun, Jun 19, 2011 at 5:10 PM, Florian Pflug f...@phlo.org wrote: On Jun19, 2011, at 20:40 , Robert Haas wrote: 2. Since VACUUM and ANALYZE often run together, we will be estimating the percentage of rows on all-visible pages just at the time

Re: [HACKERS] the big picture for index-only scans

2011-06-19 Thread Robert Haas
On Sun, Jun 19, 2011 at 7:59 PM, Florian Pflug f...@phlo.org wrote: On Jun19, 2011, at 23:16 , Robert Haas wrote: On Sun, Jun 19, 2011 at 5:10 PM, Florian Pflug f...@phlo.org wrote: On Jun19, 2011, at 20:40 , Robert Haas wrote: 2. Since VACUUM and ANALYZE often run together, we will be

Re: [HACKERS] the big picture for index-only scans

2011-05-13 Thread Cédric Villemain
2011/5/11 Robert Haas robertmh...@gmail.com: On Wed, May 11, 2011 at 3:17 AM, Simon Riggs si...@2ndquadrant.com wrote: Completely agree, but why are you saying that to me? When Tom asks me why I suggest something, nobody tells him its a free software project etc What is the difference

Re: [HACKERS] the big picture for index-only scans

2011-05-13 Thread Robert Haas
On Fri, May 13, 2011 at 6:34 PM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: Will you be able to do some ? or can you propose a simple process to do efficient benchmark of the patch ? I will probably do some benchmarking at some point, unless someone else goes nuts and makes it

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Simon Riggs
On Wed, May 11, 2011 at 12:54 AM, Greg Stark gsst...@mit.edu wrote: On a separate note though, Simon, I don't know what you mean by we normally start with a problem. It's an free software project and people are free to work on whatever interests them whether that's because it solves a problem

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Simon Riggs
On Wed, May 11, 2011 at 1:47 AM, Bruce Momjian br...@momjian.us wrote: Greg Stark wrote: On a separate note though, Simon, I don't know what you mean by we normally start with a problem. It's an free software project and people are free to work on whatever interests them whether that's

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Simon Riggs
On Wed, May 11, 2011 at 2:34 AM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: So, what do we need in order to find our way to index-only scans? 1. The visibility map needs to be crash-safe.  The basic idea of index-only scans is that, instead of checking the heap to find out

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Heikki Linnakangas
On 10.05.2011 20:15, Simon Riggs wrote: On Tue, May 10, 2011 at 5:17 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Simon Riggssi...@2ndquadrant.com wrote: This topic has been discussed many times, yet I have never seen an assessment that explains WHY we would want to do index-only

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Nicolas Barbier
2011/5/11, Bruce Momjian br...@momjian.us: FYI, because the visibility map is only one _bit_ per page, it is 8000 * 8 or 64k times smaller than the heap, e.g. one 8k page covers 64MB of heap pages. Actually, that would be one 8kB block covers 512MB of heap: 1 block of visibility map (8kB) =

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Cédric Villemain
2011/5/11 Heikki Linnakangas heikki.linnakan...@enterprisedb.com: On 10.05.2011 20:15, Simon Riggs wrote: On Tue, May 10, 2011 at 5:17 PM, Kevin Grittner kevin.gritt...@wicourts.gov  wrote: Simon Riggssi...@2ndquadrant.com  wrote: This topic has been discussed many times, yet I have never

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Cédric Villemain
2011/5/10 Kevin Grittner kevin.gritt...@wicourts.gov: Simon Riggs si...@2ndquadrant.com wrote: The typical speed up for non-covered indexes will come when we access a very large table (not in cache) via an index scan that is smaller than a bitmapindex scan. Will we be able to gauge

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Robert Haas
On Tue, May 10, 2011 at 9:34 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: So, what do we need in order to find our way to index-only scans? 1. The visibility map needs to be crash-safe.  The basic idea of index-only scans is that, instead of checking the heap to find out

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Robert Haas
On Tue, May 10, 2011 at 10:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: That will be true only if you intentionally ignore the points Greg raised.  If the table isn't entirely ALL_VISIBLE, then the choice of index will determine the ordering of the actual table probes that occur. There could be

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Robert Haas
On Wed, May 11, 2011 at 3:17 AM, Simon Riggs si...@2ndquadrant.com wrote: Completely agree, but why are you saying that to me? When Tom asks me why I suggest something, nobody tells him its a free software project etc What is the difference here? We're now 40 emails in this thread, and

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Bruce Momjian
Nicolas Barbier wrote: 2011/5/11, Bruce Momjian br...@momjian.us: FYI, because the visibility map is only one _bit_ per page, it is 8000 * 8 or 64k times smaller than the heap, e.g. one 8k page covers 64MB of heap pages. Actually, that would be one 8kB block covers 512MB of heap: 1

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Bruce Momjian
Simon Riggs wrote: On Wed, May 11, 2011 at 1:47 AM, Bruce Momjian br...@momjian.us wrote: Greg Stark wrote: On a separate note though, Simon, I don't know what you mean by we normally start with a problem. It's an free software project and people are free to work on whatever interests

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Bruce Momjian
C?dric Villemain wrote: 2011/5/10 Kevin Grittner kevin.gritt...@wicourts.gov: Simon Riggs si...@2ndquadrant.com wrote: The typical speed up for non-covered indexes will come when we access a very large table (not in cache) via an index scan that is smaller than a bitmapindex scan. Will we

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Kevin Grittner
Bruce Momjian br...@momjian.us wrote: The very fact that Kevin and yourself bring up different reasons for why we need this feature makes me nervous. Yes, no question. For count(*), you don't care about the indexed values, only the count, while for Kevin's case you are reading values

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Tom Lane
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 10.05.2011 20:15, Simon Riggs wrote: I can picture that. Regrettably, I can also picture the accesses to the visibility map, the maintenance operations on the VM that are needed for this and the contention that both of those

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Bruce Momjian
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 10.05.2011 20:15, Simon Riggs wrote: I can picture that. Regrettably, I can also picture the accesses to the visibility map, the maintenance operations on the VM that are needed for this and the contention

Re: [HACKERS] the big picture for index-only scans

2011-05-11 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: I think Simon's point is that showing a gain on specific test cases isn't a sufficient argument. Ah, if that's what he's been trying to get at, I'm curious who disagrees with that. I wouldn't have thought anyone on this list would. What we need to know

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Robert Haas
On Mon, May 9, 2011 at 10:36 PM, Merlin Moncure mmonc...@gmail.com wrote: 1. The visibility map needs to be crash-safe.  The basic idea of index-only scans is that, instead of checking the heap to find out whether each tuple is visible, we first check the visibility map.  If the visibility map

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Merlin Moncure
On Tue, May 10, 2011 at 8:22 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, May 9, 2011 at 10:36 PM, Merlin Moncure mmonc...@gmail.com wrote: 1. The visibility map needs to be crash-safe.  The basic idea of index-only scans is that, instead of checking the heap to find out whether each

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Cédric Villemain
2011/5/10 Robert Haas robertmh...@gmail.com: So, what do we need in order to find our way to index-only scans? 3. Statistics.  I believe that in order to accurately estimate the cost of an index-only scan, we're going to need to know the fraction of tuples that are on pages whose visibility

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Robert Haas
On Tue, May 10, 2011 at 10:58 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: ANALYZE can do the stats job for 'free' on the pages it collects anyway. So that looks like a good idea. I believe the really lazy vacuum is another topic; even if it will improve the performance of the

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Cédric Villemain
2011/5/10 Robert Haas robertmh...@gmail.com: On Tue, May 10, 2011 at 10:58 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: ANALYZE can do the stats job for 'free' on the pages it collects anyway. So that looks like a good idea. I believe the really lazy vacuum is another topic;

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Simon Riggs
On Tue, May 10, 2011 at 3:25 AM, Robert Haas robertmh...@gmail.com wrote: So, what do we need in order to find our way to index-only scans? 1. The visibility map needs to be crash-safe.  The basic idea of index-only scans is that, instead of checking the heap to find out whether each tuple

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Cédric Villemain
The temptation is high to estimate the cost of an index_scan(only) + ordered(by ctid) table pages fetch if heap required. (this is what I understood from heikki suggestion 3-4. and it makes sense). It may be easier to implement both at once but I didn't find the branch in the Heikki's git

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Kevin Grittner
Simon Riggs si...@2ndquadrant.com wrote: This topic has been discussed many times, yet I have never seen an assessment that explains WHY we would want to do index-only scans. In databases with this feature, it's not too unusual for a query which uses just an index to run one or more orders

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Robert Haas
On Tue, May 10, 2011 at 11:27 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 2011/5/10 Robert Haas robertmh...@gmail.com: On Tue, May 10, 2011 at 10:58 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: ANALYZE can do the stats job for 'free' on the pages it collects

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: Simon Riggs si...@2ndquadrant.com wrote: This topic has been discussed many times, yet I have never seen an assessment that explains WHY we would want to do index-only scans. In databases with this feature, it's not too unusual for a query

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Robert Haas
On Tue, May 10, 2011 at 12:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Simon Riggs si...@2ndquadrant.com wrote: This topic has been discussed many times, yet I have never seen an assessment that explains WHY we would want to do index-only scans.

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Simon Riggs
On Tue, May 10, 2011 at 5:17 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Simon Riggs si...@2ndquadrant.com wrote: This topic has been discussed many times, yet I have never seen an assessment that explains WHY we would want to do index-only scans. In databases with this feature,

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Simon Riggs si...@2ndquadrant.com wrote: This topic has been discussed many times, yet I have never seen an assessment that explains WHY we would want to do index-only scans. In databases with this

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Greg Stark
On Tue, May 10, 2011 at 5:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: It's already the case that we'll flip over to a bitmap indexscan, and thus get rid of most/all of the random page accesses, in situations where this is likely to be a big win.  Pointing to the performance difference in

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Simon Riggs
On Tue, May 10, 2011 at 6:25 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: ... but I share Simon's desire to see some proof before anything gets committed. And we agree there.  In fact, I can't think of anyone in the community who doesn't want to see that for *any* purported

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Kevin Grittner
Simon Riggs si...@2ndquadrant.com wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: ... but I share Simon's desire to see some proof before anything gets committed. And we agree there. In fact, I can't think of anyone in the community who doesn't want to see that for *any*

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Simon Riggs
On Tue, May 10, 2011 at 8:35 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Simon Riggs si...@2ndquadrant.com wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: ... but I share Simon's desire to see some proof before anything gets committed. And we agree there.  In fact, I

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Kevin Grittner
Simon Riggs si...@2ndquadrant.com wrote: Normally, others come forward with the why? when? questions and it feels like there's a bit of groupthink going on here. This looks to me like its being approached like it was a feature, but it looks to me like a possible optimisation, so suggest we

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Greg Stark
On Wed, May 11, 2011 at 12:14 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: The problem is that there are regular and fairly frequent complaints on the list about queries which run slower than people expect To be fair about 3/4 of them were actually complaining about the lack of some

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Bruce Momjian
Robert Haas wrote: Any thoughts welcome. ?Incidentally, if anyone else feels like working on this, feel free to let me know and I'm happy to step away, from all of it or from whatever part someone else wants to tackle. ?I'm mostly working on this because it's something that I think we

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Bruce Momjian
Greg Stark wrote: On a separate note though, Simon, I don't know what you mean by we normally start with a problem. It's an free software project and people are free to work on whatever interests them whether that's because it solves a problem they have, helps a client who's paying them, or

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Greg Stark
On Wed, May 11, 2011 at 1:47 AM, Bruce Momjian br...@momjian.us wrote: Isn't speeding up COUNT(*) a sufficient case because it will not have to touch the heap in many cases? Putting aside the politics questions, count(*) is an interesting case -- it exposes some of the unanswered questions

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Bruce Momjian
Greg Stark wrote: On Wed, May 11, 2011 at 1:47 AM, Bruce Momjian br...@momjian.us wrote: Isn't speeding up COUNT(*) a sufficient case because it will not have to touch the heap in many cases? Putting aside the politics questions, count(*) is an interesting case -- it exposes some of the

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Bruce Momjian
Robert Haas wrote: So, what do we need in order to find our way to index-only scans? 1. The visibility map needs to be crash-safe. The basic idea of index-only scans is that, instead of checking the heap to find out whether each tuple is visible, we first check the visibility map. If the

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Bruce Momjian
Greg Stark wrote: On Wed, May 11, 2011 at 1:47 AM, Bruce Momjian br...@momjian.us wrote: Isn't speeding up COUNT(*) a sufficient case because it will not have to touch the heap in many cases? Putting aside the politics questions, count(*) is an interesting case -- it exposes some of the

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Tom Lane
Bruce Momjian br...@momjian.us writes: Greg Stark wrote: Putting aside the politics questions, count(*) is an interesting case -- it exposes some of the unanswered questions about index-only scans. The reason select count(*) might win would be because we could pick any index and do an index

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Bruce Momjian
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Greg Stark wrote: Putting aside the politics questions, count(*) is an interesting case -- it exposes some of the unanswered questions about index-only scans. The reason select count(*) might win would be because we could pick

Re: [HACKERS] the big picture for index-only scans

2011-05-10 Thread Jesper Krogh
On 2011-05-11 01:54, Greg Stark wrote: To be fair about 3/4 of them were actually complaining about the lack of some global materialized cache of the aggregate value. Covering index-only scans are only going to be a linear speedup no matter how large the factor it's not going to turn select

[HACKERS] the big picture for index-only scans

2011-05-09 Thread Robert Haas
So, what do we need in order to find our way to index-only scans? 1. The visibility map needs to be crash-safe. The basic idea of index-only scans is that, instead of checking the heap to find out whether each tuple is visible, we first check the visibility map. If the visibility map bit is

Re: [HACKERS] the big picture for index-only scans

2011-05-09 Thread Merlin Moncure
On Mon, May 9, 2011 at 10:25 PM, Robert Haas robertmh...@gmail.com wrote: So, what do we need in order to find our way to index-only scans? 1. The visibility map needs to be crash-safe.  The basic idea of index-only scans is that, instead of checking the heap to find out whether each tuple is