Re: Index scan optimization

On 16 November 2014 19:30, Simon Riggs Wrote:
> Sent: 16 November 2014 19:30

> > I marked the patch as ready for committer.
>
> This looks very interesting.
>
> The value of the patch seems to come from skipping costly checks. Your
> performance test has a leading VARCHAR column, so in that specific case
> skipping the leading column is a big win. I don't see it would be in
> all cases.

Yes you are right. Best improvement will be for a case where leading column comparison is very costly (e.g. VARCHAR).

> Can we see a few tests on similar things to check for other
> opportunities and regressions.

> * Single column bigint index
It gives around 5% improvement.

> * Multi-column bigint index
It gives around 5% improvement.

> * 5 column index with mixed text and integers
It gives around 15% improvement.

As we can see in-case of bigint, improvement is not as high as for VARCHAR because comparison of bigint data-type is not costly.
So this being one of the worst scenario performance test and I think even in-case of worst case 5% improvement with so less/safe code changes
should be OK specially since for other scenario (like varchar index) improvement is high (15-30%).

Also even for bigint (or any other similar data-type) improvement can increase if number of records going to be selected increases.

Test-case used for testing is attached.

Please provide your opinion.

> The explanatory comments need some work to more clearly explain what
> this patch does.

Please help me to understand this point, you want me to add more comments about patch in this mail chain or in code.

Thanks and Regards,
Kumar Rajeev Rastogi