explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.063 3.105 ↓ 10.0 10 1

Insert on transfer_reagents (cost=185,558.23..185,565.06 rows=1 width=1,104) (actual time=2.926..3.105 rows=10 loops=1)

2.          

CTE to_upsert

3. 0.142 0.142 ↑ 10.0 10 1

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

4.          

CTE plates

5. 0.002 0.098 ↑ 50.0 2 1

Unique (cost=837.50..838.75 rows=100 width=38) (actual time=0.091..0.098 rows=2 loops=1)

6. 0.023 0.096 ↑ 10.0 10 1

Sort (cost=837.50..837.75 rows=100 width=38) (actual time=0.091..0.096 rows=10 loops=1)

  • Sort Key: pl.id, pl.barcode, pl.experiment_plate_number, e.label
  • Sort Method: quicksort Memory: 25kB
7. 0.006 0.073 ↑ 10.0 10 1

Nested Loop (cost=0.70..834.17 rows=100 width=38) (actual time=0.016..0.073 rows=10 loops=1)

8. 0.007 0.047 ↑ 10.0 10 1

Nested Loop (cost=0.42..798.50 rows=100 width=19) (actual time=0.011..0.047 rows=10 loops=1)

9. 0.010 0.010 ↑ 10.0 10 1

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

10. 0.030 0.030 ↑ 1.0 1 10

Index Scan using plates_pkey on plates pl (cost=0.42..7.96 rows=1 width=19) (actual time=0.002..0.003 rows=1 loops=10)

  • Index Cond: (id = tu_1.plate_id)
11. 0.020 0.020 ↑ 1.0 1 10

Index Scan using experiments_pkey on experiments e (cost=0.29..0.35 rows=1 width=27) (actual time=0.001..0.002 rows=1 loops=10)

  • Index Cond: (id = pl.experiment_id)
12.          

CTE printings

13. 0.008 0.250 ↓ 6.0 6 1

Unique (cost=184,410.69..184,410.71 rows=1 width=20) (actual time=0.238..0.250 rows=6 loops=1)

14. 0.023 0.242 ↓ 20.0 20 1

Sort (cost=184,410.69..184,410.70 rows=1 width=20) (actual time=0.236..0.242 rows=20 loops=1)

  • Sort Key: p.id, p.plate_id, p.layer, p.printed_at
  • Sort Method: quicksort Memory: 25kB
15. 0.065 0.219 ↓ 20.0 20 1

Nested Loop (cost=0.56..184,410.68 rows=1 width=20) (actual time=0.121..0.219 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
16. 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)

17. 0.022 0.150 ↑ 4,632.8 6 10

Materialize (cost=0.56..114,985.67 rows=27,797 width=24) (actual time=0.011..0.015 rows=6 loops=10)

18. 0.006 0.128 ↑ 4,632.8 6 1

Nested Loop (cost=0.56..114,846.69 rows=27,797 width=24) (actual time=0.107..0.128 rows=6 loops=1)

19. 0.102 0.102 ↑ 50.0 2 1

CTE Scan on plates plates_1 (cost=0.00..2.00 rows=100 width=4) (actual time=0.093..0.102 rows=2 loops=1)

20. 0.020 0.020 ↑ 92.7 3 2

Index Scan using printings_plate_id_idx on printings p (cost=0.56..1,145.67 rows=278 width=20) (actual time=0.008..0.010 rows=3 loops=2)

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

CTE new_transfer_perturbations

22. 2.142 2.699 ↓ 5.0 10 1

Insert on transfer_perturbations (cost=307.53..307.71 rows=2 width=1,435) (actual time=0.900..2.699 rows=10 loops=1)

23. 0.050 0.557 ↓ 5.0 10 1

Subquery Scan on *SELECT* (cost=307.53..307.71 rows=2 width=1,435) (actual time=0.489..0.557 rows=10 loops=1)

24. 0.042 0.507 ↓ 5.0 10 1

GroupAggregate (cost=307.53..307.61 rows=2 width=269) (actual time=0.473..0.507 rows=10 loops=1)

  • Group Key: tu_3.well_address, tu_3.src_barcode, tu_3.src_address, tu_3.transfer_volume, printings_1.id, tu_3.condition_id, tu_3.well_type_id, tu_3.control
25. 0.025 0.465 ↓ 5.0 10 1

Sort (cost=307.53..307.53 rows=2 width=237) (actual time=0.463..0.465 rows=10 loops=1)

  • Sort Key: tu_3.well_address, tu_3.src_barcode, tu_3.src_address, tu_3.transfer_volume, printings_1.id, tu_3.condition_id, tu_3.well_type_id, tu_3.control
  • Sort Method: quicksort Memory: 26kB
26. 0.044 0.440 ↓ 5.0 10 1

Nested Loop (cost=0.00..307.52 rows=2 width=237) (actual time=0.268..0.440 rows=10 loops=1)

  • Join Filter: ((printings_1.id = tu_3.printing_id) OR ((((printings_1.layer)::character varying)::text = (tu_3.layer)::text) AND (plates_2.id = printings_1.plate_id)))
  • Rows Removed by Join Filter: 50
27. 0.252 0.252 ↓ 6.0 6 1

CTE Scan on printings printings_1 (cost=0.00..0.02 rows=1 width=12) (actual time=0.238..0.252 rows=6 loops=1)

28. 0.066 0.144 ↑ 20.0 10 6

Nested Loop (cost=0.00..303.00 rows=200 width=273) (actual time=0.002..0.024 rows=10 loops=6)

  • 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
29. 0.018 0.018 ↑ 10.0 10 6

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=6)

30. 0.060 0.060 ↑ 50.0 2 60

CTE Scan on plates plates_2 (cost=0.00..2.00 rows=100 width=742) (actual time=0.000..0.001 rows=2 loops=60)

31. 0.061 3.042 ↓ 10.0 10 1

Nested Loop (cost=0.07..6.90 rows=1 width=1,104) (actual time=2.900..3.042 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
32. 0.021 2.945 ↓ 18.0 18 1

Nested Loop (cost=0.07..6.84 rows=1 width=144) (actual time=2.879..2.945 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
33. 0.036 2.906 ↓ 18.0 18 1

Hash Join (cost=0.07..2.84 rows=1 width=212) (actual time=2.876..2.906 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
34. 0.141 0.141 ↑ 10.0 10 1

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

35. 0.014 2.729 ↓ 5.0 10 1

Hash (cost=0.04..0.04 rows=2 width=282) (actual time=2.729..2.729 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
36. 2.715 2.715 ↓ 5.0 10 1

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

37. 0.018 0.018 ↑ 50.0 2 18

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

38. 0.036 0.036 ↓ 6.0 6 18

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

Planning time : 0.990 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_reagents 0.094 ms 10 0.009 ms
for constraint printing_prescription_conditions_id_fkey on transfer_perturbations 0.160 ms 10 0.016 ms
for constraint printing_reagents_well_type_id_fkey on transfer_perturbations 0.111 ms 10 0.011 ms
for constraint printing_reagents_printing_id_fkey on transfer_perturbations 0.096 ms 10 0.010 ms
platelet_printings_reagents_updated_at on transfer_perturbations 1.572 ms 10 0.157 ms