explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Sqqa

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 42.728 19,241.207 ↓ 62,505.0 62,505 1

Nested Loop Left Join (cost=874,185.55..1,151,533.87 rows=1 width=666) (actual time=15,687.493..19,241.207 rows=62,505 loops=1)

  • Join Filter: ((p.provider_id = nws.provider_id) AND (p.provider_location_id = nws.provider_location_id) AND (p.product_id = nws.product_id))
2.          

CTE networks

3. 1,866.590 10,500.555 ↑ 1.1 62,505 1

GroupAggregate (cost=572,868.68..665,852.57 rows=65,713 width=445) (actual time=8,424.878..10,500.555 rows=62,505 loops=1)

  • Group Key: ock.strenuus_id, ock.strenuus_location_id, op.product_id, os.specialty_id, os.board_cert_id
4. 79.722 8,446.450 ↓ 1.4 93,418 1

Sort (cost=572,868.68..573,032.96 rows=65,713 width=311) (actual time=8,424.675..8,446.450 rows=93,418 loops=1)

  • Sort Key: ock.strenuus_id, ock.strenuus_location_id, op.product_id, os.specialty_id, os.board_cert_id
  • Sort Method: quicksort Memory: 17154kB
5. 22.314 8,366.728 ↓ 1.4 93,418 1

Hash Left Join (cost=561,507.33..567,610.36 rows=65,713 width=311) (actual time=8,252.396..8,366.728 rows=93,418 loops=1)

  • Hash Cond: (op.composite_key_id = ot_mga.composite_key_id)
  • Join Filter: (os.provider_type_id = 13)
6. 24.118 8,327.865 ↓ 1.4 93,418 1

Hash Left Join (cost=556,777.42..562,634.01 rows=65,713 width=287) (actual time=8,235.827..8,327.865 rows=93,418 loops=1)

  • Hash Cond: (op.composite_key_id = ot_hai.composite_key_id)
  • Join Filter: (os.provider_type_id = 3)
7. 45.720 8,287.111 ↓ 1.4 93,418 1

Hash Right Join (cost=552,047.51..557,657.66 rows=65,713 width=255) (actual time=8,219.178..8,287.111 rows=93,418 loops=1)

  • Hash Cond: (ot_anpt.composite_key_id = op.composite_key_id)
8. 22.704 22.704 ↑ 1.0 59,493 1

Seq Scan on output_tag_518191 ot_anpt (cost=0.00..4,729.90 rows=59,499 width=20) (actual time=0.010..22.704 rows=59,493 loops=1)

  • Filter: (data_element_id = 71)
  • Rows Removed by Filter: 185459
9. 30.358 8,218.687 ↓ 1.4 93,418 1

Hash (cost=551,226.18..551,226.18 rows=65,706 width=243) (actual time=8,218.687..8,218.687 rows=93,418 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 12728kB
10. 35.561 8,188.329 ↓ 1.4 93,418 1

Hash Right Join (cost=545,517.60..551,226.18 rows=65,706 width=243) (actual time=8,127.996..8,188.329 rows=93,418 loops=1)

  • Hash Cond: (ot_han.composite_key_id = op.composite_key_id)
11. 17.373 134.688 ↓ 1.0 57,069 1

Hash Left Join (cost=7,004.34..11,881.72 rows=56,184 width=47) (actual time=109.496..134.688 rows=57,069 loops=1)

  • Hash Cond: (ot_han.data_value = ohad.hospital_affil_id)
12. 19.522 19.522 ↓ 1.0 57,069 1

Seq Scan on output_tag_518191 ot_han (cost=0.00..4,729.90 rows=56,184 width=20) (actual time=10.773..19.522 rows=57,069 loops=1)

  • Filter: (data_element_id = 1)
  • Rows Removed by Filter: 187883
13. 51.493 97.793 ↑ 1.0 218,415 1

Hash (cost=4,274.15..4,274.15 rows=218,415 width=45) (actual time=97.793..97.793 rows=218,415 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 18747kB
14. 46.300 46.300 ↑ 1.0 218,415 1

Seq Scan on output_hospital_affil_domain ohad (cost=0.00..4,274.15 rows=218,415 width=45) (actual time=0.006..46.300 rows=218,415 loops=1)

15. 20.772 8,018.080 ↑ 1.0 65,691 1

Hash (cost=537,691.94..537,691.94 rows=65,706 width=204) (actual time=8,018.080..8,018.080 rows=65,691 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 7956kB
16. 14.105 7,997.308 ↑ 1.0 65,691 1

Merge Left Join (cost=536,409.04..537,691.94 rows=65,706 width=204) (actual time=7,901.092..7,997.308 rows=65,691 loops=1)

  • Merge Cond: (op.composite_key_id = ot_ps.composite_key_id)
17. 18.337 7,968.738 ↑ 1.0 65,691 1

Merge Left Join (cost=531,679.13..532,797.75 rows=65,706 width=192) (actual time=7,886.625..7,968.738 rows=65,691 loops=1)

  • Merge Cond: (op.composite_key_id = ot_enrid.composite_key_id)
18. 13.372 7,931.537 ↑ 1.0 65,691 1

Merge Left Join (cost=526,428.72..527,250.12 rows=65,706 width=180) (actual time=7,868.747..7,931.537 rows=65,691 loops=1)

  • Merge Cond: (op.composite_key_id = ot_tlbl.composite_key_id)
19. 13.261 7,903.608 ↑ 1.0 65,691 1

Merge Left Join (cost=521,698.81..522,355.92 rows=65,706 width=168) (actual time=7,854.189..7,903.608 rows=65,691 loops=1)

  • Merge Cond: (op.composite_key_id = ot_tlvl.composite_key_id)
20. 13.087 7,875.394 ↑ 1.0 65,691 1

Merge Left Join (cost=516,968.90..517,461.73 rows=65,706 width=156) (actual time=7,839.234..7,875.394 rows=65,691 loops=1)

  • Merge Cond: (op.composite_key_id = ot_anp.composite_key_id)
21. 16.434 7,846.173 ↑ 1.0 65,691 1

Merge Left Join (cost=512,238.99..512,567.54 rows=65,706 width=144) (actual time=7,823.097..7,846.173 rows=65,691 loops=1)

  • Merge Cond: (op.composite_key_id = ot_gnpi.composite_key_id)
22. 31.734 7,813.437 ↑ 1.0 65,691 1

Sort (cost=507,509.08..507,673.35 rows=65,706 width=132) (actual time=7,806.791..7,813.437 rows=65,691 loops=1)

  • Sort Key: op.composite_key_id
  • Sort Method: quicksort Memory: 12319kB
23. 3,905.268 7,781.703 ↑ 1.0 65,691 1

Hash Join (cost=20,881.07..502,251.37 rows=65,706 width=132) (actual time=224.547..7,781.703 rows=65,691 loops=1)

  • Hash Cond: (ock.id = op.composite_key_id)
24. 3,654.669 3,654.669 ↑ 1.0 25,095,354 1

Seq Scan on output_composite_key ock (cost=0.00..386,605.36 rows=25,095,436 width=12) (actual time=0.299..3,654.669 rows=25,095,354 loops=1)

25. 19.565 221.766 ↑ 1.0 65,691 1

Hash (cost=20,059.74..20,059.74 rows=65,706 width=132) (actual time=221.766..221.766 rows=65,691 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 8066kB
26. 14.577 202.201 ↑ 1.0 65,691 1

Hash Left Join (cost=13,950.54..20,059.74 rows=65,706 width=132) (actual time=121.867..202.201 rows=65,691 loops=1)

  • Hash Cond: (op.composite_key_id = ot_nm.composite_key_id)
27. 14.771 171.850 ↑ 1.0 65,691 1

Hash Left Join (cost=9,220.63..15,083.42 rows=65,706 width=120) (actual time=106.075..171.850 rows=65,691 loops=1)

  • Hash Cond: (op.composite_key_id = ot_fl.composite_key_id)
28. 28.848 125.207 ↑ 1.0 65,691 1

Hash Right Join (cost=4,490.71..10,107.10 rows=65,706 width=108) (actual time=74.190..125.207 rows=65,691 loops=1)

  • Hash Cond: (ot_gn.composite_key_id = op.composite_key_id)
29. 22.658 22.658 ↑ 1.0 61,121 1

Seq Scan on output_tag_518191 ot_gn (cost=0.00..4,729.90 rows=61,181 width=52) (actual time=0.248..22.658 rows=61,121 loops=1)

  • Filter: (data_element_id = 2)
  • Rows Removed by Filter: 183831
30. 14.970 73.701 ↑ 1.0 63,893 1

Hash (cost=3,692.05..3,692.05 rows=63,893 width=64) (actual time=73.701..73.701 rows=63,893 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 4924kB
31. 24.935 58.731 ↑ 1.0 63,893 1

Hash Join (cost=1,776.59..3,692.05 rows=63,893 width=64) (actual time=22.606..58.731 rows=63,893 loops=1)

  • Hash Cond: (os.composite_key_id = op.composite_key_id)
32. 11.562 11.562 ↑ 1.0 63,893 1

Seq Scan on output_specialty_518191 os (cost=0.00..1,036.93 rows=63,893 width=20) (actual time=0.170..11.562 rows=63,893 loops=1)

33. 10.897 22.234 ↑ 1.0 59,493 1

Hash (cost=1,032.93..1,032.93 rows=59,493 width=44) (actual time=22.234..22.234 rows=59,493 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3301kB
34. 11.337 11.337 ↑ 1.0 59,493 1

Seq Scan on output_product_518191 op (cost=0.00..1,032.93 rows=59,493 width=44) (actual time=0.011..11.337 rows=59,493 loops=1)

35. 0.000 31.872 ↓ 0.0 0 1

Hash (cost=4,729.90..4,729.90 rows=1 width=20) (actual time=31.872..31.872 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
36. 31.872 31.872 ↓ 0.0 0 1

Seq Scan on output_tag_518191 ot_fl (cost=0.00..4,729.90 rows=1 width=20) (actual time=31.872..31.872 rows=0 loops=1)

  • Filter: (data_element_id = 117)
  • Rows Removed by Filter: 244952
37. 0.001 15.774 ↓ 0.0 0 1

Hash (cost=4,729.90..4,729.90 rows=1 width=20) (actual time=15.774..15.774 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
38. 15.773 15.773 ↓ 0.0 0 1

Seq Scan on output_tag_518191 ot_nm (cost=0.00..4,729.90 rows=1 width=20) (actual time=15.773..15.773 rows=0 loops=1)

  • Filter: (data_element_id = 176)
  • Rows Removed by Filter: 244952
39. 0.010 16.302 ↓ 0.0 0 1

Sort (cost=4,729.91..4,729.91 rows=1 width=20) (actual time=16.302..16.302 rows=0 loops=1)

  • Sort Key: ot_gnpi.composite_key_id
  • Sort Method: quicksort Memory: 25kB
40. 16.292 16.292 ↓ 0.0 0 1

Seq Scan on output_tag_518191 ot_gnpi (cost=0.00..4,729.90 rows=1 width=20) (actual time=16.292..16.292 rows=0 loops=1)

  • Filter: (data_element_id = 83)
  • Rows Removed by Filter: 244952
41. 0.005 16.134 ↓ 0.0 0 1

Sort (cost=4,729.91..4,729.91 rows=1 width=20) (actual time=16.134..16.134 rows=0 loops=1)

  • Sort Key: ot_anp.composite_key_id
  • Sort Method: quicksort Memory: 25kB
42. 16.129 16.129 ↓ 0.0 0 1

Seq Scan on output_tag_518191 ot_anp (cost=0.00..4,729.90 rows=1 width=20) (actual time=16.129..16.129 rows=0 loops=1)

  • Filter: (data_element_id = 111)
  • Rows Removed by Filter: 244952
43. 0.005 14.953 ↓ 0.0 0 1

Sort (cost=4,729.91..4,729.91 rows=1 width=20) (actual time=14.953..14.953 rows=0 loops=1)

  • Sort Key: ot_tlvl.composite_key_id
  • Sort Method: quicksort Memory: 25kB
44. 14.948 14.948 ↓ 0.0 0 1

Seq Scan on output_tag_518191 ot_tlvl (cost=0.00..4,729.90 rows=1 width=20) (actual time=14.948..14.948 rows=0 loops=1)

  • Filter: (data_element_id = 137)
  • Rows Removed by Filter: 244952
45. 0.008 14.557 ↓ 0.0 0 1

Sort (cost=4,729.91..4,729.91 rows=1 width=20) (actual time=14.557..14.557 rows=0 loops=1)

  • Sort Key: ot_tlbl.composite_key_id
  • Sort Method: quicksort Memory: 25kB
46. 14.549 14.549 ↓ 0.0 0 1

Seq Scan on output_tag_518191 ot_tlbl (cost=0.00..4,729.90 rows=1 width=20) (actual time=14.549..14.549 rows=0 loops=1)

  • Filter: (data_element_id = 150)
  • Rows Removed by Filter: 244952
47. 3.527 18.864 ↓ 1.1 8,736 1

Sort (cost=5,250.40..5,270.47 rows=8,026 width=20) (actual time=17.873..18.864 rows=8,736 loops=1)

  • Sort Key: ot_enrid.composite_key_id
  • Sort Method: quicksort Memory: 1032kB
48. 15.337 15.337 ↓ 1.0 8,292 1

Seq Scan on output_tag_518191 ot_enrid (cost=0.00..4,729.90 rows=8,026 width=20) (actual time=14.014..15.337 rows=8,292 loops=1)

  • Filter: (data_element_id = 96)
  • Rows Removed by Filter: 236660
49. 0.004 14.465 ↓ 0.0 0 1

Sort (cost=4,729.91..4,729.91 rows=1 width=20) (actual time=14.465..14.465 rows=0 loops=1)

  • Sort Key: ot_ps.composite_key_id
  • Sort Method: quicksort Memory: 25kB
50. 14.461 14.461 ↓ 0.0 0 1

Seq Scan on output_tag_518191 ot_ps (cost=0.00..4,729.90 rows=1 width=20) (actual time=14.461..14.461 rows=0 loops=1)

  • Filter: (data_element_id = 10)
  • Rows Removed by Filter: 244952
51. 0.001 16.636 ↓ 0.0 0 1

Hash (cost=4,729.90..4,729.90 rows=1 width=40) (actual time=16.636..16.636 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
52. 16.635 16.635 ↓ 0.0 0 1

Seq Scan on output_tag_518191 ot_hai (cost=0.00..4,729.90 rows=1 width=40) (actual time=16.635..16.635 rows=0 loops=1)

  • Filter: ((data_value_2 IS NOT NULL) AND (data_element_id = 1))
  • Rows Removed by Filter: 244952
53. 0.001 16.549 ↓ 0.0 0 1

Hash (cost=4,729.90..4,729.90 rows=1 width=40) (actual time=16.548..16.549 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
54. 16.548 16.548 ↓ 0.0 0 1

Seq Scan on output_tag_518191 ot_mga (cost=0.00..4,729.90 rows=1 width=40) (actual time=16.548..16.548 rows=0 loops=1)

  • Filter: ((data_value_2 IS NOT NULL) AND (data_element_id = 1))
  • Rows Removed by Filter: 244952
55.          

SubPlan (for GroupAggregate)

56. 125.010 187.515 ↑ 1.0 1 62,505

Aggregate (cost=1.25..1.26 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=62,505)

57. 62.505 62.505 ↓ 0.0 0 62,505

Function Scan on unnest x (cost=0.00..1.00 rows=100 width=32) (actual time=0.001..0.001 rows=0 loops=62,505)

58.          

CTE networks_with_sort

59. 0.001 9.548 ↓ 0.0 0 1

GroupAggregate (cost=4,951.70..5,013.87 rows=322 width=76) (actual time=9.548..9.548 rows=0 loops=1)

  • Group Key: networks.product_id, networks.provider_id, networks.provider_location_id
60. 0.007 9.547 ↓ 0.0 0 1

Sort (cost=4,951.70..4,957.27 rows=2,229 width=20) (actual time=9.547..9.547 rows=0 loops=1)

  • Sort Key: networks.product_id, networks.provider_id, networks.provider_location_id
  • Sort Method: quicksort Memory: 25kB
61. 0.013 9.540 ↓ 0.0 0 1

Hash Join (cost=2,178.32..4,827.74 rows=2,229 width=20) (actual time=9.540..9.540 rows=0 loops=1)

  • Hash Cond: (cnn.network_id = networks.product_id)
  • Join Filter: (((cn.include_only_flags = '{}'::text[]) OR ((cn.include_only_flags <> '{}'::text[]) AND (networks.flags && cn.include_only_flags))) AND ((networks.service_areas = '{NULL}'::text[]) OR (cnn.service_area IS NULL) OR (cnn.service_area = ANY (networks.service_areas))))
62. 0.014 0.522 ↑ 19,144.0 1 1

Hash Join (cost=38.53..1,400.51 rows=19,144 width=156) (actual time=0.522..0.522 rows=1 loops=1)

  • Hash Cond: ((cnn.client_network_id)::text = (cn.client_network_id)::text)
63. 0.009 0.009 ↑ 29,001.5 2 1

Seq Scan on client_networks_networks cnn (cost=0.00..953.03 rows=58,003 width=15) (actual time=0.009..0.009 rows=2 loops=1)

64. 0.118 0.499 ↓ 1.2 311 1

Hash (cost=35.18..35.18 rows=268 width=145) (actual time=0.499..0.499 rows=311 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 124kB
65. 0.381 0.381 ↓ 1.2 311 1

Seq Scan on client_networks cn (cost=0.00..35.18 rows=268 width=145) (actual time=0.013..0.381 rows=311 loops=1)

  • Filter: ((NOT suppress_flags) AND ((include_only_flags = '{}'::text[]) OR (include_only_flags <> '{}'::text[])))
  • Rows Removed by Filter: 501
66. 0.001 9.005 ↓ 0.0 0 1

Hash (cost=2,135.67..2,135.67 rows=329 width=76) (actual time=9.005..9.005 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
67. 9.004 9.004 ↓ 0.0 0 1

CTE Scan on networks (cost=0.00..2,135.67 rows=329 width=76) (actual time=9.004..9.004 rows=0 loops=1)

  • Filter: ('ZELIS_PREM_SORT'::text = ANY (flags))
  • Rows Removed by Filter: 62505
68. 37.034 19,198.479 ↓ 62,505.0 62,505 1

Nested Loop (cost=203,319.11..480,655.36 rows=1 width=570) (actual time=15,677.941..19,198.479 rows=62,505 loops=1)

69. 33.161 18,973.930 ↓ 62,505.0 62,505 1

Nested Loop (cost=203,318.68..480,654.89 rows=1 width=528) (actual time=15,677.917..18,973.930 rows=62,505 loops=1)

70. 2,176.537 18,753.254 ↓ 62,505.0 62,505 1

Hash Right Join (cost=203,318.25..480,654.44 rows=1 width=484) (actual time=15,677.893..18,753.254 rows=62,505 loops=1)

  • Hash Cond: ((pl.strenuus_id = p.provider_id) AND (pl.strenuus_location_id = p.provider_location_id))
71. 1,515.540 1,515.540 ↑ 1.0 11,403,214 1

Seq Scan on tmp_location_core_tags pl (cost=0.00..191,803.10 rows=11,404,410 width=28) (actual time=0.017..1,515.540 rows=11,403,214 loops=1)

72. 43.476 15,061.177 ↓ 62,505.0 62,505 1

Hash (cost=203,318.24..203,318.24 rows=1 width=464) (actual time=15,061.177..15,061.177 rows=62,505 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 22249kB
73. 125.171 15,017.701 ↓ 62,505.0 62,505 1

Nested Loop (cost=138,712.11..203,318.24 rows=1 width=464) (actual time=10,217.719..15,017.701 rows=62,505 loops=1)

  • Join Filter: ((p.provider_id = oc.strenuus_id) AND (p.provider_location_id = oc.strenuus_location_id))
  • Rows Removed by Join Filter: 593277
74. 50.986 12,392.330 ↑ 1.1 62,505 1

Hash Join (cost=138,711.67..140,198.43 rows=65,713 width=460) (actual time=10,217.460..12,392.330 rows=62,505 loops=1)

  • Hash Cond: (p.provider_id = pr.strenuus_id)
75. 10,557.596 10,557.596 ↑ 1.1 62,505 1

CTE Scan on networks p (cost=0.00..1,314.26 rows=65,713 width=445) (actual time=8,424.881..10,557.596 rows=62,505 loops=1)

76. 947.577 1,783.748 ↓ 1.0 4,004,631 1

Hash (cost=88,683.52..88,683.52 rows=4,002,252 width=15) (actual time=1,783.748..1,783.748 rows=4,004,631 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 202342kB
77. 836.171 836.171 ↓ 1.0 4,004,631 1

Seq Scan on tmp_providers pr (cost=0.00..88,683.52 rows=4,002,252 width=15) (actual time=0.016..836.171 rows=4,004,631 loops=1)

78. 2,500.200 2,500.200 ↑ 1.2 10 62,505

Index Scan using output_core_strenuus_id_idx on output_core oc (cost=0.43..0.78 rows=12 width=16) (actual time=0.010..0.040 rows=10 loops=62,505)

  • Index Cond: (strenuus_id = pr.strenuus_id)
79. 187.515 187.515 ↑ 1.0 1 62,505

Index Scan using tmp_output_address_pkey on tmp_output_address a (cost=0.43..0.45 rows=1 width=52) (actual time=0.003..0.003 rows=1 loops=62,505)

  • Index Cond: (address_id = oc.address_id)
80. 187.515 187.515 ↑ 1.0 1 62,505

Index Scan using tmp_output_name_pkey on tmp_output_name n (cost=0.43..0.46 rows=1 width=50) (actual time=0.003..0.003 rows=1 loops=62,505)

  • Index Cond: (name_id = oc.name_id)
81. 0.000 0.000 ↓ 0.0 0 62,505

CTE Scan on networks_with_sort nws (cost=0.00..6.44 rows=322 width=76) (actual time=0.000..0.000 rows=0 loops=62,505)

Planning time : 15.183 ms
Execution time : 19,253.309 ms