explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.070 8.479 ↓ 10.0 10 1

Insert on transfer_reagents (cost=472,166.73..472,176.60 rows=1 width=1,104) (actual time=8.299..8.479 rows=10 loops=1)

2.          

CTE to_upsert

3. 0.177 0.177 ↑ 10.0 10 1

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

4.          

CTE plates

5. 0.003 0.123 ↑ 100.0 2 1

Unique (cost=1,612.82..1,614.82 rows=200 width=15) (actual time=0.114..0.123 rows=2 loops=1)

6. 0.026 0.120 ↑ 20.0 10 1

Sort (cost=1,612.82..1,613.32 rows=200 width=15) (actual time=0.113..0.120 rows=10 loops=1)

  • Sort Key: pl.id, pl.barcode, pl.experiment_plate_number
  • Sort Method: quicksort Memory: 25kB
7. 0.014 0.094 ↑ 20.0 10 1

Nested Loop (cost=8.25..1,605.17 rows=200 width=15) (actual time=0.039..0.094 rows=10 loops=1)

8. 0.010 0.010 ↑ 10.0 10 1

CTE Scan on to_upsert tu_1 (cost=0.00..2.00 rows=100 width=36) (actual time=0.000..0.010 rows=10 loops=1)

9. 0.040 0.070 ↑ 2.0 1 10

Bitmap Heap Scan on plates pl (cost=8.25..16.01 rows=2 width=15) (actual time=0.006..0.007 rows=1 loops=10)

  • Recheck Cond: ((tu_1.plate_id = id) OR ((tu_1.plate_barcode)::text = (barcode)::text))
  • Heap Blocks: exact=10
10. 0.010 0.030 ↓ 0.0 0 10

BitmapOr (cost=8.25..8.25 rows=2 width=0) (actual time=0.003..0.003 rows=0 loops=10)

11. 0.020 0.020 ↑ 1.0 1 10

Bitmap Index Scan on plates_pkey (cost=0.00..4.11 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=10)

  • Index Cond: (tu_1.plate_id = id)
12. 0.000 0.000 ↓ 0.0 0 10

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

  • Index Cond: ((tu_1.plate_barcode)::text = (barcode)::text)
13.          

CTE printings

14. 0.010 0.280 ↓ 3.0 6 1

Unique (cost=458,912.37..458,912.39 rows=2 width=20) (actual time=0.268..0.280 rows=6 loops=1)

15. 0.028 0.270 ↓ 10.0 20 1

Sort (cost=458,912.37..458,912.37 rows=2 width=20) (actual time=0.266..0.270 rows=20 loops=1)

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

Nested Loop (cost=0.56..458,912.36 rows=2 width=20) (actual time=0.162..0.242 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 (((tu_2.printed_at IS NULL) AND (p.printed_at IS NULL)) OR ((tu_2.printed_at IS NOT NULL) AND (p.printed_at IS NOT NULL)))))
  • 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.000..0.003 rows=10 loops=1)

18. 0.018 0.170 ↑ 11,659.8 6 10

Materialize (cost=0.56..284,187.76 rows=69,959 width=24) (actual time=0.013..0.017 rows=6 loops=10)

19. 0.006 0.152 ↑ 11,659.8 6 1

Nested Loop (cost=0.56..283,837.96 rows=69,959 width=24) (actual time=0.127..0.152 rows=6 loops=1)

20. 0.124 0.124 ↑ 100.0 2 1

CTE Scan on plates plates_1 (cost=0.00..4.00 rows=200 width=4) (actual time=0.115..0.124 rows=2 loops=1)

21. 0.022 0.022 ↑ 116.7 3 2

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

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

CTE deleted_transfers

23. 2.894 2.958 ↓ 0.0 0 1

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

24. 0.008 0.064 ↑ 284.7 10 1

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

25. 0.011 0.020 ↓ 6.0 6 1

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

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

CTE Scan on printings printings_1 (cost=0.00..0.04 rows=1 width=32) (actual time=0.007..0.009 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.004..0.006 rows=2 loops=6)

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

CTE new_transfer_perturbations

29. 7.437 8.007 ↓ 2.5 10 1

Insert on transfer_perturbations transfer_perturbations_1 (cost=514.58..514.94 rows=4 width=1,435) (actual time=3.419..8.007 rows=10 loops=1)

30. 0.081 0.570 ↓ 2.5 10 1

Subquery Scan on *SELECT* (cost=514.58..514.94 rows=4 width=1,435) (actual time=0.488..0.570 rows=10 loops=1)

31. 0.044 0.489 ↓ 2.5 10 1

GroupAggregate (cost=514.58..514.74 rows=4 width=269) (actual time=0.448..0.489 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.069 0.445 ↓ 2.5 10 1

Sort (cost=514.58..514.59 rows=4 width=237) (actual time=0.438..0.445 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.039 0.376 ↓ 2.5 10 1

Nested Loop (cost=0.00..514.54 rows=4 width=237) (actual time=0.316..0.376 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. 0.283 0.283 ↓ 3.0 6 1

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

35. 0.026 0.054 ↑ 20.0 10 6

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

36. 0.016 0.028 ↑ 20.0 10 1

Nested Loop (cost=0.00..504.00 rows=200 width=273) (actual time=0.004..0.028 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
37. 0.002 0.002 ↑ 10.0 10 1

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

38. 0.010 0.010 ↑ 100.0 2 10

CTE Scan on plates plates_2 (cost=0.00..4.00 rows=200 width=222) (actual time=0.000..0.001 rows=2 loops=10)

39. 0.073 8.409 ↓ 10.0 10 1

Nested Loop (cost=0.14..10.01 rows=1 width=1,104) (actual time=8.271..8.409 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.020 8.300 ↓ 18.0 18 1

Nested Loop (cost=0.14..9.91 rows=1 width=144) (actual time=8.233..8.300 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
41. 0.040 8.262 ↓ 18.0 18 1

Hash Join (cost=0.14..2.91 rows=1 width=176) (actual time=8.231..8.262 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.179 0.179 ↑ 10.0 10 1

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

43. 0.016 8.043 ↓ 2.5 10 1

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

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

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

45. 0.018 0.018 ↑ 100.0 2 18

CTE Scan on plates (cost=0.00..4.00 rows=200 width=222) (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.140 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_reagents 0.410 ms 10 0.041 ms
for constraint plate_well_reagents_printing_reagent_id_fkey on transfer_perturbations 0.754 ms 10 0.075 ms
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_perturbations 0.341 ms 10 0.034 ms
for constraint printing_prescription_conditions_id_fkey on transfer_perturbations 0.678 ms 10 0.068 ms
for constraint printing_reagents_well_type_id_fkey on transfer_perturbations 0.489 ms 10 0.049 ms
for constraint printing_reagents_printing_id_fkey on transfer_perturbations 0.365 ms 10 0.036 ms
check_printing_reagents_mutability_trg on transfer_perturbations 1.051 ms 10 0.105 ms
platelet_printings_reagents_updated_at on transfer_perturbations 1.774 ms 10 0.177 ms
trg_stash_experiment_version_printing_reagents on transfer_perturbations 5.392 ms 20 0.270 ms