On 28 November 2016 at 21:11, Devin Smith <dsmith@xxxxxxxxxxxxxx> 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_feedJOIN (SELECT DISTINCT ON (do_post_xtag.xtag_ci) *FROM do_post_xtagJOIN 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_postON last_post.xtag_ci=xtag_stack_feed.xtag_ci ORDER BY decayed_to_base DESCLIMIT 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_feedJOIN LATERAL (SELECT DISTINCT ON (do_post_xtag.xtag_ci) *FROM do_post_xtagJOIN 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_postON trueORDER BY decayed_to_base DESCLIMIT 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)
my lips will praise you. (Psalm 63:3)