explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i2qD : Optimization for: Optimization for: Optimization for: Optimization for: plan #PTt4; plan #1SCb; plan #3jBG; plan #eE6h

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.080 728.521 ↑ 20.4 10 1

Insert on transfer_reagents (cost=648,625.19..274,236,583.41 rows=204 width=1,104) (actual time=727.816..728.521 rows=10 loops=1)

2.          

CTE to_upsert

3. 0.148 0.148 ↑ 10.0 10 1

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

4.          

CTE plates

5. 0.005 724.317 ↑ 102.0 2 1

Unique (cost=251,980.08..251,982.63 rows=204 width=38) (actual time=724.307..724.317 rows=2 loops=1)

6. 0.037 724.312 ↑ 20.4 10 1

Sort (cost=251,980.08..251,980.59 rows=204 width=38) (actual time=724.307..724.312 rows=10 loops=1)

  • Sort Key: pl.id, pl.barcode, pl.experiment_plate_number, e.label
  • Sort Method: quicksort Memory: 25kB
7. 355.686 724.275 ↑ 20.4 10 1

Merge Join (cost=15.35..251,972.26 rows=204 width=38) (actual time=713.273..724.275 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.472 47.472 ↓ 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.007..47.472 rows=91,081 loops=1)

9. 216.257 321.117 ↑ 1.3 918,860 1

Materialize (cost=0.29..31,731.84 rows=1,240,100 width=99) (actual time=0.007..321.117 rows=918,860 loops=1)

10. 59.105 104.860 ↑ 9.8 126,050 1

Nested Loop (cost=0.29..28,631.59 rows=1,240,100 width=99) (actual time=0.005..104.860 rows=126,050 loops=1)

11. 7.940 7.940 ↓ 1.0 12,605 1

Index Scan using experiments_pkey on experiments e (cost=0.29..3,828.59 rows=12,401 width=27) (actual time=0.004..7.940 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.010 0.193 ↓ 3.0 6 1

Unique (cost=353,390.18..353,390.20 rows=2 width=12) (actual time=0.181..0.193 rows=6 loops=1)

15. 0.048 0.183 ↓ 10.0 20 1

Sort (cost=353,390.18..353,390.18 rows=2 width=12) (actual time=0.179..0.183 rows=20 loops=1)

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

Nested Loop (cost=0.56..353,390.17 rows=2 width=12) (actual time=0.052..0.135 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.003 0.003 ↑ 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.003 rows=10 loops=1)

18. 0.015 0.060 ↑ 8,776.0 6 10

Materialize (cost=0.56..221,879.81 rows=52,656 width=24) (actual time=0.002..0.006 rows=6 loops=10)

19. 0.006 0.045 ↑ 8,776.0 6 1

Nested Loop (cost=0.56..221,616.53 rows=52,656 width=24) (actual time=0.015..0.045 rows=6 loops=1)

20. 0.011 0.011 ↑ 102.0 2 1

CTE Scan on plates plates_1 (cost=0.00..4.08 rows=204 width=4) (actual time=0.001..0.011 rows=2 loops=1)

21. 0.028 0.028 ↑ 86.0 3 2

Index Scan using printings_plate_id_idx on printings p (cost=0.56..1,083.76 rows=258 width=20) (actual time=0.007..0.014 rows=3 loops=2)

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

CTE deleted_transfers

23. 1.395 1.454 ↓ 0.0 0 1

Delete on transfer_perturbations (cost=0.61..22,294.63 rows=5,693 width=34) (actual time=1.454..1.454 rows=0 loops=1)

24. 0.009 0.059 ↑ 569.3 10 1

Nested Loop (cost=0.61..22,294.63 rows=5,693 width=34) (actual time=0.022..0.059 rows=10 loops=1)

25. 0.003 0.020 ↓ 3.0 6 1

Unique (cost=0.05..0.06 rows=2 width=32) (actual time=0.014..0.020 rows=6 loops=1)

26. 0.010 0.017 ↓ 3.0 6 1

Sort (cost=0.05..0.06 rows=2 width=32) (actual time=0.014..0.017 rows=6 loops=1)

  • Sort Key: printings_1.id
  • Sort Method: quicksort Memory: 25kB
27. 0.007 0.007 ↓ 3.0 6 1

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

28. 0.030 0.030 ↑ 1,423.5 2 6

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

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

CTE new_transfer_perturbations

30. 2.652 2.951 ↓ 2.5 10 1

Insert on transfer_perturbations transfer_perturbations_1 (cost=626.83..627.19 rows=4 width=1,435) (actual time=0.901..2.951 rows=10 loops=1)

31. 0.095 0.299 ↓ 2.5 10 1

Subquery Scan on *SELECT* (cost=626.83..627.19 rows=4 width=1,435) (actual time=0.225..0.299 rows=10 loops=1)

32. 0.044 0.204 ↓ 2.5 10 1

GroupAggregate (cost=626.83..626.99 rows=4 width=269) (actual time=0.168..0.204 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
33. 0.056 0.160 ↓ 2.5 10 1

Sort (cost=626.83..626.84 rows=4 width=237) (actual time=0.156..0.160 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
34. 0.041 0.104 ↓ 2.5 10 1

Nested Loop (cost=0.00..626.79 rows=4 width=237) (actual time=0.036..0.104 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
35. 0.009 0.009 ↓ 3.0 6 1

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

36. 0.022 0.054 ↑ 20.4 10 6

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

37. 0.019 0.032 ↑ 20.4 10 1

Nested Loop (cost=0.00..616.04 rows=204 width=273) (actual time=0.005..0.032 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
38. 0.003 0.003 ↑ 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.003 rows=10 loops=1)

39. 0.010 0.010 ↑ 102.0 2 10

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

40. 0.247 728.441 ↑ 20.4 10 1

Nested Loop Left Join (cost=20,329.55..273,608,287.77 rows=204 width=1,104) (actual time=727.791..728.441 rows=10 loops=1)

  • Join Filter: ((tu.transfer_perturbation_id = etp.id) OR (((tu.well_address)::text = ANY ((etp.addresses)::text[])) AND ((tu.src_barcode)::text = (etp.src_plate_barcode)::text) AND ((tu.src_address)::text = (etp.src_well_address)::text) AND (printings.id = etp.printing_id)))
  • Rows Removed by Join Filter: 450
41. 0.035 727.774 ↑ 20.4 10 1

Hash Left Join (cost=0.15..631.79 rows=204 width=204) (actual time=727.663..727.774 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[]))
  • Rows Removed by Join Filter: 8
42. 0.057 724.753 ↑ 20.4 10 1

Nested Loop Left Join (cost=0.00..629.32 rows=204 width=200) (actual time=724.660..724.753 rows=10 loops=1)

  • 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: 50
43. 0.029 724.496 ↑ 20.4 10 1

Nested Loop Left Join (cost=0.00..616.04 rows=204 width=236) (actual time=724.463..724.496 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
44. 0.147 0.147 ↑ 10.0 10 1

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

45. 724.320 724.320 ↑ 102.0 2 10

CTE Scan on plates (cost=0.00..4.08 rows=204 width=742) (actual time=72.431..72.432 rows=2 loops=10)

46. 0.200 0.200 ↓ 3.0 6 10

CTE Scan on printings (cost=0.00..0.04 rows=2 width=12) (actual time=0.018..0.020 rows=6 loops=10)

47. 0.014 2.986 ↓ 2.5 10 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
48. 2.972 2.972 ↓ 2.5 10 1

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

49. 0.230 0.420 ↑ 883,481.3 46 10

Bitmap Heap Scan on transfer_perturbations etp (cost=20,329.40..20,409.44 rows=40,640,140 width=48) (actual time=0.022..0.042 rows=46 loops=10)

  • Recheck Cond: ((tu.transfer_perturbation_id = id) OR (((tu.src_barcode)::text = (src_plate_barcode)::text) AND ((tu.src_address)::text = (src_well_address)::text)))
  • Heap Blocks: exact=140
50. 0.010 0.190 ↓ 0.0 0 10

BitmapOr (cost=20,329.40..20,329.40 rows=20 width=0) (actual time=0.019..0.019 rows=0 loops=10)

51. 0.000 0.000 ↓ 0.0 0 10

Bitmap Index Scan on printing_reagents_pkey (cost=0.00..4.57 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=10)

  • Index Cond: (tu.transfer_perturbation_id = id)
52. 0.180 0.180 ↓ 4.0 76 10

Bitmap Index Scan on printing_reagents_src_plate_barcode_src_well_address_idx (cost=0.00..4.76 rows=19 width=0) (actual time=0.018..0.018 rows=76 loops=10)

  • Index Cond: (((tu.src_barcode)::text = (src_plate_barcode)::text) AND ((tu.src_address)::text = (src_well_address)::text))
Planning time : 1.915 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_reagents 0.353 ms 10 0.035 ms
for constraint plate_well_reagents_printing_reagent_id_fkey on transfer_perturbations 0.150 ms 10 0.015 ms
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_perturbations 0.112 ms 10 0.011 ms
for constraint printing_prescription_conditions_id_fkey on transfer_perturbations 0.518 ms 10 0.052 ms
for constraint printing_reagents_well_type_id_fkey on transfer_perturbations 0.562 ms 10 0.056 ms
for constraint printing_reagents_printing_id_fkey on transfer_perturbations 0.371 ms 10 0.037 ms
check_printing_reagents_mutability_trg on transfer_perturbations 0.245 ms 10 0.025 ms
platelet_printings_reagents_updated_at on transfer_perturbations 1.100 ms 10 0.110 ms
trg_stash_experiment_version_printing_reagents on transfer_perturbations 2.747 ms 20 0.137 ms