Re: [PATCHES] Including Snapshot Info with Indexes

Gokulakannan Somasundaram wrote:
> On 10/26/07, Heikki Linnakangas <heikki(at)enterprisedb(dot)com> wrote:
>> Gokulakannan Somasundaram wrote:
>>> As far as Load Test is concerned, i have tried to provide all the
>> relevant
>>> details. Please inform me, if i have left any.
>> Thanks!
>>
>> How large were the tables?
>
> It is in the Performance test report. They contain 2 million records. 6
> columns wide, 3 text and 3 numeric. same set of tables used for both tests,
> after refresh from a file

I meant in megabytes. How wide is the data in the text and numeric fields?

> Did you run all the queries concurrently? At this point, I think it'd be
>> better to run them separately so that you can look at the impact on each
>> kind of operation in isolation.
>>
> Performance tests are run against a workload and i have taken the workload
> of a small scale partitioning setup. Running the queries individually has
> already been done and the count of logical reads have been verified. I have
> already suggested that. For some reason, i am not able to convince that for
> simple index scans, Logical reads are a good measure of performance.

I wouldn't expect any performance gain for simple, not index-only,
scans. They have to hit the heap anyway.

> What does the numbers look like if the the tables are small enough to
>> fit in RAM?
>
> I don't know whether this is a valid production setup, against which we need
> to benchmark. But if you insist, i will do that and get back to you next
> time.

A lot of people run databases that fit in RAM. And a lot of people
don't. Both cases are interesting. I'm particularly curious about that
because you've argued that the number of logical reads is important,
even if they don't become physical reads. Hannu also suggested that
swapping pages in/out of shared_buffers is relatively expensive; if
that's the case, we should see index-only scans performing much better
regular index scans, even when there's no physical I/O.

> You should do some tuning, the PostgreSQL default configuration is not
>> tuned for maximum performance. At least increase checkpoint_segments and
>> checkpoint_timeout and shared_buffers. Though I noticed that you're
>> running on Windows; I don't think anyone's done any serious performance
>> testing or tuning on Windows yet, so I'm not sure how you should tune
>> that.
>
> What we are trying to do here, is to try and compare the performance of two
> indexing structures. AFAIK, the performance test done to compare two
> software implementations should not have parameter settings, favorable to
> one. I have not done any settings change favorable to thick index.

The tuning I suggested is just basic tuning any knowledgeable Postgres
DBA will do. It's not particularly in favor of any indexing scheme. With
the default checkpoint settings, for example, the system is going to be
busy doing checkpoints all the time if you have a reasonable rate of
updates.

> But i
> have a limited setup, from which i am trying to contribute. So please don't
> ask me to run the tests against large scale servers.

Understood.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com