explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7W27

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 4,461.021 ↓ 16.0 16 1

Limit (cost=167,581.75..167,581.76 rows=1 width=693) (actual time=4,461.018..4,461.021 rows=16 loops=1)

2. 2.279 4,461.019 ↓ 16.0 16 1

Sort (cost=167,581.75..167,581.76 rows=1 width=693) (actual time=4,461.017..4,461.019 rows=16 loops=1)

  • Sort Key: (((COALESCE(ro.call_log_date, ro.needed_date))::date + "*VALUES*".column2)), (concat((plb.product_id)::text, '-', (plb.location_id)::text, '-', (plb.location_bin_id)::text, '-', (plb.product_lot_id)::text, '-', (plb.product_serial_id)::text, '-', (plb.contained_in_product_serial_id)::text, '-', ((CASE WHEN ((plb.table_name = 'contacts'::text) AND (lbt.type = 'Checked Out'::text)) THEN plb.table_id ELSE NULL::integer END))::text, '-', ((CASE WHEN ((plb.table_name = 'contacts'::text) AND (lbt.type = 'Checked Out'::text)) THEN plb.rental_order_line_id ELSE NULL::integer END))::text, '-', ("*VALUES*".column2)::text))
  • Sort Method: top-N heapsort Memory: 33kB
3. 18.866 4,458.740 ↓ 2,921.0 2,921 1

WindowAgg (cost=109,009.16..167,581.74 rows=1 width=693) (actual time=4,444.515..4,458.740 rows=2,921 loops=1)

4. 1,103.193 4,439.874 ↓ 2,921.0 2,921 1

Nested Loop Left Join (cost=109,009.16..167,581.65 rows=1 width=455) (actual time=1,582.610..4,439.874 rows=2,921 loops=1)

  • Join Filter: (p.id = plb.product_id)
  • Rows Removed by Join Filter: 20184110
5. 171.689 2,241.306 ↓ 2,921.0 2,921 1

Nested Loop (cost=109,009.16..167,261.15 rows=1 width=447) (actual time=1,582.550..2,241.306 rows=2,921 loops=1)

  • Join Filter: (a.type_id = t.id)
  • Rows Removed by Join Filter: 24250
6. 0.987 2,042.446 ↓ 27,171.0 27,171 1

Nested Loop (cost=109,009.16..167,260.03 rows=1 width=451) (actual time=1,175.478..2,042.446 rows=27,171 loops=1)

7. 23.321 1,905.554 ↓ 27,181.0 27,181 1

Nested Loop Left Join (cost=109,008.87..167,259.69 rows=1 width=451) (actual time=1,175.449..1,905.554 rows=27,181 loops=1)

8. 9.429 1,691.966 ↓ 27,181.0 27,181 1

Nested Loop (cost=109,008.58..167,259.33 rows=1 width=406) (actual time=1,175.408..1,691.966 rows=27,181 loops=1)

9. 2.798 1,546.632 ↓ 27,181.0 27,181 1

Nested Loop Left Join (cost=109,008.29..167,258.96 rows=1 width=357) (actual time=1,175.384..1,546.632 rows=27,181 loops=1)

10. 9.903 1,353.567 ↓ 27,181.0 27,181 1

Hash Join (cost=109,008.00..167,258.64 rows=1 width=350) (actual time=1,175.243..1,353.567 rows=27,181 loops=1)

  • Hash Cond: ((CASE WHEN ((plb.table_name = 'contacts'::text) AND (lbt.type = 'Checked Out'::text)) THEN plb.rental_order_line_id ELSE NULL::integer END) = rol.id)
11. 156.956 776.830 ↑ 65.4 5,792 1

GroupAggregate (cost=103,174.22..156,215.32 rows=378,865 width=268) (actual time=608.378..776.830 rows=5,792 loops=1)

  • Group Key: plb.location_id, plb.product_id, plb.location_bin_id, plb.product_location_id, plb.contained_in_product_serial_id, plb.product_lot_id, plb.product_serial_id, (CASE WHEN ((plb.table_name = 'contacts'::text) AND (lbt.type = 'Checked Out'::text)) THEN plb.table_id ELSE NULL::integer END), (CASE WHEN ((plb.table_name = 'contacts'::text) AND (lbt.type = 'Checked Out'::text)) THEN plb.rental_order_line_id ELSE NULL::integer END)
  • Filter: (COALESCE(sum((plb.quantity * plb.factor)), '0'::numeric) > '0'::numeric)
  • Rows Removed by Filter: 38820
12. 158.710 619.874 ↑ 4.5 83,438 1

Sort (cost=103,174.22..104,121.39 rows=378,865 width=126) (actual time=608.301..619.874 rows=83,438 loops=1)

  • Sort Key: plb.location_id, plb.product_id, plb.location_bin_id, plb.product_location_id, plb.contained_in_product_serial_id, plb.product_lot_id, plb.product_serial_id, (CASE WHEN ((plb.table_name = 'contacts'::text) AND (lbt.type = 'Checked Out'::text)) THEN plb.table_id ELSE NULL::integer END), (CASE WHEN ((plb.table_name = 'contacts'::text) AND (lbt.type = 'Checked Out'::text)) THEN plb.rental_order_line_id ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 14806kB
13. 133.170 461.164 ↑ 4.5 83,438 1

Hash Left Join (cost=8.01..53,658.37 rows=378,865 width=126) (actual time=2.452..461.164 rows=83,438 loops=1)

  • Hash Cond: (plb.location_bin_id = lb.id)
  • Filter: (CASE WHEN ((plb.table_name = 'contacts'::text) AND (lbt.type = 'Checked Out'::text)) THEN plb.rental_order_line_id ELSE NULL::integer END IS NOT NULL)
  • Rows Removed by Filter: 362243
14. 327.899 327.899 ↓ 1.2 445,681 1

Index Scan using plb_product_id_location_idx on product_location_bins plb (cost=0.42..42,722.72 rows=380,769 width=75) (actual time=2.301..327.899 rows=445,681 loops=1)

  • Index Cond: (product_id = ANY ('{248,187,10,836,68,36,178,35,236,69,181,70}'::integer[]))
  • Filter: ((status IS NULL) OR (status <> 'Void'::text))
  • Rows Removed by Filter: 41705
15. 0.020 0.095 ↑ 1.0 116 1

Hash (cost=6.14..6.14 rows=116 width=100) (actual time=0.095..0.095 rows=116 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
16. 0.027 0.075 ↑ 1.0 116 1

Hash Left Join (cost=1.38..6.14 rows=116 width=100) (actual time=0.047..0.075 rows=116 loops=1)

  • Hash Cond: (lb.location_bin_type_id = lbt.id)
17. 0.026 0.026 ↑ 1.0 116 1

Seq Scan on location_bins lb (cost=0.00..3.16 rows=116 width=8) (actual time=0.017..0.026 rows=116 loops=1)

18. 0.008 0.022 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=100) (actual time=0.022..0.022 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.014 0.014 ↑ 1.0 17 1

Seq Scan on location_bin_types lbt (cost=0.00..1.17 rows=17 width=100) (actual time=0.012..0.014 rows=17 loops=1)

20. 28.248 566.834 ↓ 109,176.0 109,176 1

Hash (cost=5,833.76..5,833.76 rows=1 width=86) (actual time=566.834..566.834 rows=109,176 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 11635kB
21. 0.000 538.586 ↓ 109,176.0 109,176 1

Nested Loop (cost=361.14..5,833.76 rows=1 width=86) (actual time=4.523..538.586 rows=109,176 loops=1)

22. 324.941 355.216 ↓ 94,139.0 94,139 1

Hash Left Join (cost=360.86..5,833.41 rows=1 width=82) (actual time=3.534..355.216 rows=94,139 loops=1)

  • Hash Cond: (ro.id = pfup.rental_order_id)
  • Join Filter: ((pfup.result = 'Disconnect'::text) OR (pfup.call_type = "*VALUES*".column1) OR ((regexp_replace(pfup.call_type, '\D'::text, ''::text, 'g'::text))::numeric > (regexp_replace("*VALUES*".column1, '\D'::text, ''::text, 'g'::text))::numeric))
  • Rows Removed by Join Filter: 35043
  • Filter: (pfup.id IS NULL)
  • Rows Removed by Filter: 25147
23. 20.761 26.835 ↑ 1.0 108,905 1

Nested Loop (cost=0.00..2,291.20 rows=108,905 width=82) (actual time=0.034..26.835 rows=108,905 loops=1)

24. 6.074 6.074 ↑ 1.0 21,781 1

Seq Scan on rental_orders ro (cost=0.00..929.81 rows=21,781 width=34) (actual time=0.024..6.074 rows=21,781 loops=1)

25. 0.000 0.000 ↑ 1.0 5 21,781

Materialize (cost=0.00..0.09 rows=5 width=48) (actual time=0.000..0.000 rows=5 loops=21,781)

26. 0.005 0.005 ↑ 1.0 5 1

Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=48) (actual time=0.002..0.005 rows=5 loops=1)

27. 1.721 3.440 ↑ 1.0 12,038 1

Hash (cost=210.38..210.38 rows=12,038 width=20) (actual time=3.440..3.440 rows=12,038 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 770kB
28. 1.719 1.719 ↑ 1.0 12,038 1

Seq Scan on patient_follow_up_calls pfup (cost=0.00..210.38 rows=12,038 width=20) (actual time=0.021..1.719 rows=12,038 loops=1)

29. 188.278 188.278 ↑ 1.0 1 94,139

Index Scan using rol_rental_order_idx on rental_order_lines rol (cost=0.29..0.34 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=94,139)

  • Index Cond: (rental_order_id = ro.id)
30. 190.267 190.267 ↑ 1.0 1 27,181

Index Scan using product_serials_pkey on product_serials ps (cost=0.29..0.31 rows=1 width=11) (actual time=0.007..0.007 rows=1 loops=27,181)

  • Index Cond: (id = plb.product_serial_id)
31. 135.905 135.905 ↑ 1.0 1 27,181

Index Scan using contacts_pkey on contacts c (cost=0.29..0.36 rows=1 width=57) (actual time=0.005..0.005 rows=1 loops=27,181)

  • Index Cond: (id = ro.contact_id)
32. 190.267 190.267 ↑ 1.0 1 27,181

Index Scan using addresses_pkey on addresses ad (cost=0.29..0.35 rows=1 width=53) (actual time=0.007..0.007 rows=1 loops=27,181)

  • Index Cond: (id = c.default_address_id)
33. 135.905 135.905 ↑ 1.0 1 27,181

Index Scan using accounts_pkey on accounts a (cost=0.29..0.33 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=27,181)

  • Index Cond: (id = c.account_id)
34. 27.171 27.171 ↑ 1.0 1 27,171

Seq Scan on types t (cost=0.00..1.11 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=27,171)

  • Filter: (type = 'Customer'::text)
  • Rows Removed by Filter: 8
35. 1,095.375 1,095.375 ↑ 1.0 6,911 2,921

Seq Scan on products p (cost=0.00..234.11 rows=6,911 width=16) (actual time=0.001..0.375 rows=6,911 loops=2,921)