Обсуждение: BUG #12760: Lateral files with more than 2 laterals
The following bug has been logged on the website:
Bug reference: 12760
Logged by: momomo
Email address: moe1234512345@gmail.com
PostgreSQL version: 9.3.5
Operating system: UBUNTU
Description:
set geqo_threshold = 2;
SELECT *
FROM generate_series(1, 1) A,
lateral ( SELECT B from generate_series(1, 1) B where B = A limit 1 ) AS
B0,
lateral ( SELECT C from generate_series(1, 1) C where C = A and C != B limit
1 ) AS C0
Here is the original query:
SET geqo_threshold = 24;
SELECT
A.name, A.symbol,
P0.percent AS P0_percent,
P1.percent AS P1_percent,
P2.percent AS P2_percent,
P3.percent AS P3_percent
/*,
B0.usd AS P0_start, C0.usd AS P0_end, B0.date AS P0_starttime,
C0.date AS P0_endtime,
B1.usd AS P1_start, C1.usd AS P1_end, B1.date AS P1_starttime,
C1.date AS P1_endtime*/
FROM ticker A,
lateral ( SELECT B0.usd, B0.date from priceclose B0 where B0.date BETWEEN
'2014-10-31' AND '2015-01-15' AND B0.ticker = A.symbol ORDER BY B0.date ASC
limit 1 ) AS B0,
lateral ( SELECT C0.usd, C0.date from priceclose C0 where C0.date BETWEEN
'2014-10-31' AND '2015-01-15' AND C0.ticker = A.symbol AND C0.date !=
B0.date ORDER BY C0.date DESC limit 1 ) AS C0,
lateral ( SELECT B1.usd, B1.date from priceclose B1 where B1.date BETWEEN
'2015-01-08' AND '2015-12-10' AND B1.ticker = A.symbol ORDER BY B1.date ASC
limit 1 ) AS B1,
lateral ( SELECT C1.usd, C1.date from priceclose C1 where C1.date BETWEEN
'2015-01-08' AND '2015-12-10' AND C1.ticker = A.symbol AND C1.date !=
B1.date ORDER BY C1.date DESC limit 1 ) AS C1,
lateral ( SELECT B2.usd, B2.date from priceclose B2 where B2.date BETWEEN
'2015-01-31' AND '2015-12-10' AND B2.ticker = A.symbol ORDER BY B2.date ASC
limit 1 ) AS B2,
lateral ( SELECT C2.usd, C2.date from priceclose C2 where C2.date BETWEEN
'2015-01-31' AND '2015-12-10' AND C2.ticker = A.symbol AND C2.date !=
B2.date ORDER BY C2.date DESC limit 1 ) AS C2,
lateral ( SELECT B3.usd, B3.date from priceclose B3 where B3.date BETWEEN
'2015-02-06' AND '2015-12-10' AND B3.ticker = A.symbol ORDER BY B3.date ASC
limit 1 ) AS B3,
lateral ( SELECT C3.usd, C3.date from priceclose C3 where C3.date BETWEEN
'2015-02-06' AND '2015-12-10' AND C3.ticker = A.symbol AND C3.date !=
B3.date ORDER BY C3.date DESC limit 1 ) AS C3,
lateral ( SELECT C0.usd / B0.usd AS percent ) AS P0,
lateral ( SELECT C1.usd / B1.usd AS percent ) AS P1,
lateral ( SELECT C2.usd / B2.usd AS percent ) AS P2,
lateral ( SELECT C3.usd / B3.usd AS percent ) AS P3
WHERE
P0.percent BETWEEN 0.4 AND 0.7
AND
P1.percent BETWEEN 1.08 AND 10
AND
P2.percent BETWEEN 1.03 AND 10
AND
P3.percent BETWEEN 1.01 AND 10
ORDER BY P1.percent DESC;
Note, setting geqo_threshold = 24 makes this error disappear.
* moe1234512345@gmail.com (moe1234512345@gmail.com) wrote:
> PostgreSQL version: 9.3.5
Happens in master too.
> set geqo_threshold =3D 2;
>=20
> SELECT *
> FROM generate_series(1, 1) A,
> lateral ( SELECT B from generate_series(1, 1) B where B =3D A limit 1 ) AS
> B0,
> lateral ( SELECT C from generate_series(1, 1) C where C =3D A and C !=3D =
B limit
> 1 ) AS C0
The result is:
ERROR: failed to join all relations together
Looks like an issue where GEQO and LATERAL don't get along. :/
Thanks,
Stephen
moe1234512345@gmail.com writes:
> set geqo_threshold = 2;
> SELECT *
> FROM generate_series(1, 1) A,
> lateral ( SELECT B from generate_series(1, 1) B where B = A limit 1 ) AS
> B0,
> lateral ( SELECT C from generate_series(1, 1) C where C = A and C != B limit
> 1 ) AS C0
It's a good idea to show what problem you're seeing, though in this case
I didn't have much trouble reproducing it:
regression=# SELECT *
FROM generate_series(1, 1) A,
lateral ( SELECT B from generate_series(1, 1) B where B = A limit 1 ) AS B0,
lateral ( SELECT C from generate_series(1, 1) C where C = A and C = B limit 1 ) AS C0;
ERROR: failed to join all relations together
Will look, thanks for the report!
regards, tom lane
Stephen Frost <sfrost@snowman.net> writes:
> Looks like an issue where GEQO and LATERAL don't get along. :/
On inspection, it seems like the error check that this is triggering is
just plain wrong.
What's happening is that the geqo pool initialization code
(random_init_pool) is proposing the join order A, C0, B0 (1, 3, 2);
which cannot work in this example because B0 (rel 2) has to be joined to A
before C0 is. Now merge_clump first tries to join A and C0 (1 and 3),
which is legal although it generates only a path parameterized by B0.
Then there is no way to join that to B0 (rel 2) since each side of the
join is wanting to be parameterized by the other.
You could argue that desirable_join should figure out that the 1+3 join
isn't so desirable; but that would still not prevent dead-end joins
like this from being made once gimme_tree starts setting the "force"
parameter. Or you could argue that gimme_tree+merge_clump should be
willing to back off and try another clumping once they realize that what
they're doing is a dead end. That seems pretty complicated.
On the whole, it seems like the best choice is to allow overall failure,
since we're certainly going to try other tours anyway. My recollection is
that this code used to have a failure case, which it handled by returning
DBL_MAX as the estimated cost of the tour. We got rid of that at some
point, essentially assuming that this logic could always succeed at
finding a legal join order --- but considering that it's fundamentally a
heuristic, it doesn't seem too bright to assume that it will *always* find
one.
regards, tom lane
On Thu, Feb 12, 2015 at 05:43:23PM +0100, Moe wrote: > I reposted this report a few times with additonal information. Meh. These looked like duplicates to me, so I didn't approve them from the moderator queue. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
I reposted this report a few times with additonal information. "On the whole, it seems like the best choice is to allow overall failure, since we're certainly going to try other tours anyway. " Note that increasing GEQO resolved the issue, so I am not sure what you mean by letting it fail, it seems like it can be made to work. But maybe I am misinterpreting your intention. On Wed, Feb 11, 2015 at 1:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Stephen Frost <sfrost@snowman.net> writes: > > Looks like an issue where GEQO and LATERAL don't get along. :/ > > On inspection, it seems like the error check that this is triggering is > just plain wrong. > > What's happening is that the geqo pool initialization code > (random_init_pool) is proposing the join order A, C0, B0 (1, 3, 2); > which cannot work in this example because B0 (rel 2) has to be joined to A > before C0 is. Now merge_clump first tries to join A and C0 (1 and 3), > which is legal although it generates only a path parameterized by B0. > Then there is no way to join that to B0 (rel 2) since each side of the > join is wanting to be parameterized by the other. > > You could argue that desirable_join should figure out that the 1+3 join > isn't so desirable; but that would still not prevent dead-end joins > like this from being made once gimme_tree starts setting the "force" > parameter. Or you could argue that gimme_tree+merge_clump should be > willing to back off and try another clumping once they realize that what > they're doing is a dead end. That seems pretty complicated. > > On the whole, it seems like the best choice is to allow overall failure, > since we're certainly going to try other tours anyway. My recollection is > that this code used to have a failure case, which it handled by returning > DBL_MAX as the estimated cost of the tour. We got rid of that at some > point, essentially assuming that this logic could always succeed at > finding a legal join order --- but considering that it's fundamentally a > heuristic, it doesn't seem too bright to assume that it will *always* find > one. > > regards, tom lane >