FW: Advice: Where could I be of help?

Forgot to cc' the list.

-----Original Message-----
From: Curtis Faith [mailto:curtis(at)galtair(dot)com]
Sent: Wednesday, October 02, 2002 10:59 PM
To: Tom Lane
Subject: RE: [HACKERS] Advice: Where could I be of help?

Tom,

Here are the things that I think look interesting:

1) Eliminate unchanged column indices:
Prevent index uniqueness checks when UPDATE does not modifying column

Small little task that will make a noticeable improvement. I've done this
before in a b* tree system, it had a huge impact. Should be pretty isolated.

2) Use indexes for min() and max() or convert to SELECT col FROM tab ORDER
BY col DESC LIMIT 1 if appropriate index exists and WHERE clause
acceptable - This will probably be a little more involved but I've done this
exact optimization in a SQL system 6 or 7 years ago.

3) General cache and i/o optimization:

Use bitmaps to fetch heap pages in sequential order

Based on my reading of the emails in [performance] it appears to me that
there might be huge potential in the caching system. I've worked on these
caches and there are some very non-intuitive interactions between database
type access and file systems that I believe offer good potential for
improvement. I'm basing this assessment on the assumption that the sorts of
improvements discussed in the [performance] emails have not been added in
subsequent releases.

Where does the current code stand? How are we currently doing cache flushing
in general and for indices in particular?

4) General index improvements including:
Order duplicate index entries by tid for faster heap lookups
Add FILLFACTOR to btree index creation

I've done the first one before and fill factor is pretty easy, as well.

5) Bitmaps:
Implement a bitmap index:
Use bitmaps to combine existing indexes
I've done something similar, it looks pretty interesting.

6) Improve concurrency of hash indexes (Neil Conway)- Probably more
exploration than implementation and fairly isolated problem.

Based on past experience, from a bang-for-buck perspective, I'd probably do
this in the numerical order. What do you think? I know what I like and can
do but I don't really know enough about PostgreSQL's performance weaknesses
yet.

What are we getting killed on?

- Curtis

-----Original Message-----
From: pgsql-hackers-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
Sent: Wednesday, October 02, 2002 6:55 PM
To: Curtis Faith
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Advice: Where could I be of help?

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I would read the developers corner stuff, the developers FAQ, pick a
> TODO item, and try a patch. It's that simple.

Yup. I'd also suggest starting with something relatively small and
localized (the nearby suggestion to fix IN/EXISTS, for example, is
probably not a good first project --- and anyway I was going to work
on that myself this month ;-)).

Neil Conway's thought of working on plpgsql seems a good one to me;
and as he says, there's lots of other possibilities. What do you
find interesting in the TODO list?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html