Hi Zhenglong,

> Thanks for the thorough review and the +1. v5 is attached.

Thanks for v5.  With both patches applied on master, make check
passes.

> Could you share the merge join query that still fails on v4? I tried a
> merge join over a backward index scan with the same operator class, and it
> worked both on master and with v4-0003.

Here it is.  Both queries fail on v5 too with default settings, while
master returns 21 and 3 rows.

CREATE TYPE nh AS ENUM ('r', 'g', 'b');
CREATE FUNCTION nh_lt(nh, nh) RETURNS bool
  LANGUAGE internal IMMUTABLE STRICT AS 'enum_lt';
CREATE FUNCTION nh_eq(nh, nh) RETURNS bool
  LANGUAGE internal IMMUTABLE STRICT AS 'enum_eq';
CREATE FUNCTION nh_cmp(nh, nh) RETURNS int
  LANGUAGE internal IMMUTABLE STRICT AS 'enum_cmp';
CREATE OPERATOR <<< (leftarg = nh, rightarg = nh, function = nh_lt);
CREATE OPERATOR === (leftarg = nh, rightarg = nh, function = nh_eq,
  merges);
CREATE OPERATOR CLASS nh_ops DEFAULT FOR TYPE nh USING btree AS
  OPERATOR 1 <<<, OPERATOR 3 ===, FUNCTION 1 nh_cmp(nh, nh);
CREATE TABLE tn (c nh, e int);
INSERT INTO tn SELECT (ARRAY['r', 'g', 'b'])[1 + i % 3]::nh, i % 7
  FROM generate_series(1, 3000) i;
CREATE INDEX ON tn (c DESC) INCLUDE (e);
CREATE TABLE un (c nh);
INSERT INTO un SELECT (ARRAY['r', 'g', 'b'])[1 + i % 3]::nh
  FROM generate_series(1, 30) i;
VACUUM ANALYZE tn, un;

SELECT t.c, t.e, count(*) FROM tn t JOIN un u ON t.c === u.c
GROUP BY t.c, t.e;
SELECT t.c, count(DISTINCT t.e) FROM tn t JOIN un u ON t.c === u.c
GROUP BY t.c;

It needs one more sort key after the index column, from GROUP BY or a
DISTINCT aggregate.  The forward index scan is then kept as useful for
grouping, and the merge join fails sorting the other input by c DESC.

> If your query also fails on master,
> the guard can't make things consistent and I'm fine dropping it. If not,
> maybe it is better to check the operator once in
> count_common_leading_group_pathkeys(), so that such pathkeys are not kept.

Master runs it fine, so it's the latter.  With that check on v5,
both queries above get master's plans and results.  I have gone
through the overall flow of the code, and in broad terms it looks
right to me.  I will take more time on the details, and keep looking
for regressions when the revised patch comes.

One thing so far: with partitionwise aggregation over 256 partitions
with indexes, planning time on v5 grows up to 2x over master, as the
orderings are considered for each partition.  A table with 256
partitions holds a lot of data, though, so a few ms of planning should
not matter there.

> Either way, I agree it is the committers' decision.

Agreed.

Best regards,
Henson

Reply via email to