On Sun, Oct 18, 2009 at 4:54 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Robert Haas <robertmhaas@xxxxxxxxx> writes: >> On Sun, Oct 18, 2009 at 1:59 PM, Jeff Janes <jeff.janes@xxxxxxxxx> wrote: >>> Even if country.id is a primary or unique key? > >> Well, we currently don't have any logic for making inferences based on >> unique constraints. > > Huh? > http://archives.postgresql.org/pgsql-committers/2009-09/msg00159.php > > Admittedly it's just one case and there's lots more to be done, but it's > more than nothing. So this is a *potential* argument for trying to trim > subquery outputs. What I'm not sure about is whether there are common > cases where this would be applicable below a non-flattenable subquery. Sorry, I have to stop writing emails when I'm half-asleep. Obviously what we don't have is logic for making deductions based on *foreign key* constraints, but that's not relevant here. Maybe I should shut up before I say any more dumb things, but one possible case where we don't currently do join removal but it would be nice if we did is: SELECT ... FROM a.x LEFT JOIN (SELECT bb.x, SUM(1) FROM bb GROUP BY bb.x) b ON a.x = b.x; Or even: SELECT ... FROM a.x LEFT JOIN (SELECT DISTINCT ON (bb.x) ... FROM bb) b ON a.x = b.x; Your commit message for the join removal patch mentions machine-generated SQL, but where join removal really comes up a lot for me is when using views. I like to define a view that includes all the columns that seem potentially useful and then let the user pick which ones they'd like to see. The trouble is that you don't want to incur the cost of computing the columns that the user doesn't select. It's probably true that in MOST of the cases where this comes up, the subquery can be flattened, from_collapse_limit permitting. But I think there are other cases, too. Another thing to keep in mind is that, in OLTP environments, it's sometimes important to minimize the number of server round-trips. The type of construction suggested by the OP might be someone's way of gather two somewhat-unrelated values with a single query. Except sometimes they only need one of them, but they end up paying for both anyway. They could probably work around this with a little bit different setup, but I don't think they're entirely wrong to find the current behavior a little bit surprising. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
