Re: [HACKERS] Secondary index access optimizations

Hi David,

On 11.09.2018 06:49, David Rowley wrote:
> On 9 July 2018 at 13:26, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>> I started looking over this patch and have a few comments:
> Hi Konstantin,
>
> Wondering, are you going to be submitting an updated patch for this
> commitfest? If not then I think we can mark this as returned with
> feedback as it's been waiting on author for quite a while now.
>

First of all thank your for review.
I am very sorry for delay with answer: I was in vacation in July and
just forgot about this mail.
I have to agree with you that it is better to split this patch into two
and that using range type for open and close intervals match is so good
idea.
Also the patch proposed by you is much simple and does mostly the same.
Yes, it is not covering CHECK constraints,
but as far as partitioning becomes now standard in Postgres, I do not
think that much people will use old inheritance mechanism and CHECK
constraints. In any case, there are now many optimizations which works
only for partitions, but not for inherited tables.

I attach to this mail your patch combined with corrected tests outputs.
Unfortunately the performance gain is not so much as I expected (even
without additional
predicate_implied_by() smarts). On the following database:

create table bt (k integer, v integer) partition by range (k);
create table dt1 partition of bt for values from (1) to (10001);
create table dt2 partition of bt for values from (10001) to (20001);
create index dti1 on dt1(v);
create index dti2 on dt2(v);
insert into bt values (generate_series(1,20000), generate_series(1,20000));
analyze bt;

and pgbench script:

\set x random(1, 10000)
select * from bt where k between 1 and 20001 and v=:x;

I got ~170k TPS with this patch and about ~160k TPS without it.
My hypothesis was that we have to perform redundant predicate only once
(for one selected record)
and it adds no so much overhead comparing with index search cost.
So I tried another version of  the query which selects large number of
records:

select sum(*) from bt where k between 1 and 20001 and v between :x and
:x + 1000;

Now patch version shows 23k TPS vs. 19k for Vanilla.