explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cJmw : 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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.063 728.346 ↑ 20.3 10 1

Insert on transfer_reagents (cost=264,991.82..265,613.76 rows=203 width=1,104) (actual time=728.245..728.346 rows=10 loops=1)

2.          

CTE to_upsert

3. 0.144 0.144 ↑ 10.0 10 1

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

4.          

CTE plates

5. 0.004 727.948 ↑ 101.5 2 1

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

6. 0.022 727.944 ↑ 20.3 10 1

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

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

Merge Join (cost=15.47..252,369.32 rows=203 width=38) (actual time=716.903..727.922 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. 47.465 47.465 ↓ 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.010..47.465 rows=91,081 loops=1)

9. 218.816 323.466 ↑ 1.4 918,860 1

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

10. 58.955 104.650 ↑ 10.0 126,050 1

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

11. 7.880 7.880 ↑ 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.880 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.000 0.070 ↓ 0.0 0 1

Unique (cost=869.87..869.88 rows=1 width=20) (actual time=0.070..0.070 rows=0 loops=1)

15. 0.041 0.070 ↓ 0.0 0 1

Sort (cost=869.87..869.87 rows=1 width=20) (actual time=0.070..0.070 rows=0 loops=1)

  • Sort Key: p.id, p.plate_id, p.layer, p.printed_at
  • Sort Method: quicksort Memory: 25kB
16. 0.000 0.029 ↓ 0.0 0 1

Hash Join (cost=7.16..869.86 rows=1 width=20) (actual time=0.029..0.029 rows=0 loops=1)

  • Hash Cond: (p.plate_id = plates_1.id)
17. 0.006 0.029 ↓ 0.0 0 1

Nested Loop (cost=0.56..860.50 rows=100 width=20) (actual time=0.029..0.029 rows=0 loops=1)

18. 0.003 0.003 ↑ 10.0 10 1

CTE Scan on to_upsert tu_2 (cost=0.00..2.00 rows=100 width=4) (actual time=0.000..0.003 rows=10 loops=1)

19. 0.020 0.020 ↓ 0.0 0 10

Index Scan using printings_pkey on printings p (cost=0.56..8.57 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=10)

  • Index Cond: (id = tu_2.printing_id)
20. 0.000 0.000 ↓ 0.0 0

Hash (cost=4.06..4.06 rows=203 width=4) (never executed)

21. 0.000 0.000 ↓ 0.0 0

CTE Scan on plates plates_1 (cost=0.00..4.06 rows=203 width=4) (never executed)

22.          

CTE deleted_transfers

23. 0.000 0.002 ↓ 0.0 0 1

Delete on transfer_perturbations (cost=0.59..11,123.41 rows=2,847 width=34) (actual time=0.002..0.002 rows=0 loops=1)

24. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.59..11,123.41 rows=2,847 width=34) (actual time=0.002..0.002 rows=0 loops=1)

25. 0.002 0.002 ↓ 0.0 0 1

HashAggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)

  • Group Key: printings_1.id
26. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on printings printings_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (printed_at IS NULL)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using printing_reagents_printing_id_idx on transfer_perturbations (cost=0.56..11,094.91 rows=2,847 width=10) (never executed)

  • Index Cond: (printing_id = printings_1.id)
28.          

CTE new_transfer_perturbations

29. 0.001 0.021 ↓ 0.0 0 1

Insert on transfer_perturbations transfer_perturbations_1 (cost=617.63..617.81 rows=2 width=1,435) (actual time=0.021..0.021 rows=0 loops=1)

30. 0.002 0.020 ↓ 0.0 0 1

Subquery Scan on *SELECT* (cost=617.63..617.81 rows=2 width=1,435) (actual time=0.020..0.020 rows=0 loops=1)

31. 0.001 0.018 ↓ 0.0 0 1

GroupAggregate (cost=617.63..617.71 rows=2 width=269) (actual time=0.018..0.018 rows=0 loops=1)

  • Group Key: tu_3.well_address, tu_3.src_barcode, tu_3.src_address, tu_3.transfer_volume, printings_2.id, tu_3.condition_id, tu_3.well_type_id, tu_3.control
32. 0.016 0.017 ↓ 0.0 0 1

Sort (cost=617.63..617.63 rows=2 width=237) (actual time=0.017..0.017 rows=0 loops=1)

  • Sort Key: tu_3.well_address, tu_3.src_barcode, tu_3.src_address, tu_3.transfer_volume, printings_2.id, tu_3.condition_id, tu_3.well_type_id, tu_3.control
  • Sort Method: quicksort Memory: 25kB
33. 0.000 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.00..617.62 rows=2 width=237) (actual time=0.001..0.001 rows=0 loops=1)

  • Join Filter: ((printings_2.id = tu_3.printing_id) OR ((((printings_2.layer)::character varying)::text = (tu_3.layer)::text) AND (plates_2.id = printings_2.plate_id)))
34. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on printings printings_2 (cost=0.00..0.02 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=1)

35. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..613.03 rows=203 width=273) (never executed)

  • Join Filter: ((tu_3.plate_id = plates_2.id) OR ((tu_3.plate_barcode)::text = (plates_2.barcode)::text) OR (((tu_3.experiment_label)::text = (plates_2.experiment_label)::text) AND (tu_3.plate_number = plates_2.experiment_plate_number)))
36. 0.000 0.000 ↓ 0.0 0

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

37. 0.000 0.000 ↓ 0.0 0

CTE Scan on plates plates_2 (cost=0.00..4.06 rows=203 width=742) (never executed)

38. 0.039 728.283 ↑ 20.3 10 1

Hash Left Join (cost=0.08..622.02 rows=203 width=1,104) (actual time=728.221..728.283 rows=10 loops=1)

  • Hash Cond: (((tu.src_barcode)::text = (ntp.src_plate_barcode)::text) AND ((tu.src_address)::text = (ntp.src_well_address)::text) AND (printings.id = ntp.printing_id))
  • Join Filter: ((tu.well_address)::text = ANY ((ntp.addresses)::text[]))
39. 0.015 728.221 ↑ 20.3 10 1

Nested Loop Left Join (cost=0.00..617.61 rows=203 width=196) (actual time=728.171..728.221 rows=10 loops=1)

  • Join Filter: (printings.id = tu.printing_id)
40. 0.026 728.136 ↑ 20.3 10 1

Nested Loop Left Join (cost=0.00..613.03 rows=203 width=196) (actual time=728.098..728.136 rows=10 loops=1)

  • Join Filter: ((plates.id = tu.plate_id) OR ((plates.barcode)::text = (tu.plate_barcode)::text) OR (((plates.experiment_label)::text = (tu.experiment_label)::text) AND (plates.experiment_plate_number = tu.plate_number)))
  • Rows Removed by Join Filter: 10
41. 0.150 0.150 ↑ 10.0 10 1

CTE Scan on to_upsert tu (cost=0.00..2.00 rows=100 width=268) (actual time=0.147..0.150 rows=10 loops=1)

42. 727.960 727.960 ↑ 101.5 2 10

CTE Scan on plates (cost=0.00..4.06 rows=203 width=742) (actual time=72.795..72.796 rows=2 loops=10)

43. 0.070 0.070 ↓ 0.0 0 10

CTE Scan on printings (cost=0.00..0.02 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=10)

44. 0.002 0.023 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
45. 0.021 0.021 ↓ 0.0 0 1

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