Re: Performance (was: The New Slashdot Setup (includes MySql server))

I wrote:
>>>>> Nonono, the 1000 read() calls are triggered by a simple INSERT or UPDATE
>>>>> call. They actually scan the pg_index table of the benchmark database.
>
> Ohh ... pg_index is the culprit! OK, I know exactly where that's coming
> from: the planner is looking around to see what indexes might be
> interesting for planning the query. Several comments here:
>
> 1. Probably we ought to try to bypass most of the planning process for
> a simple INSERT ... VALUES. (I thought I had fixed that, but apparently
> it's not getting short-circuited soon enough, if the search for indexes
> is still happening.)

It never pays to assume you know what's happening without having looked
:-(. It turns out the planner is not the only culprit: the executor's
ExecOpenIndices() routine *also* does a sequential scan of pg_index.
I did shortcircuit the planner's search in the INSERT ... VALUES case,
but of course the executor still must find out whether the table has
indexes.

In UPDATE, DELETE, or INSERT ... SELECT, pg_index is scanned *twice*,
once in the planner and once in the executor. (In fact it's worse
than that: the planner scans pg_index separately for each table named
in the query. At least the executor only does it once since it only
has to worry about one target relation.)

Definitely need to cache the indexing information...

regards, tom lane