2011/10/22 Andres Freund <and...@anarazel.de> > On Friday, October 21, 2011 08:14:12 PM Robert Haas wrote: > > On Fri, Oct 21, 2011 at 2:08 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > > Robert Haas <robertmh...@gmail.com> writes: > > >> On Fri, Oct 21, 2011 at 1:18 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > >>> I don't know why you'd imagine that touching an index is free, or > even > > >>> cheap, CPU-wise. The whole point of the index-only optimization is > to > > >>> avoid I/O. When you try it on a case where there's no I/O to be > saved, > > >>> and no shared-buffers contention to be avoided, there's no way it's > > >>> going to be a win. > > >> > > >> Well, call me naive, but I would have thought touching six times less > > >> data would make the operation run faster, not slower. > > > > > > It's not "touching six times less data". It's touching the exact same > > > number of tuples either way, just index tuples in one case and heap > > > tuples in the other. > > > > Yeah, but it works out to fewer pages. > But access to those is not sequential. I guess if you measure cache hit > ratios > the index scan will come out significantly worse. > > > Andres > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
"But access to those is not sequential" yes, I am agree. In my opinion the problem is that. If the query needs to scan all the b-tree index without to access the table rows, the better way to read the index is like sequential one, in fact , query like count(*) or other not need the data are in "order" so I think we could read all blocks (better, "only" the leaf blocks) without to touching too much the branch blocks. For example query like this : select column_a from table ; is better to read the data from indexes like sequential For query like this : select column_a from table order by column_a ; is better to read the data from indexes in range scan from root block to first branch blocks and their leaf blocks, so we could "save" the sorting. Mat
