Re: [HACKERS] Secondary index access optimizations

On 12.09.2018 08:14, David Rowley wrote:
> On 12 September 2018 at 08:32, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> 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've not had time to look at your updated patch yet, but one thing I
> thought about after my initial review, imagine you have a setup like:
>
> create table listp (a int, b int) partition by list(a);
> create table listp1 partition of listp for values in(1);
> create index listp_a_b_idx on listp (a,b);
>
> and a query:
>
> select * from listp where a = 1 order by b;
>
> if we remove the "a = 1" qual, then listp_a_b_idx can't be used.

Looks like this qual is considered for choosing optimal path before it
is removed from list of quals in set_append_rel_size.
At least the presence of this patch is not breaking the plan in this case:

create table listp (a int, b int) partition by list(a);
create table listp1 partition of listp for values in(1);
create table listp2 partition of listp for values in(2);
create index listp_a_b_idx on listp (a,b);
insert into listp values (1,generate_series(1,100000));
insert into listp values (2,generate_series(100001,200000));
explain select * from listp where a = 1 order by b;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Merge Append  (cost=0.30..4630.43 rows=100000 width=8)
   Sort Key: listp1.b
   ->  Index Only Scan using listp1_a_b_idx on listp1
(cost=0.29..3630.42 rows=100000 width=8)
(3 rows)

> I didn't test this in your patch, but I guess since the additional
> quals are not applied to the children in set_append_rel_size() that by
> the time set_append_rel_pathlist() is called, then when we go
> generating the paths, the (a,b) index won't be any good.
>
> Perhaps there's some workaround like inventing some sort of "no-op"
> qual that exists in planning but never makes it way down to scans.
> Although I admit to not having fully thought that idea through.
>

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