From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Richard Guo <guofenglinux(at)gmail(dot)com> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>, PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Clause accidentally pushed down ( Possible bug in Making Vars outer-join aware) |
Date: | 2023-05-12 17:49:07 |
Message-ID: | 1539384.1683913747@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
I wrote:
> This is correct AFAICS, and it's slightly cheaper, so an optimistic
> conclusion would be that we're making a cost-based decision to use a plan
> shape that the old code could not find for some reason. But these costs
> are pretty close, within the "fuzz factor", so this might be a random
> effect. It might be interesting to see if inserting unequal amounts
> of data in the tables would drive the costs further apart, allowing
> us to say that this code really does beat v15 in terms of planning
> flexibility.
Hah, indeed so:
DROP TABLE IF EXISTS t1,t2,t3,t4 CASCADE;
CREATE TABLE t1 AS SELECT true AS x FROM generate_series(0,1) x;
CREATE TABLE t2 AS SELECT true AS x FROM generate_series(0,1) x;
CREATE TABLE t3 AS SELECT true AS x FROM generate_series(0,1) x;
CREATE TABLE t4 AS SELECT true AS x FROM generate_series(0,1000) x;
ANALYZE t1,t2,t3,t4;
EXPLAIN --(COSTS OFF)
select * from t1
left join t2 on true
left join t3 on t2.x
left join t4 on t3.x;
v15 (and HEAD) find a plan of cost 180, this patch finds one
of cost 120. This test case is pretty artificial of course;
can we come up with a more plausible query that we win on?
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Munro | 2023-05-12 18:43:32 | Re: BUG #17929: LLVMBuildGEP: symbol not found |
Previous Message | Tom Lane | 2023-05-12 17:25:10 | Re: Clause accidentally pushed down ( Possible bug in Making Vars outer-join aware) |