On Sun, 24 Mar 2019 at 05:16, Julien Rouhaud <rjuju...@gmail.com> wrote: > ISTM that a query like > SELECT * FROM nested ORDER BY 1, 2; > could simply append all the partitions in the right order (or generate > a tree of ordered appends), but: > > QUERY PLAN > ------------------------------------------------------------------- > Append > -> Merge Append > Sort Key: nested_1_1.id1, nested_1_1.id2 > -> Index Scan using nested_1_1_id1_id2_idx on nested_1_1 > -> Index Scan using nested_1_2_id1_id2_idx on nested_1_2 > -> Index Scan using nested_1_3_id1_id2_idx on nested_1_3 > -> Merge Append > Sort Key: nested_2_1.id1, nested_2_1.id2 > -> Index Scan using nested_2_1_id1_id2_idx on nested_2_1 > -> Index Scan using nested_2_2_id1_id2_idx on nested_2_2 > -> Index Scan using nested_2_3_id1_id2_idx on nested_2_3 > (11 rows) > > > Also, a query like > SELECT * FROM nested_1 ORDER BY 1, 2; > could generate an append path, since the first column is guaranteed to > be identical in all partitions, but instead: > > QUERY PLAN > ------------------------------------------------------------- > Merge Append > Sort Key: nested_1_1.id1, nested_1_1.id2 > -> Index Scan using nested_1_1_id1_id2_idx on nested_1_1 > -> Index Scan using nested_1_2_id1_id2_idx on nested_1_2 > -> Index Scan using nested_1_3_id1_id2_idx on nested_1_3 > (5 rows) > > and of course > > # EXPLAIN (costs off) SELECT * FROM nested_1 ORDER BY 2; > QUERY PLAN > ------------------------------------ > Sort > Sort Key: nested_1_1.id2 > -> Append > -> Seq Scan on nested_1_1 > -> Seq Scan on nested_1_2 > -> Seq Scan on nested_1_3 > (6 rows)
I think both these cases could be handled, but I think the way it would likely have to be done would be to run the partition constraints through equivalence class processing. Likely doing that would need some new field in EquivalenceClass that indicated that the eclass did not need to be applied to the partition. If it was done that way then pathkey_is_redundant() would be true for the id1 column's pathkey in the sub-partitioned tables. The last plan you show above could also use an index scan too since build_index_pathkeys() would also find the pathkey redundant. Doing this would also cause a query like: select * from nested_1_1 where id2=1; would not apply "id2 = 1" as a base qual to the partition. That's good for 2 reasons. 1) No wasted effort filtering rows that always match; and 2) A Seq scan can be used instead of the planner possibly thinking that an index scan might be useful to filter rows. Stats might tell the planner that anyway... but... I suggested some changes to equivalence classes a few years ago in [1] and I failed to get that idea floating. In ways, this is similar as it requires having equivalence classes that are not used in all cases. I think to get something working a week before code cutoff is a step too far for this, but certainly, it would be interesting to look into fixing it in a later release. [1] https://www.postgresql.org/message-id/flat/CAKJS1f9FK_X_5HKcPcSeimy16Owe3EmPmmGsGWLcKkj_rW9s6A%40mail.gmail.com -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
