Re: UNION removes almost all rows (not duplicates) - in fresh build of pg17!

David Rowley <dgrowleyml@gmail.com>

From: David Rowley <dgrowleyml@gmail.com>

To: depesz@depesz.com

Cc: pgsql-bugs mailing list <pgsql-bugs@postgresql.org>

Date: 2024-05-20T21:54:25Z

Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Fix generate_union_paths for non-sortable types.
- c37267162e88 17.0 landed
Doc: remove 66c0185a3 from release notes.
- e1e83a71715f 17.0 landed
Revert commit 66c0185a3 and follow-on patches.
- 7204f35919b7 17.0 landed
doc PG 17 relnotes: adjust vacuum items
- d2a04470aa64 17.0 cited
Allow planner to use Merge Append to efficiently implement UNION
- 66c0185a3d14 17.0 cited

Attachments

fix_union_planning.patch (application/octet-stream) patch

On Tue, 21 May 2024 at 04:00, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
> $ explain (analyze) select count(*) from ( select * from pg_class where relkind = 'r' union select * from pg_class where relkind = 'i' );
>                                                               QUERY PLAN
> ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
>  Aggregate  (cost=42.43..42.44 rows=1 width=8) (actual time=0.108..0.109 rows=1 loops=1)
>    ->  Unique  (cost=0.00..39.53 rows=232 width=236) (actual time=0.013..0.105 rows=1 loops=1)
>          ->  Append  (cost=0.00..39.53 rows=232 width=236) (actual time=0.012..0.099 rows=232 loops=1)
>                ->  Seq Scan on pg_class  (cost=0.00..19.19 rows=68 width=263) (actual time=0.012..0.058 rows=68 loops=1)
>                      Filter: (relkind = 'r'::"char")
>                      Rows Removed by Filter: 348
>                ->  Seq Scan on pg_class pg_class_1  (cost=0.00..19.19 rows=164 width=263) (actual time=0.002..0.030 rows=164 loops=1)
>                      Filter: (relkind = 'i'::"char")
>                      Rows Removed by Filter: 252

Thanks for the report.

It looks like it's a very simple fix.  The problem is I wasn't setting
groupList when the grouping_is_sortable() returned false.  In the
prior version, make_union_unique() always set that.

The attached should apply cleanly up to d2a04470a.

David