explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RIdx

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 449,781.255 ↓ 16.0 16 1

Limit (cost=162,661.56..162,661.56 rows=1 width=715) (actual time=449,781.246..449,781.255 rows=16 loops=1)

2. 2.810 449,781.248 ↓ 16.0 16 1

Sort (cost=162,661.56..162,661.56 rows=1 width=715) (actual time=449,781.245..449,781.248 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. 28.389 449,778.438 ↓ 2,831.0 2,831 1

WindowAgg (cost=103,136.72..162,661.55 rows=1 width=715) (actual time=449,759.495..449,778.438 rows=2,831 loops=1)

4. 3,932.380 449,750.049 ↓ 2,831.0 2,831 1

Nested Loop Left Join (cost=103,136.72..162,661.46 rows=1 width=477) (actual time=64,266.158..449,750.049 rows=2,831 loops=1)

  • Join Filter: (p.id = plb.product_id)
  • Rows Removed by Join Filter: 19562210
5. 68.123 442,165.679 ↓ 2,831.0 2,831 1

Nested Loop (cost=103,136.72..162,174.96 rows=1 width=469) (actual time=64,266.011..442,165.679 rows=2,831 loops=1)

  • Join Filter: (a.type_id = t.id)
  • Rows Removed by Join Filter: 24430
6. 37.482 442,043.034 ↓ 27,261.0 27,261 1

Nested Loop (cost=103,136.72..162,173.84 rows=1 width=473) (actual time=1,314.418..442,043.034 rows=27,261 loops=1)

7. 71.295 441,896.468 ↓ 27,271.0 27,271 1

Nested Loop Left Join (cost=103,136.43..162,173.48 rows=1 width=473) (actual time=1,314.401..441,896.468 rows=27,271 loops=1)

8. 237,256.512 441,716.089 ↓ 27,271.0 27,271 1

Nested Loop (cost=103,136.13..162,173.13 rows=1 width=420) (actual time=1,314.383..441,716.089 rows=27,271 loops=1)

  • Join Filter: (ro.contact_id = c.id)
  • Rows Removed by Join Filter: 1236930747
9. 46.734 1,808.776 ↓ 27,271.0 27,271 1

Nested Loop Left Join (cost=103,136.13..160,600.07 rows=1 width=357) (actual time=1,298.572..1,808.776 rows=27,271 loops=1)

10. 49.659 1,652.958 ↓ 27,271.0 27,271 1

Hash Join (cost=103,135.84..160,599.75 rows=1 width=350) (actual time=1,298.544..1,652.958 rows=27,271 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. 276.688 1,141.269 ↑ 64.2 5,819 1

GroupAggregate (cost=97,116.19..149,440.91 rows=373,748 width=268) (actual time=836.497..1,141.269 rows=5,819 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: 38884
12. 302.109 864.581 ↑ 4.5 83,593 1

Sort (cost=97,116.19..98,050.56 rows=373,748 width=69) (actual time=836.446..864.581 rows=83,593 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: external merge Disk: 7144kB
13. 240.275 562.472 ↑ 4.5 83,593 1

Hash Left Join (cost=8.01..53,543.63 rows=373,748 width=69) (actual time=0.247..562.472 rows=83,593 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: 363031
14. 322.046 322.046 ↓ 1.2 446,624 1

Index Scan using plb_product_id_location_idx on product_location_bins plb (cost=0.42..42,755.57 rows=375,626 width=76) (actual time=0.042..322.046 rows=446,624 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: 41787
15. 0.038 0.151 ↑ 1.0 116 1

Hash (cost=6.14..6.14 rows=116 width=23) (actual time=0.151..0.151 rows=116 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
16. 0.068 0.113 ↑ 1.0 116 1

Hash Left Join (cost=1.38..6.14 rows=116 width=23) (actual time=0.031..0.113 rows=116 loops=1)

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

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

18. 0.008 0.015 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=23) (actual time=0.015..0.015 rows=17 loops=1)

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

Seq Scan on location_bin_types lbt (cost=0.00..1.17 rows=17 width=23) (actual time=0.003..0.007 rows=17 loops=1)

20. 52.077 462.030 ↓ 109,446.0 109,446 1

Hash (cost=6,019.64..6,019.64 rows=1 width=86) (actual time=462.030..462.030 rows=109,446 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 2 (originally 1) Memory Usage: 8807kB
21. 47.500 409.953 ↓ 109,446.0 109,446 1

Nested Loop (cost=1,361.61..6,019.64 rows=1 width=86) (actual time=8.133..409.953 rows=109,446 loops=1)

22. 0.000 173.735 ↓ 94,359.0 94,359 1

Gather (cost=1,361.33..6,019.29 rows=1 width=82) (actual time=8.113..173.735 rows=94,359 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
23. 202.517 262.349 ↓ 47,180.0 47,180 2

Hash Left Join (cost=361.33..5,019.19 rows=1 width=82) (actual time=7.659..262.349 rows=47,180 loops=2)

  • 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: 17610
  • Filter: (pfup.id IS NULL)
  • Rows Removed by Filter: 12613
24. 48.455 52.338 ↑ 1.2 54,592 2

Nested Loop (cost=0.00..2,782.90 rows=64,200 width=82) (actual time=0.018..52.338 rows=54,592 loops=2)

25. 3.881 3.881 ↑ 1.2 10,918 2

Parallel Seq Scan on rental_orders ro (cost=0.00..1,338.40 rows=12,840 width=34) (actual time=0.010..3.881 rows=10,918 loops=2)

26. 0.002 0.002 ↑ 1.0 5 21,837

Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=48) (actual time=0.000..0.002 rows=5 loops=21,837)

27. 3.944 7.494 ↓ 1.0 12,089 2

Hash (cost=210.59..210.59 rows=12,059 width=20) (actual time=7.494..7.494 rows=12,089 loops=2)

  • Buckets: 16384 Batches: 1 Memory Usage: 772kB
28. 3.550 3.550 ↓ 1.0 12,089 2

Seq Scan on patient_follow_up_calls pfup (cost=0.00..210.59 rows=12,059 width=20) (actual time=0.013..3.550 rows=12,089 loops=2)

29. 188.718 188.718 ↑ 1.0 1 94,359

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

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

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

  • Index Cond: (id = plb.product_serial_id)
31. 202,650.801 202,650.801 ↓ 1.0 45,358 27,271

Seq Scan on contacts c (cost=0.00..1,006.36 rows=45,336 width=71) (actual time=0.003..7.431 rows=45,358 loops=27,271)

32. 109.084 109.084 ↑ 1.0 1 27,271

Index Scan using addresses_pkey on addresses ad (cost=0.29..0.34 rows=1 width=61) (actual time=0.004..0.004 rows=1 loops=27,271)

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

Index Scan using accounts_pkey on accounts a (cost=0.29..0.34 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=27,271)

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

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

  • Filter: (type = 'Customer'::text)
  • Rows Removed by Filter: 8
35. 3,651.990 3,651.990 ↑ 1.0 6,911 2,831

Seq Scan on products p (cost=0.00..400.11 rows=6,911 width=16) (actual time=0.003..1.290 rows=6,911 loops=2,831)