Thread: ACM Sigmod interview with Bruce Lindsay

I find this Q & A a bit amusing:

http://www.sigmod.org/sigmod/record/issues/0506/p71-column-winslet.pdf

----

Q. If you magically had enough extra time to do one additional thing at
work that you're not doing now, what would it be?

A. I think I would work on indexing a little harder.

Q. What aspect of indexing?

A. Oh, there are many aspects of indexing that I think need improvement.
I think we can do a better job in the searching. I think there are
exciting things to do in multi-dimensional indexing. I think there are a
lot more advanced ways that we can use database indexes for indexing on
columns that contain sequences or XML data, and for partial indexing.
With partial indexing, we would index only some of the rows, based on
some predicate. For example, we might not index the NULL values, or we
might index only those salaries greater than $100K.

----

How long has a partial index implementation been in PostgreSQL? I seem
to recall it being brought up to speed around six years ago. The
underlying code may have been in there for much, much longer...

Mike Mascari

Mike Mascari wrote:
> I find this Q & A a bit amusing:
>
> http://www.sigmod.org/sigmod/record/issues/0506/p71-column-winslet.pdf
>
> ----
>
> Q. If you magically had enough extra time to do one additional thing at
> work that you're not doing now, what would it be?
>
> A. I think I would work on indexing a little harder.
>
> Q. What aspect of indexing?
>
> A. Oh, there are many aspects of indexing that I think need improvement.
> I think we can do a better job in the searching. I think there are
> exciting things to do in multi-dimensional indexing. I think there are a
> lot more advanced ways that we can use database indexes for indexing on
> columns that contain sequences or XML data, and for partial indexing.
> With partial indexing, we would index only some of the rows, based on
> some predicate. For example, we might not index the NULL values, or we
> might index only those salaries greater than $100K.
>
> ----
>
> How long has a partial index implementation been in PostgreSQL? I seem
> to recall it being brought up to speed around six years ago. The
> underlying code may have been in there for much, much longer...

We have had partial indexes since the Berkeley days, per-1996, but we
have improved them quite a bit.

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Mike Mascari wrote:
>> How long has a partial index implementation been in PostgreSQL?

> We have had partial indexes since the Berkeley days, per-1996, but we
> have improved them quite a bit.

We inherited code for the feature from Berkeley, but it was broken for
some considerable period of time.  I find this in the CVS logs:

2001-07-16 01:06  tgl

    * doc/src/sgml/indices.sgml, doc/src/sgml/ref/create_index.sgml,
    src/backend/bootstrap/bootstrap.c, src/backend/catalog/index.c,
    src/backend/commands/command.c, src/backend/commands/indexcmds.c,
    src/backend/executor/execUtils.c,
    src/backend/optimizer/path/indxpath.c,
    src/backend/optimizer/util/pathnode.c,
    src/backend/parser/analyze.c, src/backend/parser/gram.y,
    src/backend/parser/keywords.c, src/backend/tcop/utility.c,
    src/backend/utils/adt/ruleutils.c,
    src/backend/utils/adt/selfuncs.c, src/bin/pg_dump/pg_dump.c,
    src/bin/pg_dump/pg_dump.h, src/include/catalog/index.h,
    src/include/catalog/pg_proc.h, src/include/config.h.in,
    src/include/commands/defrem.h, src/include/nodes/execnodes.h,
    src/include/utils/builtins.h,
    src/interfaces/ecpg/preproc/keywords.c,
    src/interfaces/ecpg/preproc/preproc.y,
    src/test/regress/expected/create_index.out,
    src/test/regress/expected/portals_p2.out,
    src/test/regress/expected/sanity_check.out,
    src/test/regress/expected/select.out,
    src/test/regress/sql/create_index.sql,
    src/test/regress/sql/portals_p2.sql,
    src/test/regress/sql/select.sql: Partial indexes work again,
    courtesy of Martijn van Oosterhout.  Note: I didn't force an
    initdb, figuring that one today was enough.  However, there is a
    new function in pg_proc.h, and pg_dump won't be able to dump
    partial indexes until you add that function.

This is reflected in the 7.2 release notes.

In the context of the interview, I agree it's pretty amusing --- I
would've thought the academic DBMS community would see partial indexes
as ancient news.

            regards, tom lane