From: | Johann Spies <johann(dot)spies(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Bad query? Or planner? |
Date: | 2016-12-05 07:57:44 |
Message-ID: | CAGZ55DRww-ON2Wiz3hKaTpmSSEw61=Hr6URx+M7AD0kq3RdnEw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 28 November 2016 at 21:11, Devin Smith <dsmith(at)redcurrent(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | dhanuj hippie | 2016-12-05 09:36:43 | postgres pg_restore append data |
Previous Message | Johann Spies | 2016-12-05 07:38:18 | Re: Postgres and LibreOffice's 'Base' |