explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l7nH : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: 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; plan #GiTt; plan #SiSO; plan #f6PS; plan #X2Md; plan #mwvQ; plan #GzsZ

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.085 8,472.091 ↓ 5.0 10 1

Insert on transfer_reagents (cost=35,761,363.91..35,763,439.87 rows=2 width=1,104) (actual time=8,375.157..8,472.091 rows=10 loops=1)

2.          

CTE to_upsert

3. 0.168 0.168 ↑ 10.0 10 1

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

4.          

CTE plates

5. 0.003 4,836.134 ↑ 8,762.0 2 1

Unique (cost=16,010,695.53..16,010,870.77 rows=17,524 width=15) (actual time=4,836.118..4,836.134 rows=2 loops=1)

6. 0.036 4,836.131 ↑ 584.1 30 1

Sort (cost=16,010,695.53..16,010,739.34 rows=17,524 width=15) (actual time=4,836.117..4,836.131 rows=30 loops=1)

  • Sort Key: pl.id, pl.barcode, pl.experiment_plate_number
  • Sort Method: quicksort Memory: 26kB
7. 513.253 4,836.095 ↑ 584.1 30 1

Hash Join (cost=307,582.89..16,009,460.35 rows=17,524 width=15) (actual time=4,576.108..4,836.095 rows=30 loops=1)

  • Hash Cond: (p.plate_id = pl.id)
  • Join Filter: ((tu_1.plate_id = pl.id) OR ((tu_1.plate_barcode)::text = (pl.barcode)::text) OR (tu_1.printing_id = p.id))
  • Rows Removed by Join Filter: 1,793,360
8. 3,271.838 3,271.838 ↑ 42.7 179,339 1

Seq Scan on printings p (cost=0.00..1,183,493.67 rows=7,655,267 width=8) (actual time=0.007..3,271.838 rows=179,339 loops=1)

9. 365.958 1,051.004 ↑ 9.6 910,810 1

Hash (cost=111,932.14..111,932.14 rows=8,787,100 width=55) (actual time=1,051.004..1,051.004 rows=910,810 loops=1)

  • Buckets: 65,536 Batches: 256 Memory Usage: 786kB
10. 396.485 685.046 ↑ 9.6 910,810 1

Nested Loop (cost=0.00..111,932.14 rows=8,787,100 width=55) (actual time=0.171..685.046 rows=910,810 loops=1)

11. 0.181 0.181 ↑ 10.0 10 1

CTE Scan on to_upsert tu_1 (cost=0.00..2.00 rows=100 width=40) (actual time=0.162..0.181 rows=10 loops=1)

12. 259.055 288.380 ↓ 1.0 91,081 10

Materialize (cost=0.00..2,311.07 rows=87,871 width=15) (actual time=0.004..28.838 rows=91,081 loops=10)

13. 29.325 29.325 ↓ 1.0 91,081 1

Seq Scan on plates pl (cost=0.00..1,871.71 rows=87,871 width=15) (actual time=0.005..29.325 rows=91,081 loops=1)

14.          

CTE printings

15. 0.008 3,538.326 ↑ 31.7 6 1

Unique (cost=19,582,446.46..19,582,448.84 rows=190 width=20) (actual time=3,538.315..3,538.326 rows=6 loops=1)

16. 0.026 3,538.318 ↑ 9.5 20 1

Sort (cost=19,582,446.46..19,582,446.94 rows=190 width=20) (actual time=3,538.314..3,538.318 rows=20 loops=1)

  • Sort Key: p_1.id, p_1.plate_id, p_1.layer, p_1.printed_at
  • Sort Method: quicksort Memory: 25kB
17. 44.521 3,538.292 ↑ 9.5 20 1

Merge Join (cost=2,697,560.72..19,582,439.27 rows=190 width=20) (actual time=3,538.221..3,538.292 rows=20 loops=1)

  • Merge Cond: (plates_1.id = p_1.plate_id)
  • Join Filter: ((tu_2.printing_id = p_1.id) OR (((tu_2.layer)::text = ((p_1.layer)::character varying)::text) AND (plates_1.id = p_1.plate_id) AND (((tu_2.printed_at IS NULL) AND (p_1.printed_at IS NULL)) OR ((tu_2.printed_at IS NOT NULL) AND (p_1.printed_at IS NOT NULL)))))
  • Rows Removed by Join Filter: 40
18. 0.020 0.062 ↑ 87,620.0 20 1

Sort (cost=324,771.01..329,152.01 rows=1,752,400 width=48) (actual time=0.055..0.062 rows=20 loops=1)

  • Sort Key: plates_1.id
  • Sort Method: quicksort Memory: 26kB
19. 0.017 0.042 ↑ 87,620.0 20 1

Nested Loop (cost=0.00..35,225.24 rows=1,752,400 width=48) (actual time=0.002..0.042 rows=20 loops=1)

20. 0.005 0.005 ↑ 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.005 rows=10 loops=1)

21. 0.020 0.020 ↑ 8,762.0 2 10

CTE Scan on plates plates_1 (cost=0.00..350.48 rows=17,524 width=4) (actual time=0.000..0.002 rows=2 loops=10)

22. 71.452 3,493.709 ↑ 43.0 177,907 1

Materialize (cost=2,372,789.71..2,411,066.05 rows=7,655,267 width=20) (actual time=3,366.355..3,493.709 rows=177,907 loops=1)

23. 169.261 3,422.257 ↑ 43.0 177,853 1

Sort (cost=2,372,789.71..2,391,927.88 rows=7,655,267 width=20) (actual time=3,366.352..3,422.257 rows=177,853 loops=1)

  • Sort Key: p_1.plate_id
  • Sort Method: external merge Disk: 5,488kB
24. 3,252.996 3,252.996 ↑ 42.7 179,339 1

Seq Scan on printings p_1 (cost=0.00..1,183,493.67 rows=7,655,267 width=20) (actual time=0.011..3,252.996 rows=179,339 loops=1)

25.          

CTE deleted_transfers

26. 1.396 1.462 ↓ 0.0 0 1

Delete on transfer_perturbations (cost=4.37..11,127.19 rows=2,847 width=34) (actual time=1.462..1.462 rows=0 loops=1)

27. 0.009 0.066 ↑ 284.7 10 1

Nested Loop (cost=4.37..11,127.19 rows=2,847 width=34) (actual time=0.028..0.066 rows=10 loops=1)

28. 0.011 0.021 ↓ 6.0 6 1

HashAggregate (cost=3.80..3.81 rows=1 width=32) (actual time=0.016..0.021 rows=6 loops=1)

  • Group Key: printings_1.id
29. 0.010 0.010 ↓ 6.0 6 1

CTE Scan on printings printings_1 (cost=0.00..3.80 rows=1 width=32) (actual time=0.007..0.010 rows=6 loops=1)

  • Filter: (printed_at IS NULL)
30. 0.036 0.036 ↑ 1,423.5 2 6

Index Scan using printing_reagents_printing_id_idx on transfer_perturbations (cost=0.56..11,094.91 rows=2,847 width=10) (actual time=0.005..0.006 rows=2 loops=6)

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

CTE new_transfer_perturbations

32. 97.105 8,471.783 ↑ 1,756.7 10 1

Insert on transfer_perturbations transfer_perturbations_1 (cost=155,325.40..156,906.43 rows=17,567 width=1,435) (actual time=8,375.075..8,471.783 rows=10 loops=1)

33. 0.060 8,374.678 ↑ 1,756.7 10 1

Subquery Scan on *SELECT* (cost=155,325.40..156,906.43 rows=17,567 width=1,435) (actual time=8,374.611..8,374.678 rows=10 loops=1)

34. 0.041 8,374.618 ↑ 1,756.7 10 1

GroupAggregate (cost=155,325.40..156,028.08 rows=17,567 width=269) (actual time=8,374.584..8,374.618 rows=10 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
35. 0.032 8,374.577 ↑ 1,756.7 10 1

Sort (cost=155,325.40..155,369.32 rows=17,567 width=237) (actual time=8,374.573..8,374.577 rows=10 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: 26kB
36. 0.043 8,374.545 ↑ 1,756.7 10 1

Nested Loop (cost=0.00..152,102.38 rows=17,567 width=237) (actual time=8,374.460..8,374.545 rows=10 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)))
  • Rows Removed by Join Filter: 50
37. 0.019 4,836.152 ↑ 1,748.0 10 1

Nested Loop (cost=0.00..44,161.48 rows=17,480 width=273) (actual time=4,836.133..4,836.152 rows=10 loops=1)

  • Join Filter: ((tu_3.plate_id = plates_2.id) OR ((tu_3.plate_barcode)::text = (plates_2.barcode)::text))
  • Rows Removed by Join Filter: 10
38. 4,836.123 4,836.123 ↑ 8,762.0 2 1

CTE Scan on plates plates_2 (cost=0.00..350.48 rows=17,524 width=222) (actual time=4,836.122..4,836.123 rows=2 loops=1)

39. 0.010 0.010 ↑ 10.0 10 2

CTE Scan on to_upsert tu_3 (cost=0.00..2.00 rows=100 width=305) (actual time=0.002..0.005 rows=10 loops=2)

40. 3,538.350 3,538.350 ↑ 31.7 6 10

CTE Scan on printings printings_2 (cost=0.00..3.80 rows=190 width=12) (actual time=353.832..353.835 rows=6 loops=10)

41. 0.069 8,472.006 ↓ 5.0 10 1

Hash Join (cost=9.68..2,085.63 rows=2 width=1,104) (actual time=8,375.136..8,472.006 rows=10 loops=1)

  • Hash Cond: (ntp.printing_id = printings.id)
  • Join Filter: ((printings.id = tu.printing_id) OR ((((printings.layer)::character varying)::text = (tu.layer)::text) AND (plates.id = printings.plate_id)))
  • Rows Removed by Join Filter: 8
42. 0.039 8,471.931 ↑ 20.8 18 1

Nested Loop Left Join (cost=3.50..2,070.02 rows=375 width=144) (actual time=8,375.111..8,471.931 rows=18 loops=1)

  • Join Filter: ((plates.id = tu.plate_id) OR ((plates.barcode)::text = (tu.plate_barcode)::text))
  • Rows Removed by Join Filter: 18
43. 0.057 8,471.874 ↓ 9.0 18 1

Hash Join (cost=3.50..487.58 rows=2 width=176) (actual time=8,375.106..8,471.874 rows=18 loops=1)

  • Hash Cond: (((ntp.src_plate_barcode)::text = (tu.src_barcode)::text) AND ((ntp.src_well_address)::text = (tu.src_address)::text))
  • Join Filter: ((tu.well_address)::text = ANY ((ntp.addresses)::text[]))
  • Rows Removed by Join Filter: 34
44. 8,471.804 8,471.804 ↑ 1,756.7 10 1

CTE Scan on new_transfer_perturbations ntp (cost=0.00..351.34 rows=17,567 width=282) (actual time=8,375.078..8,471.804 rows=10 loops=1)

45. 0.006 0.013 ↑ 10.0 10 1

Hash (cost=2.00..2.00 rows=100 width=264) (actual time=0.013..0.013 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
46. 0.007 0.007 ↑ 10.0 10 1

CTE Scan on to_upsert tu (cost=0.00..2.00 rows=100 width=264) (actual time=0.002..0.007 rows=10 loops=1)

47. 0.018 0.018 ↑ 8,762.0 2 18

CTE Scan on plates (cost=0.00..350.48 rows=17,524 width=222) (actual time=0.000..0.001 rows=2 loops=18)

48. 0.004 0.006 ↑ 31.7 6 1

Hash (cost=3.80..3.80 rows=190 width=12) (actual time=0.006..0.006 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
49. 0.002 0.002 ↑ 31.7 6 1

CTE Scan on printings (cost=0.00..3.80 rows=190 width=12) (actual time=0.001..0.002 rows=6 loops=1)

Planning time : 1.240 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_reagents 0.135 ms 10 0.014 ms
for constraint plate_well_reagents_printing_reagent_id_fkey on transfer_perturbations 0.117 ms 10 0.012 ms
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_perturbations 0.098 ms 10 0.010 ms
for constraint printing_prescription_conditions_id_fkey on transfer_perturbations 0.114 ms 10 0.011 ms
for constraint printing_reagents_well_type_id_fkey on transfer_perturbations 0.106 ms 10 0.011 ms
for constraint printing_reagents_printing_id_fkey on transfer_perturbations 0.094 ms 10 0.009 ms
check_printing_reagents_mutability_trg on transfer_perturbations 0.227 ms 10 0.023 ms
platelet_printings_reagents_updated_at on transfer_perturbations 1.119 ms 10 0.112 ms
trg_stash_experiment_version_printing_reagents on transfer_perturbations 0.557 ms 20 0.028 ms