CODEWITHSUNDEEP
postgresqlsql-execution-plan
HappyGuitarBoy
HappyGuitarBoy

Reputation: 115

How to understand the nested loop in PostgreSQL explain?

Please answer,thanks a lot.

Q1: why is the query condition a.id = b.id but only scanned the index of a.id at the beginng? but the number of loop is so big ?

Q2: What does the 'Nested Loop' node do in the explain ?

happydb=# EXPLAIN (ANALYZE,VERBOSE) SELECT b.name FROM a,b WHERE a.id = b.id AND b.id < 10000;
                                                                         QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------
-------------------
 Gather  (cost=1000.57..174222.54 rows=18002 width=13) (actual time=5.881..3276.311 rows=19998 loops=1)
   Output: b.name
   Workers Planned: 5
   Workers Launched: 5
   ->  Nested Loop  (cost=0.56..171422.34 rows=3600 width=13) (actual time=3.189..3258.998 rows=3333 loops=6)
         Output: b.name
         Worker 0: actual time=2.591..3259.895 rows=1850 loops=1
         Worker 1: actual time=0.180..3251.631 rows=4081 loops=1
         Worker 2: actual time=1.344..3261.433 rows=555 loops=1
         Worker 3: actual time=8.603..3262.411 rows=3330 loops=1
         Worker 4: actual time=0.821..3259.297 rows=4623 loops=1
         ->  Parallel Seq Scan on public.b  (cost=0.00..141721.20 rows=3600 width=17) (actual time=1.020..3223.285 rows=3333 loops
=6)
               Output: b.id, b.name
               Filter: (b.id < 10000)
               Rows Removed by Filter: 2663334
               Worker 0: actual time=0.054..3237.921 rows=1850 loops=1
               Worker 1: actual time=0.049..3215.862 rows=4081 loops=1
               Worker 2: actual time=0.102..3236.592 rows=555 loops=1
               Worker 3: actual time=0.296..3235.327 rows=3330 loops=1
               Worker 4: actual time=0.055..3188.732 rows=4623 loops=1
         ->  Index Only Scan using idx_rock_id on public.a  (cost=0.56..8.24 rows=1 width=4) (actual time=0.008..0.010 r
ows=1 loops=19998)
               Output: a.id
               Index Cond: (a.id = b.id)
               Heap Fetches: 19998
               Worker 0: actual time=0.011..0.011 rows=1 loops=1850
               Worker 1: actual time=0.008..0.008 rows=1 loops=4081
               Worker 2: actual time=0.044..0.044 rows=1 loops=555
               Worker 3: actual time=0.007..0.007 rows=1 loops=3330
               Worker 4: actual time=0.006..0.015 rows=1 loops=4623
 Planning Time: 0.579 ms
 Execution Time: 3277.727 ms
(31 rows)

Finally,according to Laurenz Albe's guidance, the execution plan after adding the index is much better, and the execution time has been greatly reduced.

music=# CREATE INDEX idx_b_id ON b(id);
WARNING:  concurrent insert in progress within table "b"
CREATE INDEX


music=# set max_parallel_workers_per_gather = 6;
SET
music=# EXPLAIN (ANALYZE,VERBOSE) SELECT b.name FROM a,b WHERE a.id = b.id AND b.id < 10000;
                                                                         QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------
-------------------
 Gather  (cost=1388.64..108804.88 rows=20599 width=13) (actual time=1.316..35.770 rows=19998 loops=1)
   Output: b.name
   Workers Planned: 3
   Workers Launched: 3
   ->  Nested Loop  (cost=388.64..105744.98 rows=6645 width=13) (actual time=0.341..27.475 rows=5000 loops=4)
         Output: b.name
         Worker 0: actual time=0.095..26.337 rows=5365 loops=1
         Worker 1: actual time=0.091..26.641 rows=5753 loops=1
         Worker 2: actual time=0.101..28.578 rows=3145 loops=1
         ->  Parallel Bitmap Heap Scan on public.b  (cost=388.08..51229.82 rows=6645 width=17) (actual time=0.298..1.265 rows=5000 loops=4)
               Output: b.id, b.name
               Recheck Cond: (b.id < 10000)
               Heap Blocks: exact=31
               Worker 0: actual time=0.044..1.111 rows=5365 loops=1
               Worker 1: actual time=0.044..1.205 rows=5753 loops=1
               Worker 2: actual time=0.049..0.681 rows=3145 loops=1
               ->  Bitmap Index Scan on idx_b_id  (cost=0.00..382.93 rows=20599 width=0) (actual time=1.012..1.012 rows=19998 loops=1)
                     Index Cond: (b.id < 10000)
         ->  Index Only Scan using idx_para_select_id on public.a  (cost=0.56..8.19 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=19998)
               Output: a.id
               Index Cond: (a.id = b.id)
               Heap Fetches: 19998
               Worker 0: actual time=0.004..0.004 rows=1 loops=5365
               Worker 1: actual time=0.003..0.004 rows=1 loops=5753
               Worker 2: actual time=0.004..0.004 rows=1 loops=3145
 Planning Time: 0.264 ms
 Execution Time: 37.025 ms
(27 rows)

Upvotes: 9

Views: 25126

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246163

A nested loop join works like this:

  • PostgreSQL scans the outer table, in this case b.

  • For each row found in the outer table, PostgreSQL scans the inner table, in this case a, for matching rows.

    Since there is an index on the join condition on the inner table, PostgreSQL uses an index scan there.

So with a nested loop join, only an index on the join condition on the inner table can be used.

There is also a condition on the scan of the outer table (b.id < 10000), but that has nothing to do with the join. You don't seem to have an index on that column, so a sequential scan is used.

The large number of executions for the inner loop is explained by the 19998 rows found scanning the outer table: for each of these rows, the inner table gets scanned.

Almost all the execution time is spent on that parallel sequential scan, and most of the rows are discarded, so I assume that the following index will make the query considerably faster:

CREATE INDEX ON b (id);

Upvotes: 20

Related Questions