Search Postgresql Archives

Re: Bad query? Or planner?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux