Bad query? Or planner?

From: Devin Smith <dsmith(at)redcurrent(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Bad query? Or planner?
Date: 2016-11-28 19:11:49
Message-ID: CAMfqxKH7UKTMNGFXzqS2LBUT+AutV1JH7n4ZimgT-XePH_2T+Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I have recently started using postgres and have been operating under the
assumption that the query planner knows best (as long as I don't do
anything too stupid). I've been structuring my queries (and data) in a
certain way: writing re-usable subqueries, joining them in as necessary,
and only filtering the query at the top level (assuming that the query
planner will push down the appropriate restrictions as necessary). Of
course, also keeping in mind proper indexes to support efficient joins and
sorts.

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.

Am I doing something wrong? Is there room for query planner improvement in
cases like these?

I've attached the EXPLAIN ANALYZEs. Any help would be much appreciated!

Thanks,
-Devin

Attachment Content-Type Size
explain-analyzes.txt text/plain 5.3 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Eduardo Morras 2016-11-28 19:37:49 Re: Storing files: 2.3TBytes, 17M file count
Previous Message Melvin Davidson 2016-11-28 17:21:27 Re: pg_dump system catalog