Skip site navigation (1) Skip section navigation (2)

Re: Question about indexes

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Question about indexes
Date: 2004-01-30 11:31:17
Message-ID: 200401301131.i0UBVHU04169@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Greg Stark wrote:
> 
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> 
> > In any case, this discussion is predicated on the assumption that the
> > operations involving the bitmap are a significant fraction of the total
> > time, which I think is quite uncertain.  Until we build it and profile
> > it, we won't know that.
> 
> The other thought I had was that it would be difficult to tell when to follow
> this path. Since the main case where it wins is when the individual indexes
> aren't very selective but the combination is very selective, and we don't have
> inter-column correlation statistics ...

I like the idea of building in-memory bitmapped indexes.

In your example, if you are restricting on A and B, and have no A,B
index but an A index and B index, why wouldn't you always create an
in-memory bitmapped index from indexes A and B, unless index A hits only
a few rows.  In fact, from the optimizer statistics, you can guess on
how many bits you will hit from index A and index B, so we only have to
decide if it is better to take the more restrictive index and do heap
lookups for those, or scan the second index and then hit the heap.  The
only thing A,B combined statistics would tell you is how many heap
matches you will find.  The time to scan A and B indexes and create the
bitmap is already guessable from the single column statistics.

Also, what does an in-memory bitmapped index look like?  Is it:

	value:  bitmap...
	value:  bitmap...

with the values organized in a btree fashion?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-hackers by date

Next:From: Jeroen T. VermeulenDate: 2004-01-30 11:47:54
Subject: Re: Stupid question on Read Committed Isolation Level
Previous:From: Bruce MomjianDate: 2004-01-30 10:50:32
Subject: Re: Getting the results columns before execution

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group