explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bF4d

Settings
# exclusive inclusive rows x rows loops node
1. 0.258 440,699.170 ↓ 439.0 439 1

Nested Loop (cost=68,539.14..257,177.10 rows=1 width=114) (actual time=129,659.508..440,699.170 rows=439 loops=1)

2.          

Initplan (for Nested Loop)

3. 0.007 0.026 ↑ 1.0 1 1

Aggregate (cost=12.92..12.93 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=1)

4. 0.019 0.019 ↓ 2.5 10 1

Index Scan using idx_test_externalid on test (cost=0.42..12.91 rows=4 width=8) (actual time=0.013..0.019 rows=10 loops=1)

  • Index Cond: (externalid = 23,992)
5. 0.005 0.021 ↑ 1.0 1 1

Aggregate (cost=12.92..12.93 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=1)

6. 0.016 0.016 ↓ 2.5 10 1

Index Scan using idx_test_externalid on test test_1 (cost=0.42..12.91 rows=4 width=8) (actual time=0.009..0.016 rows=10 loops=1)

  • Index Cond: (externalid = 23,992)
7. 0.018 0.018 ↑ 1.0 1 1

Seq Scan on categorytype (cost=0.00..3.30 rows=1 width=8) (actual time=0.010..0.018 rows=1 loops=1)

  • Filter: (((typecode)::text = 'READ_ALOUD'::text) AND ((originationcode)::text = 'CB'::text))
  • Rows Removed by Filter: 86
8. 0.541 440,697.969 ↓ 439.0 439 1

Merge Join (cost=68,509.70..257,146.79 rows=1 width=242) (actual time=129,659.470..440,697.969 rows=439 loops=1)

  • Merge Cond: (ep_contentgroup.foilid = ep_foil.id)
  • Join Filter: (readaloudaccommodation_taskvariantresponse_columns.taskvariantresponseid = ep_foil.externalid)
9. 307,092.307 440,537.731 ↓ 456.4 6,846 1

Nested Loop (cost=1,002.69..708,390.14 rows=15 width=258) (actual time=114,865.918..440,537.731 rows=6,846 loops=1)

  • Join Filter: (readaloudaccommodation_taskvariantresponse_columns.contentgroupid = ep_contentgroup.externalid)
  • Rows Removed by Join Filter: 2,904,655,092
10. 1,114.584 1,114.584 ↑ 1.0 6,616,542 1

Index Scan using idx_contentgroup_foilid on contentgroup ep_contentgroup (cost=0.43..267,912.35 rows=6,617,145 width=24) (actual time=0.007..1,114.584 rows=6,616,542 loops=1)

11. 131,422.558 132,330.840 ↓ 439.0 439 6,616,542

Materialize (cost=1,002.25..341,220.62 rows=1 width=250) (actual time=0.000..0.020 rows=439 loops=6,616,542)

12. 17.958 908.282 ↓ 439.0 439 1

Gather (cost=1,002.25..341,220.62 rows=1 width=250) (actual time=907.778..908.282 rows=439 loops=1)

  • Workers Planned: 2
  • Params Evaluated: $1
  • Workers Launched: 2
13. 0.150 890.324 ↓ 146.0 146 3 / 3

Hash Join (cost=2.25..340,220.52 rows=1 width=250) (actual time=890.227..890.324 rows=146 loops=3)

  • Hash Cond: (ep_accessibilityfile.externalid = readaloudaccommodation_taskvariantresponse_columns.accessibilityfileid)
14. 886.904 886.904 ↓ 2.3 467 3 / 3

Parallel Seq Scan on accessibilityfile ep_accessibilityfile (cost=0.00..340,217.51 rows=200 width=16) (actual time=886.799..886.904 rows=467 loops=3)

  • Filter: (((originationcode)::text = 'CB'::text) AND (createdate = $1))
  • Rows Removed by Filter: 3,324,534
15. 0.158 3.270 ↓ 4.4 439 3 / 3

Hash (cost=1.00..1.00 rows=100 width=250) (actual time=3.270..3.270 rows=439 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 67kB
16. 3.112 3.112 ↓ 4.4 439 3 / 3

Function Scan on json_to_recordset readaloudaccommodation_taskvariantresponse_columns (cost=0.00..1.00 rows=100 width=250) (actual time=3.000..3.112 rows=439 loops=3)

17. 0.000 159.697 ↓ 1.8 156 1

Sort (cost=67,506.99..67,507.21 rows=86 width=16) (actual time=159.686..159.697 rows=156 loops=1)

  • Sort Key: ep_foil.id
  • Sort Method: quicksort Memory: 32kB
18. 3.320 159.788 ↓ 1.8 156 1

Gather (cost=1,000.00..67,504.23 rows=86 width=16) (actual time=159.534..159.788 rows=156 loops=1)

  • Workers Planned: 2
  • Params Evaluated: $0
  • Workers Launched: 2
19. 156.468 156.468 ↓ 1.4 52 3 / 3

Parallel Seq Scan on foil ep_foil (cost=0.00..66,495.63 rows=36 width=16) (actual time=156.321..156.468 rows=52 loops=3)

  • Filter: (((originationcode)::text = 'CB'::text) AND (createdate = $0))
  • Rows Removed by Filter: 841,372
20. 0.878 0.878 ↑ 1.0 1 439

Index Scan using idx_category_categoryname on category ep_readaloudtype (cost=0.28..1.14 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=439)

  • Index Cond: ((categoryname)::text = (readaloudaccommodation_taskvariantresponse_columns.typename)::text)
  • Filter: (((originationcode)::text = 'CB'::text) AND (categorytypeid = $2))
Planning time : 1.075 ms
Execution time : 440,699.709 ms