Re: [COMMITTERS] pgsql: Install some slightly realistic cost estimation

On 4/21/05, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>
> I believe Tom is completing this TODO item:
>
> * Allow non-bitmap indexes to be combined by creating bitmaps in memory
>

Thanks Bruce. That definitely answers my first question about lossy
index types. Sorry for asking a TODO answered question. [MENTAL
NOTE: Check TODOs as well as archives...]

I'm still curious about the implications of this (my) assumption:

> > It will also keep long lists of ORs from turning what would have been
> > a >10% index scan into a seq scan, yes? I suppose there is some
> > balance that needs to be calculated on
> > number-of-conditions-per-index-scan vs startup-cost-of-X-index-scans.
> > E.g., if you have table with 10M rows and an IN clause with 500
> > elements, it might be better to start 20 index scans with 25 condition
> > each instead of a single big index scan (what we do now if the cost
> > isn't too high), 500 index scans (what a simple "spin off a scan per
> > condition" algo would do, and what it _looks_ like the new code did to
> > your test query...), or a seq scan.
> >

Though I'm not sure if I was completely clear there...

And just to be clear, unless I'm just misunderstanding the TODO, it
looks like this:

> > unrelated indexes? Could this bitmapping code be used to combine
> > indexes from different tables, say in a large UNION or inherited table
> > setup?

should be true when the implementation is complete. The reason I ask
about the inherited table index bitmapping is that it might provide a
way to check for cross-partition UNIQUE constraints. Perhaps
something like:

ALTER TABLE base_table ADD CONSTRAINT col_global_uniq_constraint
GLOBALLY UNIQUE (unique_column);

That could use a bitmapped OR scan of indexes on "unique_column" on
the base table and all descendant tables to check for a unique value
across a "partitioned table". Hmmm... thinking more, I suppose it
could just to a quick scan of each index in order, so that might not
be useful.

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org