Re: speedup COPY TO for partitioned table.

Hi,

jian he <jian(dot)universality(at)gmail(dot)com>, 27 Oca 2025 Pzt, 04:47 tarihinde
şunu yazdı:

> in the
>
> find_all_inheritors->find_inheritance_children->find_inheritance_children_extended
>
> find_inheritance_children_extended we have
> """
> if (numoids > 1)
> qsort(oidarr, numoids, sizeof(Oid), oid_cmp);
> """
>
> so the find_all_inheritors output order is deterministic?
>

You're right that order in find_all_inheritors is deterministic. But it's
not always the same with the order of SELECT output. You can quickly see
what I mean by running a slightly modified version of the example that you
shared in your first email:

CREATE TABLE t3 (a INT, b int ) PARTITION BY RANGE (a);
-- change the order. first create t3_2 then t3_1
create table t3_2 partition of t3 for values from (11) to (15);
create table t3_1 partition of t3 for values from (1) to (11);
insert into t3 select g from generate_series(1, 3) g;
insert into t3 select g from generate_series(11, 11) g;

And the results of the two different COPY approaches would be:
postgres=# COPY t3 TO STDOUT;
11 \N
1 \N
2 \N
3 \N
postgres=# COPY (SELECT * FROM t3) TO STDOUT;
1 \N
2 \N
3 \N
11 \N

Notice that "COPY t3 TO STDOUT" changes the order since the partition t3_2
has been created first, hence it has a smaller OID. On the other hand,
SELECT sorts the partitions based on partition boundaries, not OIDs. That's
why we should always see the same order regardless of the OIDs of
partitions (you can see create_range_bounds() in partbounds.c if interested
in more details). One thing that might be useful in the COPY case would be
using a partition descriptor to access the correct order of partitions. I
believe something like (PartitionDesc) partdesc->oid should give us the
partition OIDs in order.

Thanks,
--
Melih Mutlu
Microsoft