Обсуждение: FW: Advice: Where could I be of help?

Forgot to cc' the list.

-----Original Message-----
From: Curtis Faith [mailto:curtis@galtair.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 lookupsAdd FILLFACTOR to
btreeindex 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@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Wednesday, October 02, 2002 6:55 PM
To: Curtis Faith
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Advice: Where could I be of help?


Bruce Momjian <pgman@candle.pha.pa.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

> 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?
>

I'm not a developer, but one thing I see come up occasionally around here are 
planner issues. Sometimes people get really hammered by the planner choices, 
and aren't provided a very good way to tune it. If you were able to eliminate 
some worst-case-scenario type situations, that would make the few people who 
are having problems very happy (I remember one thread in particular seemed 
nasty). If I remember correctly, some developers don't much like the idea of 
query hints, and I don't blame them, so you might want to run your ideas by 
them first.

Also, this kind of modification might require significant additions to the 
statistics system. The planner might be smart, but if it doesn't have any 
more information you might not be able to get any more out of it. Autovacuum 
might help with that as well (i.e. the info will be more up to date).

Regards,Jeff Davis