Date: Sat, 29 Dec 2018 18:25:09 -0700 (MST)
From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-performance@postgresql.org
Message-ID: <1546133109941-0.post@n3.nabble.com>
In-Reply-To: 
 <CANcFUu5a9W_MORQa5Tv9vsofN_w_irU9-OBQtN34YmL7p4gP_g@mail.gmail.com>
References: 
 <CANcFUu5a9W_MORQa5Tv9vsofN_w_irU9-OBQtN34YmL7p4gP_g@mail.gmail.com>
Subject: Re: Need help with optimising simple query
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

 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