0

I have the following query with explain analyze and it's incredibly slow because of the LEFT JOIN.

EXPLAIN ANALYZE SELECT DISTINCT ON (project_name, release_version, project_id, release_id) vbc.id
FROM component as vbc 
JOIN entry as vbe on vbe.component_id = vbc.id 
LEFT JOIN snippet vbs ON vbs.id = vbe.id  AND (vbe.match_type <> 'SNIPPET' OR (vbe.match_type = 'SNIPPET' AND vbs.ignored = false AND vbs.review_status = 'REVIEWED')) 
WHERE (vbc.bom_id = 1000) AND vbc.ignored = false ORDER BY project_name, release_version, project_id, release_id LIMIT 100 OFFSET 0;


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2966876.16..2966896.12 rows=100 width=65) (actual time=275249.395..275251.007 rows=100 loops=1)
   ->  Unique  (cost=2966876.16..2966938.04 rows=310 width=65) (actual time=275249.391..275250.991 rows=100 loops=1)
         ->  Sort  (cost=2966876.16..2966888.53 rows=4951 width=65) (actual time=275249.386..275249.924 rows=2200 loops=1)
               Sort Key: vbc.project_name, vbc.release_version, vbc.project_id, vbc.release_id
               Sort Method: quicksort  Memory: 14596kB
               ->  Gather  (cost=2136.78..2966572.33 rows=4951 width=65) (actual time=718.263..275094.602 rows=81813 loops=1)
                     Workers Planned: 2
                     Workers Launched: 2
                     ->  Hash Join  (cost=1136.78..2965077.23 rows=2063 width=65) (actual time=618.293..275125.324 rows=27271 loops=3)
                           Hash Cond: (vbe.component_id = vbc.id)
                           ->  Parallel Seq Scan on entry vbe  (cost=0.00..2931661.83 rows=12296583 width=48) (actual time=0.016..271733.711 rows=9600686 loops=3)
                           ->  Hash  (cost=1132.91..1132.91 rows=310 width=65) (actual time=47.014..47.015 rows=1427 loops=3)
                                 Buckets: 2048 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 151kB
                                 ->  Index Scan using id on component vbc  (cost=0.43..1132.91 rows=310 width=65) (actual time=2.520..46.054 rows=1427 loops=3)
                                       Index Cond: (bom_id = 1000)
                                       Filter: (NOT ignored)
 Planning Time: 0.499 ms
 Execution Time: 275252.141 ms
(18 rows)

If I use INNER JOIN, the query finishes in an expected time frame.

EXPLAIN ANALYZE SELECT DISTINCT ON (project_name, release_version, project_id, release_id) vbc.id
FROM component as vbc 
JOIN entry as vbe on vbe.component_id = vbc.id 
JOIN snippet vbs ON vbs.id = vbe.id  AND (vbe.match_type <> 'SNIPPET' OR (vbe.match_type = 'SNIPPET' AND vbs.ignored = false AND vbs.review_status = 'REVIEWED')) 
WHERE (vbc.bom_id = 1000) AND vbc.ignored = false ORDER BY project_name, release_version, project_id, release_id LIMIT 100 OFFSET 0;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=385418.33..385418.55 rows=18 width=65) (actual time=10720.710..10720.890 rows=0 loops=1)
   ->  Unique  (cost=385418.33..385418.55 rows=18 width=65) (actual time=10720.708..10720.886 rows=0 loops=1)
         ->  Sort  (cost=385418.33..385418.37 rows=18 width=65) (actual time=10720.706..10720.883 rows=0 loops=1)
               Sort Key: vbc.project_name, vbc.release_version, vbc.project_id, vbc.release_id
               Sort Method: quicksort  Memory: 25kB
               ->  Gather  (cost=2137.22..385417.95 rows=18 width=65) (actual time=10720.651..10720.825 rows=0 loops=1)
                     Workers Planned: 2
                     Workers Launched: 2
                     ->  Hash Join  (cost=1137.22..384416.15 rows=8 width=65) (actual time=10689.277..10689.281 rows=0 loops=3)
                           Hash Cond: (vbe.component_id = vbc.id)
                           ->  Nested Loop  (cost=0.44..383162.55 rows=44504 width=8) (actual time=432.096..10682.463 rows=16 loops=3)
                                 ->  Parallel Seq Scan on snippet vbs  (cost=0.00..7826.27 rows=44728 width=21) (actual time=0.696..239.924 rows=35782 loops=3)
                                 ->  Index Scan using entry_pkey on entry vbe  (cost=0.44..8.38 rows=1 width=48) (actual time=0.290..0.290 rows=0 loops=107346)
                                       Index Cond: (id = vbs.id)
                                       Filter: ((match_type <> 'SNIPPET'::text) OR ((match_type = 'SNIPPET'::text) AND (NOT vbs.ignored) AND (vbs.review_status = 'REVIEWED'::text)))
                                       Rows Removed by Filter: 1
                           ->  Hash  (cost=1132.91..1132.91 rows=310 width=65) (actual time=6.522..6.524 rows=1427 loops=3)
                                 Buckets: 2048 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 151kB
                                 ->  Index Scan using id on component vbc  (cost=0.43..1132.91 rows=310 width=65) (actual time=1.131..5.619 rows=1427 loops=3)
                                       Index Cond: (bom_id = 1000)
                                       Filter: (NOT ignored)
 Planning Time: 46.880 ms
 Execution Time: 10721.121 ms
(23 rows)

The problem is that the LEFT JOIN on snippet is needed for accurate results. Should the query be written in a different format?

PS: I'm using PG11.

Edit:

Query plan with track_io_timings and EXPLAIN (ANALYZE, BUFFERS)

                                                                                      QUERY PLAN                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2966875.99..2966895.95 rows=100 width=65) (actual time=301225.934..301227.765 rows=100 loops=1)
   Buffers: shared hit=1094434 read=1716026 dirtied=35 written=21
   I/O Timings: read=522968.395 write=30.194
   ->  Unique  (cost=2966875.99..2966937.86 rows=310 width=65) (actual time=301225.932..301227.745 rows=100 loops=1)
         Buffers: shared hit=1094434 read=1716026 dirtied=35 written=21
         I/O Timings: read=522968.395 write=30.194
         ->  Sort  (cost=2966875.99..2966888.36 rows=4950 width=65) (actual time=301225.927..301226.536 rows=2200 loops=1)
               Sort Key: vbc.project_name, vbc.release_version, vbc.project_id, vbc.release_id
               Sort Method: quicksort  Memory: 14596kB
               Buffers: shared hit=1094434 read=1716026 dirtied=35 written=21
               I/O Timings: read=522968.395 write=30.194
               ->  Gather  (cost=2136.78..2966572.23 rows=4950 width=65) (actual time=546.213..301036.820 rows=81813 loops=1)
                     Workers Planned: 2
                     Workers Launched: 2
                     Buffers: shared hit=1094434 read=1716026 dirtied=35 written=21
                     I/O Timings: read=522968.395 write=30.194
                     ->  Hash Join  (cost=1136.78..2965077.23 rows=2062 width=65) (actual time=470.918..301084.436 rows=27271 loops=3)
                           Hash Cond: (vbe.component_id = vbc.id)
                           Buffers: shared hit=1094434 read=1716026 dirtied=35 written=21
                           I/O Timings: read=522968.395 write=30.194
                           ->  Parallel Seq Scan on entry vbe  (cost=0.00..2931661.83 rows=12296583 width=48) (actual time=12.168..297138.197 rows=9600686 loops=3)
                                 Buffers: shared hit=1092670 read=1716026 dirtied=35 written=21
                                 I/O Timings: read=522968.395 write=30.194
                           ->  Hash  (cost=1132.91..1132.91 rows=310 width=65) (actual time=3.180..3.181 rows=1427 loops=3)
                                 Buckets: 2048 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 151kB
                                 Buffers: shared hit=1676
                                 ->  Index Scan using bom_ id on component vbc  (cost=0.43..1132.91 rows=310 width=65) (actual time=0.129..2.318 rows=1427 loops=3)
                                       Index Cond: (bom_id = 1000)
                                       Filter: (NOT ignored)
                                       Buffers: shared hit=1676
 Planning Time: 0.501 ms
 Execution Time: 301229.344 ms
(32 rows)
Improve this question
5
  • Inner joining to snippet efficiently eliminates almost all of the rows from needing to be fetched. Left joining to it does not. Often getting the right answer requires more work than getting the wrong answer does.
    – jjanes
    Commented Dec 15, 2021 at 18:34
  • @jjanes Yes, there's an index and the tables are all up to date! "idx_entry_component_id" btree (component_id)
    – underachiever
    Commented Dec 15, 2021 at 19:00
  • It is baffling why the seq scan of "entry" is so slow, and thought to be so costly too. How big is the table? Is it massively bloated?
    – jjanes
    Commented Dec 15, 2021 at 20:24
  • @jjanes Added the EXPLAIN (ANALYZE, BUFFERS) in the question. The the entry table is about 27GB.
    – underachiever
    Commented Dec 16, 2021 at 13:18
  • 27 GB does explain the cost estimate. That amounts to just over 1000 bytes per row of the table, which is large but not implausibly so. Does this seem like about the right average size for a row, given what you know about your data? But still, why is it not doing a nested loop and using the component_id index? What plan do you get if you set enable_hashjoin to off?
    – jjanes
    Commented Dec 16, 2021 at 20:44

1 Answer 1

Reset to default
0

It looks like the left join with snippet is unnecessary, at least it is eliminated by PostgreSQL. This probably has to do with a unique constraint on snippet. So you should do away with the whole LEFT JOIN.

For the rest of the query, an index on entry(component_id) should get you a fast index-only scan.

Improve this answer
12
  • The LEFT JOIN is needed to get the correct records and there is already an index for the component_id in the entry table. "idx_entry_component_id" btree (component_id)
    – underachiever
    Commented Dec 16, 2021 at 14:18
  • Well, PostgreSQL says otherwise. Remove the whole join to snippet and see if anything changes.
    – Laurenz Albe
    Commented Dec 16, 2021 at 14:29
  • Without the LEFT JOIN on snippet, the query completes in 10 seconds but there are many unwanted records. AND (vbe.match_type <> 'SNIPPET' OR (vbe.match_type = 'SNIPPET' AND vbs.ignored = false AND vbs.review_status = 'REVIEWED')) is crucial in filtering the rows.
    – underachiever
    Commented Dec 16, 2021 at 14:46
  • Then why does snippet not appear in the execution plan you posted?
    – Laurenz Albe
    Commented Dec 16, 2021 at 14:48
  • 1
    @LaurenzAlbe Left joins can be removed when the right-hand columns are never used (so it can't matter if they are NULL-extended or real) and the join condition can't return multiple right rows per left row, due to unique constraint on the right table. And of course it inherently can't remove rows as they will just get NULL extended instead. There is nothing about FK constraints directly involved in the decision.
    – jjanes
    Commented Dec 16, 2021 at 17:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.