Re: Ordered Partitioned Table Scans - Mailing list pgsql-hackers

On Sun, 24 Mar 2019 at 05:16, Julien Rouhaud <rjuju123@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