On Fri, Apr 28, 2017 at 3:24 PM, David Rowley <david.row...@2ndquadrant.com> wrote:
> On 29 April 2017 at 07:59, Dave Vitek <dvi...@grammatech.com> wrote: > > Is what I want in the query planner's vocabulary? It would need to > exploit > > the fact that the _id columns are not nullable, and either exploit the > > uniqueness of the id columns or do an extra LIMIT step after the join. I > > think I want it to effectively give the same result I expect (haven't > > tested) it would give for: > > Unfortunately, it's not a plan that the current planner will consider. > > > SELECT D.val, B.val, C.val FROM > > (SELECT * FROM A ORDER BY A.rank LIMIT 100) AS D > > JOIN B ON D.b_id = B.id > > JOIN C ON B.c_id = C.id > > LIMIT 100; > > > > Perhaps there are reasons why this optimization is not safe that I > haven't > > thought about? > > Yeah, I think so. What happens if an A row cannot find a match in B or > C? This version of the query will end up returning fewer rows due to > that, but the original version would consider other rows with a higher > rank. > > We've danced around a bit with using foreign keys as proofs that rows > will exist for other optimisations in the past, but it's tricky ground > since foreign keys are not updated immediately, so there are windows > where they may not actually hold true to their word. > I read this query as having a relation cardinality of one-to-one mandatory - which precludes the scenario described. Is the above saying that, today, there is no planning benefit to setting up two deferrable references constraints to enforce the non-optional requirement? I know I'm guilty of not enforcing the non-optional part of the constraint. Mostly due to not really realizing it but also having to deal the added syntax to perform inserts. ORMs I suspect generally would be unaccommodating here as well... David J.
