Tuning single row operations - Mailing list pgsql-hackers

For 8.3 my goal is to improve the performance of single row operations,
such asINSERT INTO foo ... VALUES (...)UPDATE foo SET .... WHERE <unique index> = <values>DELETE FROM foo     WHERE
<uniqueindex> = <values>
 

OLTP Assumptions
- all statements are prepared first, then executed with bound
parameters. 
- need to cover cases where these are executed from functions in a PL,
as well as the case where they are executed via a protocol v3+ message
from a latest-edition driver.
- we are likely to be using RETURNING clauses
- we care about both single connections issuing a stream of these
requests, as well as performance with many concurrent sessions
- we have Referential Integrity constraints defined
- we care about both with/without Slony replication
- we care about log-shipping/warm standby also
- we want to support all other features also: Most Advanced OSDB etc

I would like to discuss what opportunities exist to improve these
operations and to prioritise them for work during 8.3 and beyond.

Currently, I'm aware of these possibilities, some fairly vague
- set up index scan at plan time, not in executor
- stop the index scan immediately a single row is returned
- reduce WAL for updates when SET clause doesn't mention all cols
- avoid RI checks for update of a column not mentioned in SET
- separate prepared plan from plan state, to avoid memcpy
- avoid double access of buffer for UPDATE/DELETE by producing new fast
path through executor, streamlined for unique accesses
- turn off WAL for (some?) indexes and rebuild them following a crash
- HOT updates: don't do index inserts for unchanged indexed cols
- avoid explicit locking of indexes (at cost of concurrent index ops)
- improve RI check perf by caching small, static tables in each backend
- apply index filter conditions on index scan to avoid heap lookup
- others... feel free to add your own etc

Clearly, some of these need further work. The question is which ones
have sufficient promise to be worth taking further and what would the
priority order for that work be? I assume that a full feasibility
investigation is needed for each item and that there is *no* presumption
that something prioritised higher means it is pre-approved for
inclusion. I'll document the responses as an additional section of the
public TODO, some of which may be removed later if they prove
infeasible. Those would possibly be labelled: OLTP Performance and
Investigations: Items thought to be worth investigation.

I'd like to initiate some open discussion on how, given the above goal,
to improve performance of PostgreSQL. If you don't have any ideas or
opinions now, you're welcome to reply to this thread in the future to
introduce new possibilities.

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com