Re: Bad query? Or planner?

[Devin Smith <dsmith@xxxxxxxxxxxxxx>]

Correct. There is no WHERE filter in the first query. The JOIN condition from the first query was moved into a WHERE filter in the second query (enabled by LATERAL). Both have the same ordering applied with a limit of 1.

I chatted with a couple users in the IRC channel, and I think I got the consensus that the two queries are semantically the same, but the query planner doesn't currently optimize my original query.

-Devin

On Mon, Dec 5, 2016 at 1:59 AM Johann Spies <johann.spies@xxxxxxxxx> wrote:

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_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)