Tom Lane writes:
> Keep in mind though that you seem to be experimenting with a
> fully-cached database; you may find that the planner's beliefs more
> nearly approach reality when actual I/O has to occur.
My hope is that the entire database should fit in memory. This may
not be in the case right now with only 1GB, but it should be close.
The pgsql/data/base/NNN directory is about 1.5GB on production. I'm
pretty sure with constant vacuuming, we could keep that size down.
A pgdump is about 60MB now, growing at about .5MB a day.
> Another thing I'd be interested to know about is how closely the
> physical order of the table entries correlates with min_date_time.
Probably "pretty close". The primary key of aa_t is (bb_id,
server_id), and bb_id is a sequence. aa_t is updated heavily on
production, but these tests are on a fresh import so vacuuming and
index order is not a factor. We do a reload every now and then to
improve performance on production. min_date_time is highly correlated
with bb_id, because both are increasing constantly. server_id is one
of 16 values.
> A high correlation reduces the actual cost of the indexscan (since
> visiting the rows in index order becomes less of a random-access
> proposition). We are aware that the planner doesn't model this effect
> very well at present ...
Oracle's optimizer is lacking here, too. The best optimizer I've seen
was at Tandem, and even then hints were required.
Are there plans for explicit hints to the planner?
Thanks,
Rob