explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QelG

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 95,589.116 ↑ 1.0 25 1

Limit (cost=1,095,667.52..1,095,667.58 rows=25 width=710) (actual time=95,589.108..95,589.116 rows=25 loops=1)

  • Buffers: shared hit=4087996 read=70154
2.          

CTE contact_accounts_with_recipient_data

3. 178.518 4,481.046 ↓ 82.2 84,171 1

HashAggregate (cost=2,734.42..2,749.78 rows=1,024 width=16) (actual time=4,442.056..4,481.046 rows=84,171 loops=1)

  • Group Key: ca.id, cr.id, pri.isdeleted
  • Buffers: shared hit=631271 read=2726
4. 25.222 4,302.528 ↓ 212.8 217,858 1

Nested Loop Left Join (cost=616.29..2,716.50 rows=1,024 width=17) (actual time=10.002..4,302.528 rows=217,858 loops=1)

  • Buffers: shared hit=631271 read=2726
5. 7.852 321.269 ↓ 228.1 84,171 1

Nested Loop Left Join (cost=615.87..2,460.17 rows=369 width=13) (actual time=9.994..321.269 rows=84,171 loops=1)

  • Buffers: shared hit=223344 read=84
6. 43.618 60.904 ↓ 228.1 84,171 1

Hash Right Join (cost=615.58..2,205.60 rows=369 width=16) (actual time=8.675..60.904 rows=84,171 loops=1)

  • Hash Cond: (cr.contactaccountid = ca.id)
  • Buffers: shared hit=1311
7. 8.743 8.743 ↑ 1.0 73,769 1

Seq Scan on publishroomitemcontactrecipients cr (cost=0.00..1,309.69 rows=73,769 width=12) (actual time=0.115..8.743 rows=73,769 loops=1)

  • Buffers: shared hit=1084
8. 4.204 8.543 ↓ 207.0 26,706 1

Hash (cost=613.97..613.97 rows=129 width=8) (actual time=8.543..8.543 rows=26,706 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1300kB
  • Buffers: shared hit=227
9. 4.339 4.339 ↓ 207.0 26,706 1

Seq Scan on contactaccounts ca (cost=0.00..613.97 rows=129 width=8) (actual time=0.009..4.339 rows=26,706 loops=1)

  • Filter: (accountid = (@ accountid))
  • Buffers: shared hit=227
10. 252.513 252.513 ↑ 1.0 1 84,171

Index Scan using publishroomitems_pkey on publishroomitems pri (cost=0.29..0.68 rows=1 width=5) (actual time=0.002..0.003 rows=1 loops=84,171)

  • Index Cond: (id = cr.publishroomitemid)
  • Buffers: shared hit=222033 read=84
11. 3,956.037 3,956.037 ↑ 2.0 2 84,171

Index Scan using ix_emaillogs_publishroomitemcontactrecipients_id on emaillogs el (cost=0.42..0.65 rows=4 width=8) (actual time=0.027..0.047 rows=2 loops=84,171)

  • Index Cond: (publishroomitemcontactrecipientid = cr.id)
  • Buffers: shared hit=407927 read=2642
12.          

CTE contact_accounts_with_recipient_data_counts

13. 46.220 4,558.594 ↓ 133.5 26,706 1

HashAggregate (cost=30.72..32.72 rows=200 width=24) (actual time=4,550.471..4,558.594 rows=26,706 loops=1)

  • Group Key: cawrd.contactaccountid, cawrd.contactid
  • Buffers: shared hit=631271 read=2726
14. 4,512.374 4,512.374 ↓ 82.2 84,171 1

CTE Scan on contact_accounts_with_recipient_data cawrd (cost=0.00..20.48 rows=1,024 width=11) (actual time=4,442.059..4,512.374 rows=84,171 loops=1)

  • Buffers: shared hit=631271 read=2726
15.          

CTE contact_accounts_with_metadata

16. 149.844 4,755.620 ↓ 133.5 26,706 1

HashAggregate (cost=951.91..954.41 rows=200 width=72) (actual time=4,723.879..4,755.620 rows=26,706 loops=1)

  • Group Key: cawrdc.contactaccountid, cawrdc.contactid, cawrdc.numberofdelivered, cawrdc.numberofopened
  • Buffers: shared hit=631464 read=2726
17. 12.061 4,605.776 ↓ 125.9 35,748 1

Hash Right Join (cost=888.42..947.65 rows=284 width=42) (actual time=4,593.335..4,605.776 rows=35,748 loops=1)

  • Hash Cond: (ct.id = cat.contacttagid)
  • Buffers: shared hit=631464 read=2726
18. 0.405 0.405 ↓ 1.0 1,553 1

Seq Scan on contacttags ct (cost=0.00..31.68 rows=1,543 width=18) (actual time=0.011..0.405 rows=1,553 loops=1)

  • Filter: (NOT isdeleted)
  • Rows Removed by Filter: 226
  • Buffers: shared hit=14
19. 7.403 4,593.310 ↓ 125.9 35,748 1

Hash (cost=884.87..884.87 rows=284 width=28) (actual time=4,593.310..4,593.310 rows=35,748 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2593kB
  • Buffers: shared hit=631450 read=2726
20. 11.389 4,585.907 ↓ 125.9 35,748 1

Hash Left Join (cost=873.78..884.87 rows=284 width=28) (actual time=4,558.836..4,585.907 rows=35,748 loops=1)

  • Hash Cond: (cawrdc.contactaccountid = cat.contactaccountid)
  • Buffers: shared hit=631450 read=2726
21. 4,566.201 4,566.201 ↓ 133.5 26,706 1

CTE Scan on contact_accounts_with_recipient_data_counts cawrdc (cost=0.00..4.00 rows=200 width=24) (actual time=4,550.472..4,566.201 rows=26,706 loops=1)

  • Buffers: shared hit=631271 read=2726
22. 4.517 8.317 ↓ 1.0 32,047 1

Hash (cost=487.79..487.79 rows=30,879 width=8) (actual time=8.317..8.317 rows=32,047 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1508kB
  • Buffers: shared hit=179
23. 3.800 3.800 ↓ 1.0 32,047 1

Seq Scan on contactaccounttags cat (cost=0.00..487.79 rows=30,879 width=8) (actual time=0.011..3.800 rows=32,047 loops=1)

  • Buffers: shared hit=179
24. 0.464 95,589.112 ↑ 3.4 25 1

Sort (cost=1,091,930.60..1,091,930.82 rows=85 width=710) (actual time=95,589.107..95,589.112 rows=25 loops=1)

  • Sort Key: c.firstname, c.lastname, c.email
  • Sort Method: top-N heapsort Memory: 85kB
  • Buffers: shared hit=4087996 read=70154
25. 1.048 95,588.648 ↓ 1.9 163 1

WindowAgg (cost=1,091,914.18..1,091,928.20 rows=85 width=710) (actual time=95,588.260..95,588.648 rows=163 loops=1)

  • Buffers: shared hit=4087993 read=70154
26. 92.304 95,587.600 ↓ 1.9 163 1

GroupAggregate (cost=1,091,914.18..1,091,925.02 rows=85 width=654) (actual time=95,493.162..95,587.600 rows=163 loops=1)

  • Group Key: cawm.contactaccountid, cawm.contacttagsjson, cawm.numberofdelivered, cawm.numberofopened, c.id, (COALESCE(c.picture, s.sourcelogo)), j.id, s.originalurl, s.name, s.domainreach, s.country, iu.userid, tu.userid
  • Buffers: shared hit=4087993 read=70154
27. 92.746 95,495.296 ↓ 195.8 16,639 1

Sort (cost=1,091,914.18..1,091,914.39 rows=85 width=523) (actual time=95,492.203..95,495.296 rows=16,639 loops=1)

  • Sort Key: cawm.contactaccountid, cawm.contacttagsjson, cawm.numberofdelivered, cawm.numberofopened, c.id, (COALESCE(c.picture, s.sourcelogo)), j.id, s.originalurl, s.name, s.domainreach, s.country, iu.userid, tu.userid
  • Sort Method: quicksort Memory: 9173kB
  • Buffers: shared hit=4087993 read=70154
28. 635.873 95,402.550 ↓ 195.8 16,639 1

Nested Loop Left Join (cost=205,556.95..1,091,911.46 rows=85 width=523) (actual time=4,823.287..95,402.550 rows=16,639 loops=1)

  • Join Filter: (prff.contactaccountid = cawm.contactaccountid)
  • Rows Removed by Join Filter: 7005019
  • Buffers: shared hit=4087982 read=70154
29. 1,880.511 94,417.258 ↓ 198.1 16,639 1

Nested Loop Left Join (cost=205,556.95..1,091,428.72 rows=84 width=692) (actual time=4,823.147..94,417.258 rows=16,639 loops=1)

  • Join Filter: (pr.id = prf.publishroomid)
  • Rows Removed by Join Filter: 22678957
  • Buffers: shared hit=4087979 read=70154
30. 701.941 91,455.212 ↓ 198.1 16,639 1

Nested Loop Left Join (cost=205,556.95..1,089,690.49 rows=84 width=679) (actual time=4,822.621..91,455.212 rows=16,639 loops=1)

  • Join Filter: (prf.contactaccountid = cawm.contactaccountid)
  • Rows Removed by Join Filter: 7005019
  • Buffers: shared hit=4087965 read=70154
31. 38.502 90,420.491 ↓ 200.5 16,639 1

Nested Loop Left Join (cost=205,556.95..1,089,213.41 rows=83 width=667) (actual time=4,822.453..90,420.491 rows=16,639 loops=1)

  • Buffers: shared hit=4087962 read=70154
32. 37.472 78,851.162 ↓ 200.5 16,639 1

Nested Loop Left Join (cost=205,556.51..1,088,649.60 rows=83 width=663) (actual time=4,821.076..78,851.162 rows=16,639 loops=1)

  • Buffers: shared hit=4046419 read=61155
33. 67.067 45,984.943 ↓ 200.5 16,639 1

Nested Loop Left Join (cost=205,555.95..1,088,074.46 rows=83 width=647) (actual time=4,817.901..45,984.943 rows=16,639 loops=1)

  • Buffers: shared hit=3991353 read=32943
34. 12.862 17,091.764 ↓ 3,217.2 16,086 1

Nested Loop Left Join (cost=2.57..56,966.09 rows=5 width=643) (actual time=4,817.869..17,091.764 rows=16,086 loops=1)

  • Buffers: shared hit=3927175 read=13072
35. 0.519 4,868.898 ↓ 163.0 163 1

Nested Loop Left Join (cost=2.14..39.87 rows=1 width=639) (actual time=4,724.296..4,868.898 rows=163 loops=1)

  • Buffers: shared hit=633441 read=2798
36. 0.469 4,867.075 ↓ 163.0 163 1

Nested Loop Left Join (cost=1.71..33.05 rows=1 width=478) (actual time=4,724.283..4,867.075 rows=163 loops=1)

  • Buffers: shared hit=632787 read=2798
37. 0.341 4,845.090 ↓ 163.0 163 1

Nested Loop Left Join (cost=1.28..26.28 rows=1 width=456) (actual time=4,724.268..4,845.090 rows=163 loops=1)

  • Buffers: shared hit=632153 read=2779
38. 0.396 4,831.220 ↓ 163.0 163 1

Nested Loop Left Join (cost=0.85..19.49 rows=1 width=428) (actual time=4,724.264..4,831.220 rows=163 loops=1)

  • Buffers: shared hit=632140 read=2768
39. 0.423 4,783.228 ↓ 163.0 163 1

Nested Loop (cost=0.42..12.70 rows=1 width=396) (actual time=4,724.252..4,783.228 rows=163 loops=1)

  • Buffers: shared hit=632117 read=2727
40. 4,780.034 4,780.034 ↓ 163.0 163 1

CTE Scan on contact_accounts_with_metadata cawm (cost=0.00..4.25 rows=1 width=56) (actual time=4,724.215..4,780.034 rows=163 loops=1)

  • Filter: (contacttagsjson @> ANY ('{"[{\"id\": 9605}]"}'::jsonb[]))
  • Rows Removed by Filter: 26543
  • Buffers: shared hit=631464 read=2726
41. 2.771 2.771 ↑ 1.0 1 163

Index Scan using contacts_pkey on contacts c (cost=0.42..8.44 rows=1 width=344) (actual time=0.016..0.017 rows=1 loops=163)

  • Index Cond: (id = cawm.contactid)
  • Buffers: shared hit=653 read=1
42. 47.596 47.596 ↓ 0.0 0 163

Index Scan using instagramusers_pkey on instagramusers iu (cost=0.43..6.78 rows=1 width=40) (actual time=0.292..0.292 rows=0 loops=163)

  • Index Cond: (userid = c.instagramuserid)
  • Buffers: shared hit=23 read=41
43. 13.529 13.529 ↓ 0.0 0 163

Index Scan using twitterusers_pkey on twitterusers tu (cost=0.43..6.78 rows=1 width=36) (actual time=0.083..0.083 rows=0 loops=163)

  • Index Cond: (userid = c.twitteruserid)
  • Buffers: shared hit=13 read=11
44. 21.516 21.516 ↑ 1.0 1 163

Index Scan using journalists_pkey on journalists j (cost=0.42..6.76 rows=1 width=26) (actual time=0.132..0.132 rows=1 loops=163)

  • Index Cond: (id = c.journalistid)
  • Buffers: shared hit=634 read=19
45. 1.304 1.304 ↑ 1.0 1 163

Index Scan using sourcestmp_pkey on sources s (cost=0.43..6.80 rows=1 width=169) (actual time=0.007..0.008 rows=1 loops=163)

  • Index Cond: (id = j.sourceid)
  • Buffers: shared hit=654
46. 12,210.004 12,210.004 ↓ 1.5 99 163

Index Only Scan using journalistitems_pkey on journalistitems ji (cost=0.43..56,925.57 rows=65 width=8) (actual time=8.707..74.908 rows=99 loops=163)

  • Index Cond: (journalistid = j.id)
  • Heap Fetches: 1699
  • Buffers: shared hit=3293734 read=10274
47. 16,086.000 28,826.112 ↑ 203.0 1 16,086

Bitmap Heap Scan on items i (cost=205,553.38..206,219.64 rows=203 width=16) (actual time=1.785..1.792 rows=1 loops=16,086)

  • Recheck Cond: ((id = ji.itemid) OR ((sourceid = iu.userid) AND (mediatype = 2)))
  • Heap Blocks: exact=16594
  • Buffers: shared hit=64178 read=19871
48. 32.172 12,740.112 ↓ 0.0 0 16,086

BitmapOr (cost=205,553.38..205,553.38 rows=203 width=0) (actual time=0.792..0.792 rows=0 loops=16,086)

  • Buffers: shared hit=58557 read=8898
49. 12,675.768 12,675.768 ↑ 1.0 1 16,086

Bitmap Index Scan on items_pkey (cost=0.00..3.81 rows=1 width=0) (actual time=0.788..0.788 rows=1 loops=16,086)

  • Index Cond: (id = ji.itemid)
  • Buffers: shared hit=55549 read=8883
50. 32.172 32.172 ↓ 0.0 0 16,086

Bitmap Index Scan on ix_items_sourceidmediatype (cost=0.00..9.15 rows=202 width=0) (actual time=0.002..0.002 rows=0 loops=16,086)

  • Index Cond: ((sourceid = iu.userid) AND (mediatype = 2))
  • Buffers: shared hit=3008 read=15
51. 32,828.747 32,828.747 ↑ 1.0 1 16,639

Index Scan using articlemetadatas_pkey on articlemetadatas am (cost=0.56..6.92 rows=1 width=20) (actual time=1.961..1.973 rows=1 loops=16,639)

  • Index Cond: (itemid = i.id)
  • Buffers: shared hit=55066 read=28212
52. 11,530.827 11,530.827 ↓ 0.0 0 16,639

Index Scan using instagrammetadatas_pkey on instagrammetadatas im (cost=0.44..6.78 rows=1 width=8) (actual time=0.693..0.693 rows=0 loops=16,639)

  • Index Cond: (itemid = i.id)
  • Buffers: shared hit=41543 read=8999
53. 332.729 332.780 ↓ 1.1 421 16,639

Materialize (cost=0.00..8.65 rows=377 width=16) (actual time=0.000..0.020 rows=421 loops=16,639)

  • Buffers: shared hit=3
54. 0.051 0.051 ↓ 1.1 421 1

Seq Scan on publishroomfollowers prf (cost=0.00..6.77 rows=377 width=16) (actual time=0.005..0.051 rows=421 loops=1)

  • Buffers: shared hit=3
55. 1,081.350 1,081.535 ↓ 1.0 1,363 16,639

Materialize (cost=0.00..34.33 rows=1,355 width=17) (actual time=0.000..0.065 rows=1,363 loops=16,639)

  • Buffers: shared hit=14
56. 0.185 0.185 ↓ 1.0 1,363 1

Seq Scan on publishrooms pr (cost=0.00..27.55 rows=1,355 width=17) (actual time=0.006..0.185 rows=1,363 loops=1)

  • Buffers: shared hit=14
57. 349.377 349.419 ↓ 1.1 421 16,639

Materialize (cost=0.00..8.65 rows=377 width=4) (actual time=0.000..0.021 rows=421 loops=16,639)

  • Buffers: shared hit=3
58. 0.042 0.042 ↓ 1.1 421 1

Seq Scan on publishroomfollowers prff (cost=0.00..6.77 rows=377 width=4) (actual time=0.002..0.042 rows=421 loops=1)

  • Buffers: shared hit=3