On 28 November 2016 at 21:11, Devin Smith <dsm...@redcurrent.com> wrote:
> Hi, > > > I recently wrote a query that I thought was easy to reason about, and I > assumed the query planner would execute it efficiently. > > SELECT * FROM xtag_stack_feed > JOIN ( > SELECT DISTINCT ON (do_post_xtag.xtag_ci) * > FROM do_post_xtag > JOIN do_post ON do_post.rc_id=do_post_xtag.post_rc_id > ORDER BY do_post_xtag.xtag_ci ASC, do_post_xtag.post_rc_id DESC) > last_post > ON last_post.xtag_ci=xtag_stack_feed.xtag_ci > ORDER BY decayed_to_base DESC > LIMIT 1; > > Unfortunately, the query as written is not being executed efficiently. I > tried to rewrite it in a couple different ways without success, and then > learned about lateral joins. Rewritten as follows, it executes efficiently. > > SELECT * FROM xtag_stack_feed > JOIN LATERAL ( > SELECT DISTINCT ON (do_post_xtag.xtag_ci) * > FROM do_post_xtag > JOIN do_post ON do_post.rc_id=do_post_xtag.post_rc_id > WHERE do_post_xtag.xtag_ci=xtag_stack_feed.xtag_ci > ORDER BY do_post_xtag.xtag_ci ASC, do_post_xtag.post_rc_id DESC) > last_post > ON true > ORDER BY decayed_to_base DESC > LIMIT 1; > > From my naive perspective, it seems like the second query is semantically > equivalent to the first; it just has the join condition moved into the > subquery as a WHERE filter. > > > I do not see a "where" condition in your first query. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
