Re: Confusion and Questions about blocks read

From: Jim Nasby <jim(at)nasby(dot)net>
To: Markus Schaber <schabi(at)logix-tt(dot)com>
Cc: "Pgsql-Performance ((E-mail))" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Confusion and Questions about blocks read
Date: 2006-09-27 03:08:35
Message-ID: FD014885-5492-4F44-8C2E-F77C85B4FDCF@nasby.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Sep 23, 2006, at 8:19 AM, Markus Schaber wrote:
> Btw, would it be feasible to enhance normal index scans by looking at
> all rows in the current table block whether they meet the query
> criteria, fetch them all, and blacklist the block for further
> revisiting
> during the same index scan?
>
> I think that, for non-sorted cases, this could improve index scans a
> little, but I don't know whether it's worth the effort, given that
> bitmap indidex scans exist.

The trade-off is you'd burn a lot more CPU on those pages. What might
be interesting would be collapsing bitmap scan data down to a page
level when certain conditions were met, such as if you're getting a
significant number of hits for a given page. There's probably other
criteria that could be used as well. One issue would be considering
the effects of other bitmap index operations; if you're ANDing a
bunch of scans together, you're likely to have far fewer tuples per
page coming out the backside, which means you probably wouldn't want
to burn the extra CPU to do full page scans.

BTW, I remember discussion at some point about ordering the results
of a bitmap scan by page/tuple ID, which would essentially do what
you're talking about. I don't know if it actually happened or not,
though.

If this is something that interests you, I recommend taking a look at
the code; it's generally not too hard to read through thanks to all
the comments.
--
Jim Nasby jimn(at)enterprisedb(dot)com
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

--
Jim Nasby jimn(at)enterprisedb(dot)com
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Jim Nasby 2006-09-27 03:14:15 Re: Update on high concurrency OLTP application and Postgres
Previous Message Junaili Lie 2006-09-26 23:27:41 Re: slow i/o