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

"Michael A. Olson" <mao@sleepycat.com> writes:
> Third, we had to abstract a lot of the hard-coded table scans in
> the bowels of the system to call a routine that checked for the
> existence of an index on the system table, and used it.

The way that we've been approaching this is by switching from hard-coded
sequential scans (heap_getnext() calls) to hard-coded indexscans
(index_getnext() calls) at places where performance dictates it.

An advantage of doing it that way is that you don't have the
bootstrapping/circularity problems that Mike describes; the code doesn't
need to consult pg_index to know whether there is an index to use, it
just has the necessary info hard-coded in.  For the same reason it's
very quick.

Nonetheless it's also a pretty ugly answer.  I'd rather the code wasn't
so tightly tied to a particular set of indexes for system tables.

I was thinking about doing something like what Mike describes: replace
uses of heap_beginscan() with calls to a routine that would examine the
passed ScanKey(s) to see if there is a relevant index, and then start
either a heap or index scan as appropriate.  The circularity issue could
be resolved by having that routine have hard-coded knowledge of some of
the system-table indexes (or even all of them, which is still better
than having that knowledge scattered throughout the code).  But the
performance cost of identifying the right index based on ScanKeys gives
me pause.  It's hard to justify that per-search overhead when the
hard-coded approach works well enough.

Thoughts anyone?
        regards, tom lane