On 12.09.2018 08:14, David Rowley wrote:
On 12 September 2018 at 08:32, Konstantin Knizhnik
<k.knizh...@postgrespro.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
