explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GiTt : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #PTt4; plan #1SCb; plan #3jBG; plan #eE6h; plan #i2qD; plan #tQzX; plan #Mys4; plan #cJmw; plan #SAeq; plan #uq9X; plan #V9og; plan #QmWj4

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 721.616 ↓ 0.0 0 1

Insert on transfer_reagents (cost=623,999.43..624,004.27 rows=1 width=1,104) (actual time=721.616..721.616 rows=0 loops=1)

2.          

CTE to_upsert

3. 0.165 0.165 ↑ 10.0 10 1

Function Scan on json_to_recordset to_upsert (cost=0.00..1.00 rows=100 width=353) (actual time=0.165..0.165 rows=10 loops=1)

4.          

CTE plates

5. 0.006 721.357 ↑ 101.5 2 1

Unique (cost=252,377.10..252,379.64 rows=203 width=38) (actual time=721.349..721.357 rows=2 loops=1)

6. 0.038 721.351 ↑ 20.3 10 1

Sort (cost=252,377.10..252,377.61 rows=203 width=38) (actual time=721.348..721.351 rows=10 loops=1)

  • Sort Key: pl.id, pl.barcode, pl.experiment_plate_number, e.label
  • Sort Method: quicksort Memory: 25kB
7. 354.812 721.313 ↑ 20.3 10 1

Merge Join (cost=15.47..252,369.32 rows=203 width=38) (actual time=710.413..721.313 rows=10 loops=1)

  • Merge Cond: (pl.experiment_id = e.id)
  • Join Filter: ((tu_1.plate_id = pl.id) OR ((tu_1.plate_barcode)::text = (pl.barcode)::text) OR ((tu_1.plate_number = pl.experiment_plate_number) AND ((tu_1.experiment_label)::text = (e.label)::text)))
  • Rows Removed by Join Filter: 910,800
8. 45.708 45.708 ↓ 1.0 91,081 1

Index Scan using unique_experiment_id_and_plate_number on plates pl (cost=0.29..5,163.84 rows=87,871 width=19) (actual time=0.006..45.708 rows=91,081 loops=1)

9. 216.646 320.793 ↑ 1.4 918,860 1

Materialize (cost=0.29..32,193.90 rows=1,260,500 width=99) (actual time=0.173..320.793 rows=918,860 loops=1)

10. 58.955 104.147 ↑ 10.0 126,050 1

Nested Loop (cost=0.29..29,042.65 rows=1,260,500 width=99) (actual time=0.170..104.147 rows=126,050 loops=1)

11. 7.377 7.377 ↑ 1.0 12,605 1

Index Scan using experiments_pkey on experiments e (cost=0.29..3,831.65 rows=12,605 width=27) (actual time=0.003..7.377 rows=12,605 loops=1)

12. 37.815 37.815 ↑ 10.0 10 12,605

CTE Scan on to_upsert tu_1 (cost=0.00..2.00 rows=100 width=72) (actual time=0.000..0.003 rows=10 loops=12,605)

13.          

CTE printings

14. 0.012 0.172 ↓ 3.0 6 1

Unique (cost=371,608.65..371,608.68 rows=2 width=20) (actual time=0.161..0.172 rows=6 loops=1)

15. 0.032 0.160 ↓ 10.0 20 1

Sort (cost=371,608.65..371,608.66 rows=2 width=20) (actual time=0.160..0.160 rows=20 loops=1)

  • Sort Key: p.id, p.plate_id, p.layer, p.printed_at
  • Sort Method: quicksort Memory: 25kB
16. 0.074 0.128 ↓ 10.0 20 1

Nested Loop (cost=0.56..371,608.64 rows=2 width=20) (actual time=0.048..0.128 rows=20 loops=1)

  • Join Filter: ((tu_2.printing_id = p.id) OR (((tu_2.layer)::text = ((p.layer)::character varying)::text) AND (plates_1.id = p.plate_id) AND CASE WHEN (tu_2.printed_at IS NULL) THEN (p.printed_at IS NULL) ELSE (p.printed_at IS NOT NULL) END))
  • Rows Removed by Join Filter: 40
17. 0.004 0.004 ↑ 10.0 10 1

CTE Scan on to_upsert tu_2 (cost=0.00..2.00 rows=100 width=44) (actual time=0.001..0.004 rows=10 loops=1)

18. 0.013 0.050 ↑ 9,404.5 6 10

Materialize (cost=0.56..230,680.21 rows=56,427 width=24) (actual time=0.002..0.005 rows=6 loops=10)

19. 0.006 0.037 ↑ 9,404.5 6 1

Nested Loop (cost=0.56..230,398.07 rows=56,427 width=24) (actual time=0.012..0.037 rows=6 loops=1)

20. 0.009 0.009 ↑ 101.5 2 1

CTE Scan on plates plates_1 (cost=0.00..4.06 rows=203 width=4) (actual time=0.000..0.009 rows=2 loops=1)

21. 0.022 0.022 ↑ 92.7 3 2

Index Scan using printings_plate_id_idx on printings p (cost=0.56..1,132.17 rows=278 width=20) (actual time=0.005..0.011 rows=3 loops=2)

  • Index Cond: (plate_id = plates_1.id)
22.          

CTE new_transfer_perturbations

23. 0.001 0.246 ↓ 0.0 0 1

Insert on transfer_perturbations (cost=7.34..7.52 rows=2 width=1,435) (actual time=0.246..0.246 rows=0 loops=1)

24. 0.000 0.245 ↓ 0.0 0 1

Subquery Scan on *SELECT* (cost=7.34..7.52 rows=2 width=1,435) (actual time=0.245..0.245 rows=0 loops=1)

25. 0.002 0.245 ↓ 0.0 0 1

GroupAggregate (cost=7.34..7.42 rows=2 width=269) (actual time=0.245..0.245 rows=0 loops=1)

  • Group Key: tu_3.well_address, tu_3.src_barcode, tu_3.src_address, tu_3.transfer_volume, printings_1.id, tu_3.condition_id, tu_3.well_type_id, tu_3.control
26. 0.040 0.243 ↓ 0.0 0 1

Sort (cost=7.34..7.34 rows=2 width=237) (actual time=0.243..0.243 rows=0 loops=1)

  • Sort Key: tu_3.well_address, tu_3.src_barcode, tu_3.src_address, tu_3.transfer_volume, printings_1.id, tu_3.condition_id, tu_3.well_type_id, tu_3.control
  • Sort Method: quicksort Memory: 25kB
27. 0.008 0.203 ↓ 0.0 0 1

Hash Join (cost=2.48..7.33 rows=2 width=237) (actual time=0.203..0.203 rows=0 loops=1)

  • Hash Cond: (plates_2.id = tu_3.plate_id)
28. 0.000 0.000 ↑ 203.0 1 1

CTE Scan on plates plates_2 (cost=0.00..4.06 rows=203 width=4) (actual time=0.000..0.000 rows=1 loops=1)

29. 0.000 0.195 ↓ 0.0 0 1

Hash (cost=2.46..2.46 rows=2 width=241) (actual time=0.195..0.195 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
30. 0.008 0.195 ↓ 0.0 0 1

Hash Join (cost=0.07..2.46 rows=2 width=241) (actual time=0.195..0.195 rows=0 loops=1)

  • Hash Cond: (tu_3.printing_id = printings_1.id)
31. 0.005 0.005 ↑ 10.0 10 1

CTE Scan on to_upsert tu_3 (cost=0.00..2.00 rows=100 width=241) (actual time=0.001..0.005 rows=10 loops=1)

32. 0.009 0.182 ↓ 3.0 6 1

Hash (cost=0.04..0.04 rows=2 width=4) (actual time=0.182..0.182 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 0.173 0.173 ↓ 3.0 6 1

CTE Scan on printings printings_1 (cost=0.00..0.04 rows=2 width=4) (actual time=0.162..0.173 rows=6 loops=1)

34. 0.011 721.616 ↓ 0.0 0 1

Hash Join (cost=2.59..7.43 rows=1 width=1,104) (actual time=721.616..721.616 rows=0 loops=1)

  • Hash Cond: (plates.id = tu.plate_id)
35. 721.350 721.350 ↑ 203.0 1 1

CTE Scan on plates (cost=0.00..4.06 rows=203 width=4) (actual time=721.350..721.350 rows=1 loops=1)

36. 0.001 0.255 ↓ 0.0 0 1

Hash (cost=2.58..2.58 rows=1 width=104) (actual time=0.255..0.255 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
37. 0.006 0.254 ↓ 0.0 0 1

Hash Join (cost=0.14..2.58 rows=1 width=104) (actual time=0.254..0.254 rows=0 loops=1)

  • Hash Cond: ((printings.id = ntp.printing_id) AND ((tu.src_barcode)::text = (ntp.src_plate_barcode)::text) AND ((tu.src_address)::text = (ntp.src_well_address)::text))
  • Join Filter: ((tu.well_address)::text = ANY ((ntp.addresses)::text[]))
38. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=0.07..2.46 rows=2 width=204) (never executed)

  • Hash Cond: (tu.printing_id = printings.id)
39. 0.000 0.000 ↓ 0.0 0

CTE Scan on to_upsert tu (cost=0.00..2.00 rows=100 width=200) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.04..0.04 rows=2 width=4) (never executed)

41. 0.000 0.000 ↓ 0.0 0

CTE Scan on printings (cost=0.00..0.04 rows=2 width=4) (never executed)

42. 0.000 0.248 ↓ 0.0 0 1

Hash (cost=0.04..0.04 rows=2 width=282) (actual time=0.248..0.248 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
43. 0.248 0.248 ↓ 0.0 0 1

CTE Scan on new_transfer_perturbations ntp (cost=0.00..0.04 rows=2 width=282) (actual time=0.248..0.248 rows=0 loops=1)