explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sZqV

Settings
# exclusive inclusive rows x rows loops node
1. 270.489 6,446.624 ↑ 1.2 5,218 1

Nested Loop (cost=119,351.16..170,164.69 rows=6,066 width=298) (actual time=5,381.317..6,446.624 rows=5,218 loops=1)

  • Buffers: shared hit=870259
2. 13.111 6,160.481 ↑ 1.2 5,218 1

Hash Join (cost=119,350.62..166,199.43 rows=6,066 width=236) (actual time=5,379.435..6,160.481 rows=5,218 loops=1)

  • Buffers: shared hit=822403
3. 574.290 786.218 ↑ 1.2 10,304 1

Nested Loop (cost=10.51..46,765.5 rows=12,131 width=236) (actual time=12.924..786.218 rows=10,304 loops=1)

  • Buffers: shared hit=149412
4. 99.125 170.712 ↑ 1.2 10,304 1

Nested Loop (cost=9.97..38,835.64 rows=12,131 width=174) (actual time=0.984..170.712 rows=10,304 loops=1)

  • Buffers: shared hit=55937
5. 5.272 40.675 ↑ 1.2 10,304 1

Hash Join (cost=6.98..1,470.27 rows=12,131 width=152) (actual time=0.533..40.675 rows=10,304 loops=1)

  • Buffers: shared hit=658
6. 7.160 35.355 ↑ 1.2 10,304 1

Hash Join (cost=5.84..1,400.84 rows=12,131 width=128) (actual time=0.448..35.355 rows=10,304 loops=1)

  • Buffers: shared hit=657
7. 6.684 28.070 ↑ 1.2 10,332 1

Hash Join (cost=3.26..1,229.6 rows=12,625 width=116) (actual time=0.289..28.07 rows=10,332 loops=1)

  • Buffers: shared hit=656
8. 15.871 21.331 ↑ 1.3 10,421 1

Hash Join (cost=1.69..1,051.06 rows=13,527 width=92) (actual time=0.155..21.331 rows=10,421 loops=1)

  • Buffers: shared hit=655
9. 5.378 5.378 ↑ 1.0 18,916 1

Seq Scan on organization organization (cost=0..843.16 rows=18,916 width=76) (actual time=0.017..5.378 rows=18,916 loops=1)

  • Buffers: shared hit=654
10. 0.007 0.082 ↓ 1.1 19 1

Hash (cost=1.47..1.47 rows=18 width=40) (actual time=0.082..0.082 rows=19 loops=1)

  • Buffers: shared hit=1
11. 0.075 0.075 ↓ 1.1 19 1

Seq Scan on category category (cost=0..1.47 rows=18 width=40) (actual time=0.053..0.075 rows=19 loops=1)

  • Filter: (category._rowid <> ALL ('{2000,12000,9000,14000,2000,23000,68000}'::bigint[]))
  • Buffers: shared hit=1
12. 0.014 0.055 ↑ 1.0 22 1

Hash (cost=1.29..1.29 rows=22 width=40) (actual time=0.055..0.055 rows=22 loops=1)

  • Buffers: shared hit=1
13. 0.041 0.041 ↑ 1.0 22 1

Seq Scan on industrytype industrytype (cost=0..1.29 rows=22 width=40) (actual time=0.031..0.041 rows=22 loops=1)

  • Filter: (industrytype._rowid <> 21000)
  • Buffers: shared hit=1
14. 0.053 0.125 ↑ 1.0 63 1

Hash (cost=1.8..1.8 rows=63 width=28) (actual time=0.125..0.125 rows=63 loops=1)

  • Buffers: shared hit=1
15. 0.072 0.072 ↑ 1.0 63 1

Seq Scan on regiontype regiontype (cost=0..1.8 rows=63 width=28) (actual time=0.035..0.072 rows=63 loops=1)

  • Filter: (regiontype._rowid <> 42000)
  • Buffers: shared hit=1
16. 0.005 0.048 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=40) (actual time=0.048..0.048 rows=6 loops=1)

  • Buffers: shared hit=1
17. 0.043 0.043 ↑ 1.0 6 1

Seq Scan on organizationtype organizationtype (cost=0..1.06 rows=6 width=40) (actual time=0.04..0.043 rows=6 loops=1)

  • Buffers: shared hit=1
18. 0.000 30.912 ↑ 1.0 1 10,304

Index Scan using electronicaddress_rowid_indx on electronicaddress eletronicaddressalias1 (cost=2.99..3.07 rows=1 width=30) (actual time=0.003..0.003 rows=1 loops=10,304)

  • Index Cond: (eletronicaddressalias1._rowid = (SubPlan 1))
  • Buffers: shared hit=25869
19.          

SubPlan (forIndex Scan)

20. 10.304 82.432 ↑ 1.0 1 10,304

Limit (cost=2.7..2.7 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=10,304)

  • Buffers: shared hit=29410
21. 30.912 72.128 ↑ 2.0 1 10,304

Sort (cost=2.7..2.7 rows=2 width=12) (actual time=0.007..0.007 rows=1 loops=10,304)

  • Sort Key: ie1.ordering
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=29410
22. 41.216 41.216 ↑ 2.0 1 10,304

Index Scan using electronicaddress_organizationid_indx on electronicaddress ie1 (cost=0.29..2.69 rows=2 width=12) (actual time=0.004..0.004 rows=1 loops=10,304)

  • Index Cond: (ie1.organizationid = organization._rowid)
  • Filter: (ie1.deletionuserid IS NULL)
  • Buffers: shared hit=29407
23. 10.304 82.432 ↑ 1.0 1 10,304

Limit (cost=2.7..2.7 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=10,304)

  • Buffers: shared hit=29410
24. 30.912 72.128 ↑ 2.0 1 10,304

Sort (cost=2.7..2.7 rows=2 width=12) (actual time=0.007..0.007 rows=1 loops=10,304)

  • Sort Key: ie1.ordering
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=29410
25. 41.216 41.216 ↑ 2.0 1 10,304

Index Scan using electronicaddress_organizationid_indx on electronicaddress ie1 (cost=0.29..2.69 rows=2 width=12) (actual time=0.004..0.004 rows=1 loops=10,304)

  • Index Cond: (ie1.organizationid = organization._rowid)
  • Filter: (ie1.deletionuserid IS NULL)
  • Buffers: shared hit=29407
26. 41.216 41.216 ↑ 1.0 1 10,304

Index Scan using geoaddr_rowid_indx on geoaddress billto (cost=0.54..0.64 rows=1 width=62) (actual time=0.003..0.004 rows=1 loops=10,304)

  • Index Cond: (billto._rowid = getorgaddr(organization._rowid, 'billto'::text))
  • Buffers: shared hit=30809
27. 2.095 5,361.152 ↓ 1.9 5,380 1

Hash (cost=119,305..119,305 rows=2,808 width=8) (actual time=5,361.152..5,361.152 rows=5,380 loops=1)

  • Buffers: shared hit=672991
28. 4.930 5,359.057 ↓ 1.9 5,380 1

Aggregate (cost=119,248.84..119,276.92 rows=2,808 width=9) (actual time=5,357.647..5,359.057 rows=5,380 loops=1)

  • Buffers: shared hit=672991
29. 1.250 5,354.127 ↓ 2.5 6,981 1

Append (cost=118,515.52..119,241.82 rows=2,808 width=9) (actual time=5,342.432..5,354.127 rows=6,981 loops=1)

  • Buffers: shared hit=672991
30. 19.838 5,349.701 ↓ 21.9 4,373 1

Aggregate (cost=118,515.52..118,519.52 rows=200 width=16) (actual time=5,342.431..5,349.701 rows=4,373 loops=1)

  • Filter: ((max("*SELECT* 1".maxdate) >= '2017-01-01 00:00:00-07'::timestamp with time zone) AND (max("*SELECT* 1".maxdate) <= ('now'::cstring)::date))
  • Buffers: shared hit=672106
31. 2.838 5,329.863 ↑ 2.9 22,912 1

Append (cost=3,132.85..118,018.84 rows=66,224 width=16) (actual time=302.11..5,329.863 rows=22,912 loops=1)

  • Buffers: shared hit=672106
32. 0.212 302.783 ↑ 7.2 1,319 1

Subquery Scan on *SELECT* 1 (cost=3,132.85..3,322.37 rows=9,476 width=16) (actual time=302.109..302.783 rows=1,319 loops=1)

  • Buffers: shared hit=56965
33. 86.921 302.571 ↑ 7.2 1,319 1

Aggregate (cost=3,132.85..3,227.61 rows=9,476 width=37) (actual time=302.108..302.571 rows=1,319 loops=1)

  • Buffers: shared hit=56965
34. 57.672 215.650 ↓ 10.6 100,269 1

Hash Join (cost=468.36..3,038.09 rows=9,476 width=37) (actual time=12.259..215.65 rows=100,269 loops=1)

  • Buffers: shared hit=56965
35. 15.915 145.804 ↓ 6.6 46,815 1

Nested Loop (cost=0.91..2,422.53 rows=7,114 width=37) (actual time=0.072..145.804 rows=46,815 loops=1)

  • Buffers: shared hit=56750
36. 16.147 43.324 ↑ 1.0 2,985 1

Merge Join (cost=0.62..1,193.84 rows=2,985 width=37) (actual time=0.054..43.324 rows=2,985 loops=1)

  • Buffers: shared hit=13058
37. 5.249 5.249 ↑ 1.0 2,985 1

Index Scan using organizationtimeblockjoin_organizationid_index on organizationtimeblockjoin organizationtimeblockjoin (cost=0.28..79.06 rows=2,985 width=16) (actual time=0.015..5.249 rows=2,985 loops=1)

  • Buffers: shared hit=2202
38. 21.928 21.928 ↓ 1.1 20,422 1

Index Scan using organzation_rowid_indx on organization organization_1 (cost=0.29..1,039.32 rows=18,916 width=29) (actual time=0.017..21.928 rows=20,422 loops=1)

  • Index Cond: (organization_1._rowid IS NOT NULL)
  • Buffers: shared hit=10856
39. 86.565 86.565 ↓ 5.3 16 2,985

Index Scan using role_organizationid_index on role role (cost=0.29..0.38 rows=3 width=16) (actual time=0.006..0.029 rows=16 loops=2,985)

  • Index Cond: (role.organizationid = organization_1._rowid)
  • Buffers: shared hit=43692
40. 2.305 12.174 ↓ 1.0 4,747 1

Hash (cost=410.73..410.73 rows=4,537 width=16) (actual time=12.174..12.174 rows=4,747 loops=1)

  • Buffers: shared hit=215
41. 3.851 9.869 ↓ 1.0 4,747 1

Hash Join (cost=257.63..410.73 rows=4,537 width=16) (actual time=4.87..9.869 rows=4,747 loops=1)

  • Buffers: shared hit=215
42. 1.173 1.173 ↑ 1.0 4,537 1

Seq Scan on contacttimeblockjoin contacttimeblockjoin (cost=0..79.37 rows=4,537 width=8) (actual time=0.012..1.173 rows=4,537 loops=1)

  • Buffers: shared hit=34
43. 1.240 4.845 ↑ 1.0 3,406 1

Hash (cost=215.06..215.06 rows=3,406 width=16) (actual time=4.845..4.845 rows=3,406 loops=1)

  • Buffers: shared hit=181
44. 3.605 3.605 ↑ 1.0 3,406 1

Seq Scan on timeblock timeblock (cost=0..215.06 rows=3,406 width=16) (actual time=0.011..3.605 rows=3,406 loops=1)

  • Buffers: shared hit=181
45. 1.257 905.916 ↑ 2.5 7,709 1

Subquery Scan on *SELECT* 2 (cost=24,698.97..25,077.29 rows=18,916 width=16) (actual time=901.762..905.916 rows=7,709 loops=1)

  • Buffers: shared hit=106345
46. 261.728 904.659 ↑ 2.5 7,709 1

Aggregate (cost=24,698.97..24,888.13 rows=18,916 width=37) (actual time=901.76..904.659 rows=7,709 loops=1)

  • Buffers: shared hit=106345
47. 145.030 642.931 ↓ 4.7 371,501 1

Hash Join (cost=19,591.81..23,914.44 rows=78,453 width=37) (actual time=377.338..642.931 rows=371,501 loops=1)

  • Buffers: shared hit=106345
48. 100.081 374.961 ↓ 1.0 33,470 1

Hash Join (cost=15,963.18..19,172.09 rows=32,919 width=16) (actual time=254.342..374.961 rows=33,470 loops=1)

  • Buffers: shared hit=59716
49. 23.873 23.873 ↑ 1.0 127,938 1

Seq Scan on contacttaskjoin contacttaskjoin (cost=0..2,256.38 rows=127,938 width=8) (actual time=0.008..23.873 rows=127,938 loops=1)

  • Buffers: shared hit=977
50. 15.254 251.007 ↑ 1.0 32,917 1

Hash (cost=15,551.69..15,551.69 rows=32,919 width=24) (actual time=251.007..251.007 rows=32,917 loops=1)

  • Buffers: shared hit=58739
51. 86.764 235.753 ↑ 1.0 32,917 1

Merge Join (cost=559.05..15,551.69 rows=32,919 width=24) (actual time=5.958..235.753 rows=32,917 loops=1)

  • Buffers: shared hit=58739
52. 134.292 134.292 ↑ 1.0 134,623 1

Index Scan using task_pkey on task task (cost=0.42..13,964.76 rows=134,623 width=16) (actual time=0.016..134.292 rows=134,623 loops=1)

  • Buffers: shared hit=50912
53. 14.697 14.697 ↑ 1.0 32,919 1

Index Scan using organizationtaskjoin_taskid_index on organizationtaskjoin organizationtaskjoin (cost=0.29..839.08 rows=32,919 width=16) (actual time=0.082..14.697 rows=32,919 loops=1)

  • Buffers: shared hit=7827
54. 27.220 122.940 ↓ 1.1 47,437 1

Hash (cost=3,065.12..3,065.12 rows=45,081 width=29) (actual time=122.94..122.94 rows=47,437 loops=1)

  • Buffers: shared hit=46629
55. 38.872 95.720 ↓ 1.1 47,437 1

Merge Join (cost=0.58..3,065.12 rows=45,081 width=29) (actual time=0.026..95.72 rows=47,437 loops=1)

  • Buffers: shared hit=46629
56. 14.972 14.972 ↑ 1.0 18,916 1

Index Scan using organzation_rowid_indx on organization organization_2 (cost=0.29..1,039.32 rows=18,916 width=29) (actual time=0.014..14.972 rows=18,916 loops=1)

  • Index Cond: (organization_2._rowid IS NOT NULL)
  • Buffers: shared hit=10930
57. 41.876 41.876 ↑ 1.0 45,091 1

Index Scan using role_organizationid_index on role role_1 (cost=0.29..1,417.88 rows=45,173 width=16) (actual time=0.008..41.876 rows=45,091 loops=1)

  • Buffers: shared hit=35699
58. 1.961 3,300.657 ↑ 1.9 9,777 1

Subquery Scan on *SELECT* 3 (cost=53,802.75..54,181.07 rows=18,916 width=16) (actual time=3,293.871..3,300.657 rows=9,777 loops=1)

  • Buffers: shared hit=357918
59. 188.801 3,298.696 ↑ 1.9 9,777 1

Aggregate (cost=53,802.75..53,991.91 rows=18,916 width=37) (actual time=3,293.87..3,298.696 rows=9,777 loops=1)

  • Buffers: shared hit=357918
60. 242.276 3,109.895 ↓ 1.0 169,075 1

Hash Join (cost=1,081.31..52,142.9 rows=165,985 width=37) (actual time=16.683..3,109.895 rows=169,075 loops=1)

  • Buffers: shared hit=357918
61. 210.799 2,851.071 ↓ 1.0 169,088 1

Merge Join (cost=1.7..47,121.14 rows=165,985 width=16) (actual time=0.095..2,851.071 rows=169,088 loops=1)

  • Buffers: shared hit=357264
62. 2,411.117 2,411.117 ↑ 1.0 160,950 1

Index Scan using email_id on email email (cost=0.42..34,300.01 rows=162,138 width=16) (actual time=0.078..2,411.117 rows=160,950 loops=1)

  • Filter: ((email.fromstring)::text !~ '@snaptron.com'::text)
  • Buffers: shared hit=188661
63. 229.155 229.155 ↑ 1.0 389,518 1

Index Scan using emailorganizationjoin_emailid_index on emailorganizationjoin emailorganizationjoin (cost=0.42..9,783.21 rows=389,519 width=16) (actual time=0.011..229.155 rows=389,518 loops=1)

  • Buffers: shared hit=168603
64. 7.172 16.548 ↑ 1.0 18,916 1

Hash (cost=843.16..843.16 rows=18,916 width=29) (actual time=16.548..16.548 rows=18,916 loops=1)

  • Buffers: shared hit=654
65. 9.376 9.376 ↑ 1.0 18,916 1

Seq Scan on organization organization_3 (cost=0..843.16 rows=18,916 width=29) (actual time=0.005..9.376 rows=18,916 loops=1)

  • Buffers: shared hit=654
66. 0.586 817.669 ↑ 4.6 4,107 1

Subquery Scan on *SELECT* 4 (cost=35,059.79..35,438.11 rows=18,916 width=16) (actual time=815.85..817.669 rows=4,107 loops=1)

  • Buffers: shared hit=150878
67. 257.344 817.083 ↑ 4.6 4,107 1

Aggregate (cost=35,059.79..35,248.95 rows=18,916 width=37) (actual time=815.85..817.083 rows=4,107 loops=1)

  • Buffers: shared hit=150878
68. 123.863 559.739 ↓ 7.1 382,808 1

Hash Join (cost=4.91..34,522.51 rows=53,728 width=37) (actual time=0.204..559.739 rows=382,808 loops=1)

  • Buffers: shared hit=150878
69. 152.180 435.800 ↓ 7.1 382,808 1

Merge Join (cost=1.35..34,317.38 rows=53,728 width=45) (actual time=0.11..435.8 rows=382,808 loops=1)

  • Buffers: shared hit=150877
70. 16.425 204.797 ↑ 1.0 22,495 1

Nested Loop (cost=1.06..32,040.38 rows=22,544 width=45) (actual time=0.075..204.797 rows=22,495 loops=1)

  • Buffers: shared hit=115178
71. 18.778 53.186 ↑ 1.0 22,531 1

Merge Join (cost=0.63..1,938.86 rows=22,544 width=37) (actual time=0.046..53.186 rows=22,531 loops=1)

  • Buffers: shared hit=24968
72. 12.727 12.727 ↑ 1.0 18,916 1

Index Scan using organzation_rowid_indx on organization organization_4 (cost=0.29..1,039.32 rows=18,916 width=29) (actual time=0.025..12.727 rows=18,916 loops=1)

  • Index Cond: (organization_4._rowid IS NOT NULL)
  • Buffers: shared hit=10930
73. 21.681 21.681 ↑ 1.0 22,544 1

Index Scan using fileattachmentorganizationjoin_organizationid_index on fileattachmentorganizationjoin fileattachmentorganizationjoin (cost=0.29..570.45 rows=22,544 width=16) (actual time=0.011..21.681 rows=22,544 loops=1)

  • Buffers: shared hit=14038
74. 135.186 135.186 ↑ 1.0 1 22,531

Index Scan using fileattachment_pkey on fileattachment fileattachment (cost=0.42..1.33 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=22,531)

  • Index Cond: (fileattachment._rowid = fileattachmentorganizationjoin.fileattachmentid)
  • Buffers: shared hit=90210
75. 40.471 78.823 ↓ 9.0 405,015 1

Materialize (cost=0.29..1,530.82 rows=45,173 width=16) (actual time=0.022..78.823 rows=405,015 loops=1)

  • Buffers: shared hit=35699
76. 38.352 38.352 ↑ 1.0 45,091 1

Index Scan using role_organizationid_index on role role_2 (cost=0.29..1,417.88 rows=45,173 width=16) (actual time=0.011..38.352 rows=45,091 loops=1)

  • Buffers: shared hit=35699
77. 0.048 0.076 ↑ 1.0 114 1

Hash (cost=2.14..2.14 rows=114 width=8) (actual time=0.076..0.076 rows=114 loops=1)

  • Buffers: shared hit=1
78. 0.028 0.028 ↑ 1.0 114 1

Seq Scan on fileattachmentcontactjoin fileattachmentcontactjoin (cost=0..2.14 rows=114 width=8) (actual time=0.011..0.028 rows=114 loops=1)

  • Buffers: shared hit=1
79. 3.176 3.176 ↑ 1.0 2,608 1

Index Scan using organization_categoryid_index on organization organization_5 (cost=0.29..694.22 rows=2,608 width=8) (actual time=0.051..3.176 rows=2,608 loops=1)

  • Index Cond: (organization_5.categoryid = ANY ('{3000,11000,5000,69000}'::bigint[]))
  • Buffers: shared hit=885
80. 15.654 15.654 ↑ 1.0 1 5,218

Index Scan using geoaddr_rowid_indx on geoaddress shipto (cost=0.54..0.64 rows=1 width=62) (actual time=0.003..0.003 rows=1 loops=5,218)

  • Index Cond: (shipto._rowid = getorgaddr(organization._rowid, 'shipto'::text))
  • Buffers: shared hit=15656