explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.087 730.671 ↓ 10.0 10 1

Insert on transfer_reagents (cost=635,737.02..635,748.01 rows=1 width=1,104) (actual time=730.481..730.671 rows=10 loops=1)

2.          

CTE to_upsert

3. 0.164 0.164 ↑ 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.164 rows=10 loops=1)

4.          

CTE plates

5. 0.005 722.118 ↑ 101.5 2 1

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

6. 0.019 722.113 ↑ 20.3 10 1

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

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

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

9. 216.247 320.298 ↑ 1.4 918,860 1

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

10. 58.660 104.051 ↑ 10.0 126,050 1

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

11. 7.576 7.576 ↑ 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.576 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.007 722.287 ↓ 3.0 6 1

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

15. 0.034 722.280 ↓ 10.0 20 1

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

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

Nested Loop (cost=0.56..371,608.64 rows=2 width=20) (actual time=722.174..722.246 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.000..0.004 rows=10 loops=1)

18. 0.016 722.160 ↑ 9,404.5 6 10

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

19. 0.007 722.144 ↑ 9,404.5 6 1

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

20. 722.121 722.121 ↑ 101.5 2 1

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

21. 0.016 0.016 ↑ 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.006..0.008 rows=3 loops=2)

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

CTE deleted_transfers

23. 2.777 2.842 ↓ 0.0 0 1

Delete on transfer_perturbations (cost=0.61..11,123.43 rows=2,847 width=34) (actual time=2.842..2.842 rows=0 loops=1)

24. 0.011 0.065 ↑ 284.7 10 1

Nested Loop (cost=0.61..11,123.43 rows=2,847 width=34) (actual time=0.024..0.065 rows=10 loops=1)

25. 0.010 0.018 ↓ 6.0 6 1

HashAggregate (cost=0.04..0.05 rows=1 width=32) (actual time=0.014..0.018 rows=6 loops=1)

  • Group Key: printings_1.id
26. 0.008 0.008 ↓ 6.0 6 1

CTE Scan on printings printings_1 (cost=0.00..0.04 rows=1 width=32) (actual time=0.005..0.008 rows=6 loops=1)

  • Filter: (printed_at IS NULL)
27. 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)
28.          

CTE new_transfer_perturbations

29. 7.579 730.173 ↓ 2.5 10 1

Insert on transfer_perturbations transfer_perturbations_1 (cost=623.77..624.13 rows=4 width=1,435) (actual time=725.638..730.173 rows=10 loops=1)

30. 0.101 722.594 ↓ 2.5 10 1

Subquery Scan on *SELECT* (cost=623.77..624.13 rows=4 width=1,435) (actual time=722.510..722.594 rows=10 loops=1)

31. 0.043 722.493 ↓ 2.5 10 1

GroupAggregate (cost=623.77..623.93 rows=4 width=269) (actual time=722.453..722.493 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
32. 0.060 722.450 ↓ 2.5 10 1

Sort (cost=623.77..623.78 rows=4 width=237) (actual time=722.443..722.450 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
33. 0.042 722.390 ↓ 2.5 10 1

Nested Loop (cost=0.00..623.73 rows=4 width=237) (actual time=722.317..722.390 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
34. 722.294 722.294 ↓ 3.0 6 1

CTE Scan on printings printings_2 (cost=0.00..0.04 rows=2 width=12) (actual time=722.278..722.294 rows=6 loops=1)

35. 0.023 0.054 ↑ 20.3 10 6

Materialize (cost=0.00..614.04 rows=203 width=273) (actual time=0.002..0.009 rows=10 loops=6)

36. 0.019 0.031 ↑ 20.3 10 1

Nested Loop (cost=0.00..613.03 rows=203 width=273) (actual time=0.005..0.031 rows=10 loops=1)

  • 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)))
  • Rows Removed by Join Filter: 10
37. 0.002 0.002 ↑ 10.0 10 1

CTE Scan on to_upsert tu_3 (cost=0.00..2.00 rows=100 width=341) (actual time=0.000..0.002 rows=10 loops=1)

38. 0.010 0.010 ↑ 101.5 2 10

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

39. 0.089 730.584 ↓ 10.0 10 1

Nested Loop (cost=0.14..11.13 rows=1 width=1,104) (actual time=730.443..730.584 rows=10 loops=1)

  • Join Filter: ((ntp.printing_id = printings.id) AND ((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: 98
40. 0.027 730.459 ↓ 18.0 18 1

Nested Loop (cost=0.14..11.03 rows=1 width=144) (actual time=730.390..730.459 rows=18 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: 18
41. 0.039 730.414 ↓ 18.0 18 1

Hash Join (cost=0.14..2.91 rows=1 width=212) (actual time=730.386..730.414 rows=18 loops=1)

  • Hash Cond: (((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[]))
  • Rows Removed by Join Filter: 34
42. 0.165 0.165 ↑ 10.0 10 1

CTE Scan on to_upsert tu (cost=0.00..2.00 rows=100 width=300) (actual time=0.163..0.165 rows=10 loops=1)

43. 0.016 730.210 ↓ 2.5 10 1

Hash (cost=0.08..0.08 rows=4 width=282) (actual time=730.210..730.210 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
44. 730.194 730.194 ↓ 2.5 10 1

CTE Scan on new_transfer_perturbations ntp (cost=0.00..0.08 rows=4 width=282) (actual time=725.640..730.194 rows=10 loops=1)

45. 0.018 0.018 ↑ 101.5 2 18

CTE Scan on plates (cost=0.00..4.06 rows=203 width=742) (actual time=0.000..0.001 rows=2 loops=18)

46. 0.036 0.036 ↓ 3.0 6 18

CTE Scan on printings (cost=0.00..0.04 rows=2 width=12) (actual time=0.000..0.002 rows=6 loops=18)

Planning time : 2.427 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_reagents 0.382 ms 10 0.038 ms
for constraint plate_well_reagents_printing_reagent_id_fkey on transfer_perturbations 0.765 ms 10 0.076 ms
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_perturbations 0.347 ms 10 0.035 ms
for constraint printing_prescription_conditions_id_fkey on transfer_perturbations 0.709 ms 10 0.071 ms
for constraint printing_reagents_well_type_id_fkey on transfer_perturbations 0.491 ms 10 0.049 ms
for constraint printing_reagents_printing_id_fkey on transfer_perturbations 0.362 ms 10 0.036 ms
check_printing_reagents_mutability_trg on transfer_perturbations 1.046 ms 10 0.105 ms
platelet_printings_reagents_updated_at on transfer_perturbations 1.667 ms 10 0.167 ms
trg_stash_experiment_version_printing_reagents on transfer_perturbations: time=5.419 calls=20"Trigger platelet_printings_reagents_updated_at on transfer_perturbations 5.963 ms 10 0.596 ms