Re: [HACKERS] Secondary index access optimizations

On 04.10.2018 12:19, David Rowley wrote:
> On 4 October 2018 at 22:11, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> On 04.10.2018 06:19, David Rowley wrote:
>>> Please, can you also add a test which tests this code which has a
>>> partition with columns in a different order than it's parent. Having
>>> an INT and a TEXT column is best as if the translations are done
>>> incorrectly it's likely to result in a crash which will alert us to
>>> the issue. It would be good to also verify the test causes a crash if
>>> you temporarily put the code back to using the untranslated qual.
>>>
>>> Thanks for working on this.
>>>
>> Thank you very much for detecting and fixing this problem.
>> I have checked that all changes in plan caused by this fix are correct.
>> Updated version of the patch is attached.
> Can you add the test that I mentioned above?
>
Will the following test be enough:

-- check that columns for parent table are correctly mapped to child
partition of their order doesn't match
create table paren (a int, b text) partition by range(a);
create table child_1 partition of paren for values from (0) to (10);
create table child_2 (b text, a int);
alter table paren attach partition child_2 for values from (10) to (20);
insert into paren values (generate_series(0,19), generate_series(100,119));

explain (costs off) select * from paren where a between 0 and 9;
explain (costs off) select * from paren where a between 10 and 20;
explain (costs off) select * from paren where a >= 5;
explain (costs off) select * from paren where a <= 15;

select count(*) from paren where a >= 5;
select count(*) from paren where a < 15;

drop table paren cascade;

--------------------------------------
If so, then updated patch is attached.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company