explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YUT6 : Optimization for: plan #n9q

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.108 2,312.657 ↑ 1.0 1 1

Limit (cost=290,469.22..290,469.22 rows=1 width=58) (actual time=2,312.550..2,312.657 rows=1 loops=1)

2. 104.047 2,312.549 ↑ 1,531,597.0 1 1

Sort (cost=290,469.22..294,298.21 rows=1,531,597 width=58) (actual time=2,312.548..2,312.549 rows=1 loops=1)

  • Sort Key: f.created, l.line_position, cla.classification_label DESC
  • Sort Method: top-N heapsort Memory: 25kB
3. 524.384 2,208.502 ↑ 2.7 563,140 1

HashAggregate (cost=267,495.26..282,811.23 rows=1,531,597 width=58) (actual time=2,030.096..2,208.502 rows=563,140 loops=1)

  • Group Key: l.id, cla.classification_label, f.id
4. 138.970 1,684.118 ↑ 2.7 563,140 1

Nested Loop (cost=35,068.22..256,008.28 rows=1,531,597 width=58) (actual time=146.522..1,684.118 rows=563,140 loops=1)

5. 0.000 570.408 ↑ 1.7 194,948 1

Gather (cost=35,068.22..187,167.37 rows=337,066 width=44) (actual time=146.514..570.408 rows=194,948 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
6. 88.046 982.342 ↑ 2.2 48,737 4

Nested Loop (cost=34,068.22..152,460.77 rows=108,731 width=44) (actual time=176.261..982.342 rows=48,737 loops=4)

7. 20.606 894.295 ↑ 2.2 48,737 4

Hash Join (cost=34,068.22..150,395.59 rows=108,731 width=60) (actual time=176.220..894.295 rows=48,737 loops=4)

  • Hash Cond: (f.entity_id = e.id)
8. 23.936 816.221 ↑ 2.2 48,737 4

Hash Join (cost=21,641.00..137,682.93 rows=108,731 width=76) (actual time=117.848..816.221 rows=48,737 loops=4)

  • Hash Cond: (i.file_id = f.id)
9. 634.859 675.732 ↑ 2.2 48,737 4

Nested Loop (cost=0.00..115,756.51 rows=108,731 width=52) (actual time=0.104..675.732 rows=48,737 loops=4)

10. 40.870 40.870 ↑ 1.3 219,710 4

Parallel Seq Scan on invoice_line l (cost=0.00..25,277.16 rows=282,916 width=36) (actual time=0.017..40.870 rows=219,710 loops=4)

11. 0.003 0.003 ↓ 0.0 0 878,838

Index Scan using invoice_id_idx on invoice i (cost=0.00..0.32 rows=1 width=48) (actual time=0.003..0.003 rows=0 loops=878,838)

  • Index Cond: (id = l.invoice_id)
  • Rows Removed by Index Recheck: 0
  • Filter: ((source_file = 'pdf'::text) AND (exported_status = 'AVAILABLE'::text))
  • Rows Removed by Filter: 1
12. 35.252 116.553 ↑ 1.0 130,507 4

Hash (cost=19,934.89..19,934.89 rows=136,489 width=40) (actual time=116.553..116.553 rows=130,507 loops=4)

  • Buckets: 262144 Batches: 1 Memory Usage: 11225kB
13. 81.301 81.301 ↑ 1.0 130,507 4

Seq Scan on file f (cost=0.00..19,934.89 rows=136,489 width=40) (actual time=0.025..81.301 rows=130,507 loops=4)

14. 5.992 57.468 ↑ 5.3 19,520 4

Hash (cost=11,125.95..11,125.95 rows=104,102 width=16) (actual time=57.468..57.468 rows=19,520 loops=4)

  • Buckets: 131072 Batches: 1 Memory Usage: 1939kB
15. 51.476 51.476 ↑ 5.3 19,520 4

Index Only Scan using client_pkey on entity e (cost=0.42..11,125.95 rows=104,102 width=16) (actual time=0.105..51.476 rows=19,520 loops=4)

  • Heap Fetches: 30678
16. 0.001 0.001 ↑ 1.0 1 194,948

Index Scan using supplier_id_idx on supplier s (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=194,948)

  • Index Cond: (id = i.supplier_id)
17. 974.740 974.740 ↑ 1.0 3 194,948

Index Scan using classification_source_id_idx1 on classification cla (cost=0.00..0.17 rows=3 width=30) (actual time=0.003..0.005 rows=3 loops=194,948)

  • Index Cond: (source_id = l.id)
  • Rows Removed by Index Recheck: 0
  • Filter: (NOT is_training)
  • Rows Removed by Filter: 0