apache / apache/cloudberry

different plan when set the enable_parallel on REPLICATED table.

Open
#824 0 comments 0 reactions 0 assignees View on GitHub
priority: Low type: Performance
Dominant language
C
Stars
1.4k
Forks
247
Avg merge
4d 3h
Merged PRs (30d)
39

Description

### Apache Cloudberry version

_No response_

### What happened

```
drop table if exists with_test2 cascade;
drop table if exists with_test3 cascade;
create table with_test2 (id bigserial NOT NULL, isc varchar(15) NOT NULL,iscd varchar(15) NULL) DISTRIBUTED REPLICATED;
create table with_test3 (id numeric NULL, rc varchar(255) NULL,ri numeric NULL) DISTRIBUTED REPLICATED;
insert into with_test2 (isc,iscd) values ('CMN_BIN_YES', 'CMN_BIN_YES');
insert into with_test3 (id,rc,ri) values (113551,'CMN_BIN_YES',101991), (113552,'CMN_BIN_NO',101991), (113553,'CMN_BIN_ERR',101991), (113554,'CMN_BIN_NULL',101991);
explain
WITH
t1 AS (SELECT * FROM with_test2),
t2 AS (SELECT id, rc FROM with_test3 WHERE ri = 101991)
SELECT p.*FROM t1 p JOIN t2 r ON p.isc = r.rc JOIN t2 r1 ON p.iscd = r1.rc LIMIT 1;
```
The result when enable_parallel turn on and off.
![image](https://github.com/user-attachments/assets/265a845b-c098-4fa3-8ab8-2f6bb4add8d7)

This case has nothing to do with the table's statistics.
Result after analyze:
![image](https://github.com/user-attachments/assets/5c0da9bf-760b-4f49-a28d-a1b4a1611511)

### What you think should happen instead

_No response_

### How to reproduce

pass

### Operating System

any

### Anything else

_No response_

### Are you willing to submit PR?

- [ ] Yes, I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/cloudberry/blob/main/CODE_OF_CONDUCT.md).

Contributor guide

Open the contributing guide

Research direction

No source file or test entry point is named. Start by running the supplied SQL with enable_parallel both on and off, compare the EXPLAIN plans, and investigate the replicated-table planner path; done means the plan discrepancy has an agreed expected result and is resolved.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, sql
Domain
databases, distributed-systems, performance
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.