From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-performance@postgresql.org
Subject: Re: Need help with optimising simple query
Date: Sat, 29 Dec 2018 18:25:09 -0700 (MST)
Message-ID: <1546133109941-0.post@n3.nabble.com> (raw)
In-Reply-To: <CANcFUu5a9W_MORQa5Tv9vsofN_w_irU9-OBQtN34YmL7p4gP_g@mail.gmail.com>
References: <CANcFUu5a9W_MORQa5Tv9vsofN_w_irU9-OBQtN34YmL7p4gP_g@mail.gmail.com>
I didn't find any CREATE TABLE's in your description, or else I would have
tried it with the sequences and all that, but I think this ought to work.
postgres=# explain select * from ((select * from parent inner join child on
parent.child_id = child.id limit 10) union all (select * from parent left
outer join child on parent.child_id = child.id where child.id is null limit
10)) as v limit 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Limit (cost=0.15..3.72 rows=10 width=88)
-> Append (cost=0.15..7.29 rows=20 width=88)
-> Limit (cost=0.15..2.46 rows=10 width=88)
-> Nested Loop (cost=0.15..246.54 rows=1070 width=88)
-> Seq Scan on parent (cost=0.00..20.70 rows=1070
width=48)
-> Index Scan using child_pkey on child
(cost=0.15..0.21 rows=1 width=40)
Index Cond: (id = parent.child_id)
-> Limit (cost=0.15..4.63 rows=10 width=88)
-> Nested Loop Anti Join (cost=0.15..239.71 rows=535
width=88)
-> Seq Scan on parent parent_1 (cost=0.00..20.70
rows=1070 width=48)
-> Index Scan using child_pkey on child child_1
(cost=0.15..0.21 rows=1 width=40)
Index Cond: (parent_1.child_id = id)
(12 rows)
-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jfinnert@amazon.com
Subject: Re: Need help with optimising simple query
In-Reply-To: <1546133109941-0.post@n3.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox