pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizer failed to generate plan
2+ messages / 2 participants
[nested] [flat]
* Optimizer failed to generate plan
@ 2023-02-13 10:24 xianliang.ji@esgyn.cn
2023-02-13 11:40 ` Re: Optimizer failed to generate plan David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: xianliang.ji@esgyn.cn @ 2023-02-13 10:24 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: Wang Xiao-Zhong <xiaozhong.wang@esgyn.cn>; Guo Heng <heng.guo@esgyn.cn>
Hi PostgreSQL community,
Hello guys，I am a DBA and found the flowing issue when doing test on
postgresql. is there any plan to fix this issue？Thanks and feedback are
welcome.
xianliang.ji
Company: esgyn
Email: xianliang.ji@esgyn.cn
[postgres@mdw postgresql]$ /home/postgres/postgresql/install/bin/psql
psql (15.2)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=# CREATE TABLE dept(
postgres(# deptno int,
postgres(# dname VARCHAR(14),
postgres(# loc VARCHAR(13)
postgres(# );
CREATE TABLE
postgres=# CREATE TABLE emp(
postgres(# empno int,
postgres(# ename VARCHAR(10),
postgres(# job VARCHAR(9),
postgres(# mgr int,
postgres(# hiredate DATE,
postgres(# sal double precision,
postgres(# comm double precision,
postgres(# deptno int,
postgres(# jobno int
postgres(# );
CREATE TABLE
postgres=# select a.*,b.* from EMP a FULL JOIN DEPT b on
a.deptno=b.deptno OR upper(ename)='BLAKE' where a.job='MANAGER' OR
b.dname='SALES';
ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable
join conditions
postgres=#
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Optimizer failed to generate plan
2023-02-13 10:24 Optimizer failed to generate plan xianliang.ji@esgyn.cn
@ 2023-02-13 11:40 ` David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: David Rowley @ 2023-02-13 11:40 UTC (permalink / raw)
To: xianliang.ji@esgyn.cn; +Cc: pgsql-bugs@lists.postgresql.org, Wang Xiao-Zhong <xiaozhong.wang@esgyn.cn>; Guo Heng <heng.guo@esgyn.cn>
On Tue, 14 Feb 2023 at 00:18, <xianliang.ji@esgyn.cn> wrote:
> postgres=# select a.*,b.* from EMP a FULL JOIN DEPT b on
> a.deptno=b.deptno OR upper(ename)='BLAKE' where a.job='MANAGER' OR
> b.dname='SALES';
> ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable
It's not a bug. The problem is the join condition has OR conditions
and we can do a full Hash or Merge join for joins like that.
I'm a bit unclear on what you need exactly, but maybe you could get it
by repeating the query once for each branch of the OR and then
performing a UNION ALL.
David
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-02-13 11:40 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-13 10:24 Optimizer failed to generate plan xianliang.ji@esgyn.cn
2023-02-13 11:40 ` David Rowley <dgrowleyml@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox