Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
Updated TODO:
* Allow the creation of bitmap indexes which can be quickly combined
with other bitmap indexes
This TODO item description is fundamentally misleading.
The point was *not* about making bitmap indexes, which on
Updated TODO:
* Allow the creation of bitmap indexes which can be quickly combined
with other bitmap indexes
Bitmap indexes index single columns that can be combined with other bitmap
indexes to dynamically create a composite index to match a specific query.
Each index is a bitmap, and
Bruce Momjian [EMAIL PROTECTED] writes:
Updated TODO:
* Allow the creation of bitmap indexes which can be quickly combined
with other bitmap indexes
This TODO item description is fundamentally misleading.
The point was *not* about making bitmap indexes, which on its face
suggests a
On K, 2004-10-27 at 00:58, Andre Maasikas wrote:
Hannu Krosing wrote:
the per-page clustering would make sure that all the tuples would be on
1 (or on a few) pages.
I understand that You can cluster on one column, but how do you do it for
indexes on other columns?
Thanks to PostgreSQL's
Hannu Krosing [EMAIL PROTECTED] writes:
so if I change foo=1 to foo=2 on a tuple that has bar=2 and baz=3 then
the updated tuple will go to a page for which foo=2, bar=2 and baz=3.
if no such page has enough free space left (found by anding bitmaps for
foo=2, bar=2 and baz=3 and FSM) then
Hi,
On Wed, Oct 27, 2004 at 10:13:56AM -0400, Greg Stark wrote:
There's a logical separation between the idea of index methods and table
storage mechanisms. Trying to implement something like this that breaks that
abstraction will only make things far more confusing.
I think what you're
Greg Stark wrote:
I think what you're trying to accomplish is better accomplished through
partitioned tables. Then the user can decide which keys to use to partition
the data and the optimizer can use the data to completely exclude some
partitions from consideration. And it wouldn't interfere with
Mark Kirkwood wrote:
I think that bitmap indexes provide a flexible may to get fact access
to the result set
that should be *fast* access tosorry
---(end of broadcast)---
TIP 6: Have you searched our list archives?
On K, 2004-10-20 at 03:03, Simon Riggs wrote:
Well, thats the best one yet. That's the solution, if ever I heard it.
The reduction in bitmap size makes their use much safer. Size matters, since
we're likely to start using these techniques on very large databases, which
imply obviously have
On K, 2004-10-20 at 01:52, Mark Kirkwood wrote:
I don't believe that read only is required. The update/insert
performance impact of bimap indexes is however very high (in Oracle's
implementation anyway) - to the point where many sites drop them before
adding in new data, and recreated 'em
Hannu Krosing [EMAIL PROTECTED] writes:
I repeat here my earlier proposal of making the bitmap indexes
page-level and clustering data automatically on AND of all defined
bitmap indexes.
The problem with page-level bitmaps is that they could be much less effective.
Consider a query like
On T, 2004-10-26 at 18:42, Greg Stark wrote:
Hannu Krosing [EMAIL PROTECTED] writes:
I repeat here my earlier proposal of making the bitmap indexes
page-level and clustering data automatically on AND of all defined
bitmap indexes.
The problem with page-level bitmaps is that they could
On T, 2004-10-26 at 23:53, Hannu Krosing wrote:
On T, 2004-10-26 at 18:42, Greg Stark wrote:
Hannu Krosing [EMAIL PROTECTED] writes:
I repeat here my earlier proposal of making the bitmap indexes
page-level and clustering data automatically on AND of all defined
bitmap indexes.
Hannu Krosing wrote:
the per-page clustering would make sure that all the tuples would be on
1 (or on a few) pages.
I understand that You can cluster on one column, but how do you do it for
indexes on other columns?
BTW, lossy variants also lose count(), group by only from index
and what comes to
On Tue, Oct 19, 2004 at 07:38:49PM -0300, Alvaro Herrera wrote:
Huh, you are wrong. At least btree does index null values, and one
other index method does too. The other two index methods don't. What
doesn't work is using an index with the IS NULL construct, because it's
not an operator.
Gavin == Gavin Sherry [EMAIL PROTECTED] writes:
Gavin I'm uncertain about the potential benefit of
Gavin this. Isn't/shouldn't the effects of caching be assisting
Gavin us here?
It all depends on how large your table is, and how busy the system is
(other pressures on the cache).
Mark Kirkwood wrote
Tom Lane wrote:
I believe that the term bitmap index is also used with a different
meaning wherein it actually does describe a particular kind of on-disk
index structure, with one bit per table row.
IMHO building in-memory bitmaps (the first idea) is a very good idea
On Tue, Oct 19, 2004 at 11:22:31PM +0100, Simon Riggs wrote:
I was thinking about this recently, then realised that building the bitmap
would not be as easily, since PostgreSQL doesn't index null values. That
would mean that the sets of CTIDs in each index would be disjoint. My
thinking about
Simon Riggs wrote:
I believe that the benefit of on-disk bitmap indexes is supposed to be
reduced storage size (compared to btree).
The main problem is the need for the table to be read-only. Until we have
partitioning, we wouldn't be able to easily guarantee parts of a table as
being
Simon Riggs [EMAIL PROTECTED] writes:
I was thinking about this recently, then realised that building the bitmap
would not be as easily, since PostgreSQL doesn't index null values.
As Alvaro already pointed out, this statement is bogus; and I'm not sure
what it has to do with the topic anyway.
Tom,
I've been taking bitmap to be a rather handwavy way of saying a
compact representation of sets of CTIDs that is readily amenable to
being ANDed and ORed with other sets.
Well, actually I think we're talking about two different features:
1) a way to use more than one index per
On Tue, 19 Oct 2004, Josh Berkus wrote:
Tom,
I've been taking bitmap to be a rather handwavy way of saying a
compact representation of sets of CTIDs that is readily amenable to
being ANDed and ORed with other sets.
Well, actually I think we're talking about two different features:
1)
Tom Lane
Simon Riggs [EMAIL PROTECTED] writes:
How would you dynamically build the bit maps from the indexes?
Or would you:
- copy aside and sort the indexes on CTID
- merge join them all to find matching CTIDs
- probe into the main table
I've been taking bitmap to be a rather
Alvaro Herrera
On Tue, Oct 19, 2004 at 11:22:31PM +0100, Simon Riggs wrote:
I was thinking about this recently, then realised that building the
bitmap
would not be as easily, since PostgreSQL doesn't index null values. That
would mean that the sets of CTIDs in each index would be disjoint.
Tom == Tom Lane [EMAIL PROTECTED] writes:
Tom One huge advantage is that the actual heap visiting becomes
Tom efficient, eg you never visit the same page more than once.
Tom (What you lose is the ability to retrieve data in index
Tom order, so this isn't a replacement for
On Tue, 19 Oct 2004, Sailesh Krishnamurthy wrote:
Tom == Tom Lane [EMAIL PROTECTED] writes:
Tom One huge advantage is that the actual heap visiting becomes
Tom efficient, eg you never visit the same page more than once.
Tom (What you lose is the ability to retrieve data in index
Tom Lane wrote:
I believe that the term bitmap index is also used with a different
meaning wherein it actually does describe a particular kind of on-disk
index structure, with one bit per table row.
IMHO building in-memory bitmaps (the first idea) is a very good idea to
pursue for Postgres. I'm
Alvaro Herrera [EMAIL PROTECTED] writes:
On Thu, Oct 14, 2004 at 11:08:54PM +0200, Yann Michel wrote:
BTW: Is there any more documented CVS-version available? I mean it would
be really nice to read some comments from time to time or at least more
comments about each function/method's purpose
Hi Tom,
On Fri, Oct 15, 2004 at 11:27:05AM -0400, Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
On Thu, Oct 14, 2004 at 11:08:54PM +0200, Yann Michel wrote:
BTW: Is there any more documented CVS-version available? I mean it would
be really nice to read some comments from time to
create index people_male_gay_ix on people (city) where gender = 'male' and
orientation = 'gay';
You've forgotten part of my premise (based on a real case I discussed on IRC)
that there are EIGHTEEN criteria columns.
That is why I said maybe :-) Whether it helps depends on the number of
Hi,
On Sat, Oct 09, 2004 at 01:31:36PM -0400, Chris Browne wrote:
The most nearly comparable thing is be the notion of partial
indexes, where, supposing you had 60 region codes (e.g. - 50 US
states, 10 Canadian provinces), you might set up indices thus:
[...]
The partial indexes will
On Thu, Oct 14, 2004 at 11:08:54PM +0200, Yann Michel wrote:
BTW: Is there any more documented CVS-version available? I mean it would
be really nice to read some comments from time to time or at least more
comments about each function/method's purpose or functionality.
Huh, the code is
On Sun, 2004-10-10 at 03:36, Chris Browne wrote:
There are doubtless cases where the optimizer won't use them where it
would be plausible to do so; that suggests, to me, possibilities for
enhancing the optimizer.
Speaking of which, if anyone has any examples of queries for which we
ought to be
The most nearly comparable thing is be the notion of partial
indexes, where, supposing you had 60 region codes (e.g. - 50 US
states, 10 Canadian provinces), you might set up indices thus:
For example, imagine you have a table on a dating website with 18 columns
representing 18 different
On K, 2004-10-13 at 00:09, Greg Stark wrote:
Josh Berkus [EMAIL PROTECTED] writes:
SELECT * FROM people WHERE orientation = 'gay' AND gender = 'male' AND city =
'San Francisco';
There are actually two TODOs here.
1) a bitmap scan that would be usable with any type of index. The tuple
Josh Berkus schrieb:
The most nearly comparable thing is be the notion of partial
indexes, where, supposing you had 60 region codes (e.g. - 50 US
states, 10 Canadian provinces), you might set up indices thus:
I'm afraid that you're mistaken about the functionality of bitmap indexes.
The purpose
Zeugswetter Andreas DAZ SD [EMAIL PROTECTED] writes:
Workable examples for useful partitioned indexes, that help here are:
create index people_male_ix on people (city) where gender = 'male';
create index people_gay_ix on people (city) where orientation = 'gay';
create index people_male_gay_ix
Andreas,
I think bitmap indexes do have valid use cases, but partitioned indexes
are really a wonderful feature with a lot of use cases,
Sure, no question. That's why we have them.
maybe including
this one.
Nope, not at all.
Workable examples for useful partitioned indexes, that help
Josh Berkus [EMAIL PROTECTED] writes:
BTW, Tom, I was talking to Sean last night and he was saying that our
current planner cost calculations assume that a 2-column index fetch
will be twice as expensive as a 1-column index fetch. Is this right?
No.
regards, tom lane
[EMAIL PROTECTED] (Josh Berkus) writes:
Lots of people have talked about it but I don't know anyone coding it.
I would love to have bitmap indexes in Postgres, as would a lot of other
community members. However, they are far from trivial to code. Are you
offering to help?
I'm curious
[EMAIL PROTECTED] (Yann Michel) writes:
On Fri, Oct 08, 2004 at 10:09:18AM +0100, Dave Page wrote:
I think what Reini was asking was why do you think you need bitmap
indexes as opposed to any existing type?
due to I'm developing a datawarehousing application we have lots of
fact-data in our
Josh Berkus [EMAIL PROTECTED] writes:
SELECT * FROM people WHERE orientation = 'gay' AND gender = 'male' AND city =
'San Francisco';
There are actually two TODOs here.
1) a bitmap scan that would be usable with any type of index. The tuple
locations can be read in for each criteria and
Josh Berkus [EMAIL PROTECTED] writes:
The Bitmap index allows the query executor to use several indexes on
the same operation, comparing them and selecting rows where they
overlap like a Venn diagram.
Note that what Josh is describing is not really a distinct index type,
but a different way of
Josh Berkus wrote:
Chris,
The most nearly comparable thing is be the notion of partial
indexes, where, supposing you had 60 region codes (e.g. - 50 US
states, 10 Canadian provinces), you might set up indices thus:
I'm afraid that you're mistaken about the functionality of bitmap indexes.
The
Hi,
On Thu, Oct 07, 2004 at 06:54:15PM -0400, Bruce Momjian wrote:
I'd like to know if there are any plans on introducing bitmap indexes
into postgresql. I think this could mean a big performance improvement
especially for datawarehousing applications. I know that there is an
index type
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Yann Michel
Sent: 08 October 2004 08:28
To: Reini Urban
Cc: [EMAIL PROTECTED]
Subject: Re: [HACKERS] plans for bitmap indexes?
I don't know what this means to my questin, but anyway
Hi,
On Fri, Oct 08, 2004 at 10:09:18AM +0100, Dave Page wrote:
I think what Reini was asking was why do you think you need bitmap
indexes as opposed to any existing type?
due to I'm developing a datawarehousing application we have lots of
fact-data in our central fact-table. As I know how to
On R, 2004-10-08 at 12:45, Yann Michel wrote:
Hi,
On Fri, Oct 08, 2004 at 10:09:18AM +0100, Dave Page wrote:
I think what Reini was asking was why do you think you need bitmap
indexes as opposed to any existing type?
due to I'm developing a datawarehousing application we have lots of
On Fri, 8 Oct 2004, Yann Michel wrote:
Hi,
On Fri, Oct 08, 2004 at 10:09:18AM +0100, Dave Page wrote:
I think what Reini was asking was why do you think you need bitmap
indexes as opposed to any existing type?
due to I'm developing a datawarehousing application we have lots of
fact-data in our
Yann,
Lots of people have talked about it but I don't know anyone coding it.
I would love to have bitmap indexes in Postgres, as would a lot of other
community members. However, they are far from trivial to code. Are you
offering to help?
--
Josh Berkus
Aglio Database Solutions
San
Hi Josh,
On Fri, Oct 08, 2004 at 09:59:41AM -0700, Josh Berkus wrote:
Lots of people have talked about it but I don't know anyone coding it.
I would love to have bitmap indexes in Postgres, as would a lot of other
community members. However, they are far from trivial to code. Are you
Yann,
I'd like to help you, but I think, that my C-Experience is not good
enough for beeing able to. I mean, I coded some C-stuff and I know how
bitmap indexes (should) work but I guess that this won't be enough. In
addidtion I never took a look into postgresql's sources.
Well, there's no
Hi Josh,
On Fri, Oct 08, 2004 at 10:18:27AM -0700, Josh Berkus wrote:
I'd like to help you, but I think, that my C-Experience is not good
enough for beeing able to. I mean, I coded some C-stuff and I know how
bitmap indexes (should) work but I guess that this won't be enough. In
Yann == Yann Michel [EMAIL PROTECTED] writes:
Yann O.K. I downloaded it :-) We will see if and how I can
Yann help
FYI .. in case you aren't aware already:
http://portal.acm.org/citation.cfm?id=98720
--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh
Yann Michel schrieb:
I'd like to know if there are any plans on introducing bitmap indexes
into postgresql. I think this could mean a big performance improvement
especially for datawarehousing applications. I know that there is an
index type hash but I don't know how both types are comparable due
Yann Michel wrote:
Hi,
I'd like to know if there are any plans on introducing bitmap indexes
into postgresql. I think this could mean a big performance improvement
especially for datawarehousing applications. I know that there is an
index type hash but I don't know how both types are
56 matches
Mail list logo
