Query optimization problem
in [PgSql]
|
Prev: [HACKERS] Solaris Sparc - dblink regression test failure
Next: [PATCH] "could not reattach to shared memory" on Windows
From: Dimitri Fontaine on 20 Jul 2010 11:23 Robert Haas <robertmhaas(a)gmail.com> writes: > All that having been said, I think the issue here is that the query > planner isn't inferring that d1.ID=<some constant> implies d2.ID=<some > constant>, even though there's a join clause d1.ID=d2.ID. I think that's what the Equivalence Classes are for. Or at least that's what they do in my head, not forcibly in the code. The specific diff between the two queries is : JOIN DocPrimary d2 ON d2.BasedOn=d1.ID - WHERE (d1.ID=234409763) or (d2.ID=234409763) + WHERE (d2.BasedOn=234409763) or (d2.ID=234409763) So the OP would appreciate that the planner is able to consider applying the restriction on d2.BasedOn rather than d1.ID given that d2.BasedOn is the same thing as d1.ID, from the JOIN. I have no idea if Equivalence Classes are where to look for this, and if they're meant to extend up to there, and if that's something possible or wise to implement, though. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 20 Jul 2010 11:51 On Tue, Jul 20, 2010 at 11:23 AM, Dimitri Fontaine <dfontaine(a)hi-media.com> wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> All that having been said, I think the issue here is that the query >> planner isn't inferring that d1.ID=<some constant> implies d2.ID=<some >> constant>, even though there's a join clause d1.ID=d2.ID. > > I think that's what the Equivalence Classes are for. Or at least that's > what they do in my head, not forcibly in the code. > > The specific diff between the two queries is�: > > � JOIN DocPrimary d2 ON d2.BasedOn=d1.ID > - WHERE (d1.ID=234409763) or (d2.ID=234409763) > + WHERE (d2.BasedOn=234409763) or (d2.ID=234409763) > > So the OP would appreciate that the planner is able to consider applying > the restriction on d2.BasedOn rather than d1.ID given that d2.BasedOn is > the same thing as d1.ID, from the JOIN. > > I have no idea if Equivalence Classes are where to look for this, and if > they're meant to extend up to there, and if that's something possible or > wise to implement, though. I was thinking of the equivalence class machinery as well. I think the OR clause may be the problem. If you just had d1.ID=constant, I think it would infer that d1.ID, d2.BasedOn, and the constant formed an equivalence class. But here you obviously can't smash the constant into the equivalence class, and I think the planner's not smart enough to consider other ways of applying an equivalent qual. In fact, I have some recollection that Tom has explicitly rejected adding support for this in the past, on the grounds that the computation would be too expensive for the number of queries it would help. Still, it seems to keep coming up. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Dimitri Fontaine on 20 Jul 2010 15:33 Robert Haas <robertmhaas(a)gmail.com> writes: > On Tue, Jul 20, 2010 at 11:23 AM, Dimitri Fontaine > <dfontaine(a)hi-media.com> wrote: >> JOIN DocPrimary d2 ON d2.BasedOn=d1.ID >> - WHERE (d1.ID=234409763) or (d2.ID=234409763) >> + WHERE (d2.BasedOn=234409763) or (d2.ID=234409763) > > I was thinking of the equivalence class machinery as well. I think > the OR clause may be the problem. If you just had d1.ID=constant, I > think it would infer that d1.ID, d2.BasedOn, and the constant formed > an equivalence class. But here you obviously can't smash the constant > into the equivalence class, and I think the planner's not smart enough > to consider other ways of applying an equivalent qual. In fact, I > have some recollection that Tom has explicitly rejected adding support > for this in the past, on the grounds that the computation would be too > expensive for the number of queries it would help. Still, it seems to > keep coming up. Well what I'm thinking now could have nothing to do with how the code works. I'd have to check, but well, it's easier to write this mail and get the chance to have you wonder :) So, the JOIN condition teaches us that d2.BasedOn=d1.ID, and the OP would want the planner to derive that (d1.ID=234409763) is the same thing as (d2.BasedOn=234409763). I guess it would make sense to produce plans with both the writings and pick one based on the costs. Now, does it make sense to generate this many more plans to analyze in the general case, I have no idea about. But given only one join and only one WHERE clause where the Equivalent applies… Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 20 Jul 2010 21:00 On Tue, Jul 20, 2010 at 3:33 PM, Dimitri Fontaine <dfontaine(a)hi-media.com> wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> On Tue, Jul 20, 2010 at 11:23 AM, Dimitri Fontaine >> <dfontaine(a)hi-media.com> wrote: >>> � JOIN DocPrimary d2 ON d2.BasedOn=d1.ID >>> - WHERE (d1.ID=234409763) or (d2.ID=234409763) >>> + WHERE (d2.BasedOn=234409763) or (d2.ID=234409763) >> >> I was thinking of the equivalence class machinery as well. �I think >> the OR clause may be the problem. �If you just had d1.ID=constant, I >> think it would infer that d1.ID, d2.BasedOn, and the constant formed >> an equivalence class. �But here you obviously can't smash the constant >> into the equivalence class, and I think the planner's not smart enough >> to consider other ways of applying an equivalent qual. �In fact, I >> have some recollection that Tom has explicitly rejected adding support >> for this in the past, on the grounds that the computation would be too >> expensive for the number of queries it would help. �Still, it seems to >> keep coming up. > > Well what I'm thinking now could have nothing to do with how the code > works. I'd have to check, but well, it's easier to write this mail and > get the chance to have you wonder�:) > > So, the JOIN condition teaches us that d2.BasedOn=d1.ID, and the OP > would want the planner to derive that (d1.ID=234409763) is the same > thing as (d2.BasedOn=234409763). I guess it would make sense to produce > plans with both the writings and pick one based on the costs. > > Now, does it make sense to generate this many more plans to analyze in > the general case, I have no idea about. But given only one join and only > one WHERE clause where the Equivalent applies� It seems like deciding which rel to apply the filter condition to would be a fairly expensive optimization. Perhaps we could recognize the special case where substituting another member of the equivalence class allows the qual to be pushed down where it otherwise couldn't be. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Sam Mason on 21 Jul 2010 09:40
On Tue, Jul 20, 2010 at 09:57:06AM +0400, Zotov wrote: > SELECT d1.ID, d2.ID > FROM DocPrimary d1 > JOIN DocPrimary d2 ON d2.BasedOn=d1.ID > WHERE (d1.ID=234409763) or (d2.ID=234409763) You could try rewriting it to: SELECT d1.ID, d2.ID FROM DocPrimary d1 JOIN DocPrimary d2 ON d2.BasedOn=d1.ID WHERE d1.ID=234409763 UNION SELECT d1.ID, d2.ID FROM DocPrimary d1 JOIN DocPrimary d2 ON d2.BasedOn=d1.ID WHERE d2.ID=234409763 This should have the same semantics as the original query. I don't believe PG knows how to do a rewrite like this at the moment. -- Sam http://samason.me.uk/ -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |