explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rqtO : qb slow

Settings
# exclusive inclusive rows x rows loops node
1. 1.285 408,714.776 ↓ 4.0 20 1

Result (cost=13,438.53..13,441.48 rows=5 width=757) (actual time=408,713.839..408,714.776 rows=20 loops=1)

2.          

CTE transactions_with_last_quickbooks_transactions

3. 4.030 59.022 ↓ 1.0 19,592 1

Unique (cost=11,756.15..11,901.71 rows=19,339 width=334) (actual time=48.798..59.022 rows=19,592 loops=1)

4. 25.096 54.992 ↑ 1.5 19,735 1

Sort (cost=11,756.15..11,828.93 rows=29,111 width=334) (actual time=48.797..54.992 rows=19,735 loops=1)

  • Sort Key: t.id DESC, qt.id DESC
  • Sort Method: external merge Disk: 6296kB
5. 12.475 29.896 ↑ 1.5 19,735 1

Hash Right Join (cost=2,786.13..5,117.68 rows=29,111 width=334) (actual time=15.620..29.896 rows=19,735 loops=1)

  • Hash Cond: (qt.transaction_id = t.id)
6. 1.829 1.829 ↑ 5.6 5,213 1

Seq Scan on quickbooks_transactions qt (cost=0.00..968.11 rows=29,111 width=41) (actual time=0.005..1.829 rows=5,213 loops=1)

7. 8.384 15.592 ↓ 1.0 19,592 1

Hash (cost=1,769.39..1,769.39 rows=19,339 width=297) (actual time=15.592..15.592 rows=19,592 loops=1)

  • Buckets: 16384 Batches: 2 Memory Usage: 3390kB
8. 7.208 7.208 ↓ 1.0 19,592 1

Seq Scan on transactions t (cost=0.00..1,769.39 rows=19,339 width=297) (actual time=0.009..7.208 rows=19,592 loops=1)

9. 0.056 408,713.491 ↓ 4.0 20 1

Sort (cost=1,536.82..1,536.83 rows=5 width=729) (actual time=408,713.489..408,713.491 rows=20 loops=1)

  • Sort Key: twlqt.id, twlqt.loan_id
  • Sort Method: quicksort Memory: 30kB
10. 0.639 408,713.435 ↓ 4.0 20 1

Nested Loop Left Join (cost=375.81..1,536.76 rows=5 width=729) (actual time=589.483..408,713.435 rows=20 loops=1)

  • Filter: (((twlqt.code)::text !~ '^RV'::text) OR (((twlqt.code)::text ~ '^RV'::text) AND ((pt.code)::text ~ '^JELD$|^JER$|^JEREF$|^JEREV$|^LD$|PRE|PM|PMM|PMI|PMP|LP|LPI|LPP|LPM|AT|ATI|ATP|ATM|RC|TAP|^TR$|WO|WOR'::text)))
11. 0.064 408,709.196 ↓ 20.0 20 1

Nested Loop Left Join (cost=375.56..1,523.60 rows=1 width=658) (actual time=589.259..408,709.196 rows=20 loops=1)

12. 1.704 408,709.092 ↓ 20.0 20 1

Nested Loop (cost=375.41..1,523.26 rows=1 width=598) (actual time=589.251..408,709.092 rows=20 loops=1)

  • Join Filter: (twlqt.date >= COALESCE(o.quickbooks_export_start_date, '-infinity'::date))
  • Rows Removed by Join Filter: 732
13. 72.523 408,699.868 ↓ 752.0 752 1

Nested Loop (cost=375.41..1,510.24 rows=1 width=581) (actual time=589.241..408,699.868 rows=752 loops=1)

  • Join Filter: (l.id = d.loan_id)
  • Rows Removed by Join Filter: 867808
14. 2.180 107.857 ↓ 752.0 752 1

Nested Loop (cost=0.42..492.61 rows=1 width=357) (actual time=48.857..107.857 rows=752 loops=1)

15. 1.703 104.173 ↓ 752.0 752 1

Nested Loop (cost=0.27..491.78 rows=1 width=321) (actual time=48.850..104.173 rows=752 loops=1)

16. 96.394 96.394 ↓ 1,519.0 1,519 1

CTE Scan on transactions_with_last_quickbooks_transactions twlqt (cost=0.00..483.48 rows=1 width=281) (actual time=48.827..96.394 rows=1,519 loops=1)

  • Filter: ((quickbooks_transaction_identifier IS NULL) AND (NOT COALESCE(never_send, false)) AND (date <= '2019-07-18'::date) AND ((code)::text ~ '^LD$|^JEREF$'::text))
  • Rows Removed by Filter: 18073
17. 6.076 6.076 ↓ 0.0 0 1,519

Index Scan using loans_pkey on loans l (cost=0.27..8.29 rows=1 width=44) (actual time=0.004..0.004 rows=0 loops=1,519)

  • Index Cond: (id = twlqt.loan_id)
  • Filter: ((NOT quickbooks_exclude_from_export) AND (NOT is_participation) AND (twlqt.date >= COALESCE(quickbooks_export_start_date, '-infinity'::date)))
  • Rows Removed by Filter: 1
18. 1.504 1.504 ↑ 1.0 1 752

Index Scan using subfunds_pkey on subfunds sf (cost=0.15..0.83 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=752)

  • Index Cond: (id = l.subfund_id)
19. 85.728 408,519.488 ↓ 577.5 1,155 752

Append (cost=374.99..1,017.58 rows=2 width=708) (actual time=2.815..543.244 rows=1,155 loops=752)

20. 0.000 1,851.424 ↓ 0.0 0 752

Nested Loop Left Join (cost=374.99..508.72 rows=1 width=708) (actual time=2.462..2.462 rows=0 loops=752)

  • Join Filter: (a.addressable_id = p.id)
21. 0.000 1,851.424 ↓ 0.0 0 752

Nested Loop Left Join (cost=374.99..487.71 rows=1 width=316) (actual time=2.462..2.462 rows=0 loops=752)

  • Join Filter: (ws.websiteable_id = p.id)
22. 0.000 1,851.424 ↓ 0.0 0 752

Nested Loop Left Join (cost=374.99..471.45 rows=1 width=284) (actual time=2.462..2.462 rows=0 loops=752)

  • Join Filter: (em.emailable_id = p.id)
23. 0.000 1,851.424 ↓ 0.0 0 752

Nested Loop Left Join (cost=374.99..455.18 rows=1 width=252) (actual time=2.462..2.462 rows=0 loops=752)

  • Join Filter: (ph.phoneable_id = p.id)
24. 0.000 1,851.424 ↓ 0.0 0 752

Nested Loop (cost=374.99..424.52 rows=1 width=220) (actual time=2.462..2.462 rows=0 loops=752)

25. 721.168 1,851.424 ↓ 0.0 0 752

Nested Loop (cost=374.85..416.29 rows=1 width=88) (actual time=2.462..2.462 rows=0 loops=752)

  • Join Filter: (p.id = d.participant_id)
  • Rows Removed by Join Filter: 18480
26. 94.000 261.696 ↓ 8.8 1,155 752

Subquery Scan on d (cost=374.85..397.10 rows=131 width=8) (actual time=0.002..0.348 rows=1,155 loops=752)

  • Filter: d."primary
  • Rows Removed by Filter: 46
27. 116.560 167.696 ↓ 4.6 1,201 752

Unique (cost=374.85..394.48 rows=262 width=38) (actual time=0.002..0.223 rows=1,201 loops=752)

28. 50.389 51.136 ↑ 2.2 1,201 752

Sort (cost=374.85..381.39 rows=2,618 width=38) (actual time=0.002..0.068 rows=1,201 loops=752)

  • Sort Key: d_1.loan_id, d_1.participant_id
  • Sort Method: quicksort Memory: 142kB
29. 0.112 0.747 ↑ 2.2 1,201 1

Result (cost=0.00..226.22 rows=2,618 width=38) (actual time=0.008..0.747 rows=1,201 loops=1)

30. 0.082 0.635 ↑ 2.2 1,201 1

Append (cost=0.00..200.04 rows=2,618 width=9) (actual time=0.007..0.635 rows=1,201 loops=1)

31. 0.137 0.137 ↑ 1.1 1,201 1

Seq Scan on debtors d_1 (cost=0.00..30.09 rows=1,309 width=9) (actual time=0.006..0.137 rows=1,201 loops=1)

32. 0.001 0.416 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=95.68..156.86 rows=1,309 width=9) (actual time=0.416..0.416 rows=0 loops=1)

33. 0.006 0.415 ↓ 0.0 0 1

Hash Join (cost=95.68..143.77 rows=1,309 width=38) (actual time=0.415..0.415 rows=0 loops=1)

  • Hash Cond: (d_2.loan_id = pl.id)
34. 0.001 0.001 ↑ 1,309.0 1 1

Seq Scan on debtors d_2 (cost=0.00..30.09 rows=1,309 width=9) (actual time=0.001..0.001 rows=1 loops=1)

35. 0.000 0.408 ↓ 0.0 0 1

Hash (cost=92.09..92.09 rows=287 width=12) (actual time=0.408..0.408 rows=0 loops=1)

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

Hash Join (cost=47.46..92.09 rows=287 width=12) (actual time=0.408..0.408 rows=0 loops=1)

  • Hash Cond: (l_1.participation_loan_id = pl.id)
37. 0.076 0.076 ↓ 4.0 1,155 1

Seq Scan on loans l_1 (cost=0.00..43.87 rows=287 width=8) (actual time=0.003..0.076 rows=1,155 loops=1)

38. 0.120 0.278 ↓ 4.0 1,155 1

Hash (cost=43.87..43.87 rows=287 width=4) (actual time=0.277..0.278 rows=1,155 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 57kB
39. 0.158 0.158 ↓ 4.0 1,155 1

Seq Scan on loans pl (cost=0.00..43.87 rows=287 width=4) (actual time=0.002..0.158 rows=1,155 loops=1)

40. 868.497 868.560 ↓ 8.0 16 868,560

Materialize (cost=0.00..15.26 rows=2 width=84) (actual time=0.000..0.001 rows=16 loops=868,560)

41. 0.063 0.063 ↓ 8.0 16 1

Seq Scan on participants p (cost=0.00..15.25 rows=2 width=84) (actual time=0.018..0.063 rows=16 loops=1)

  • Filter: ((data_type)::text = 'Participant::Business'::text)
  • Rows Removed by Filter: 715
42. 0.000 0.000 ↓ 0.0 0

Index Scan using participant_businesses_pkey on participant_businesses pb (cost=0.14..8.16 rows=1 width=140) (never executed)

  • Index Cond: (id = p.data_id)
43. 0.000 0.000 ↓ 0.0 0

Seq Scan on phones ph (cost=0.00..30.64 rows=2 width=36) (never executed)

  • Filter: ("primary" AND ((phoneable_type)::text = 'Participant'::text))
44. 0.000 0.000 ↓ 0.0 0

Seq Scan on emails em (cost=0.00..16.25 rows=1 width=36) (never executed)

  • Filter: ("primary" AND ((emailable_type)::text = 'Participant'::text))
45. 0.000 0.000 ↓ 0.0 0

Seq Scan on websites ws (cost=0.00..16.25 rows=1 width=36) (never executed)

  • Filter: ("primary" AND ((websiteable_type)::text = 'Participant'::text))
46. 0.000 0.000 ↓ 0.0 0

Seq Scan on addresses a (cost=0.00..21.00 rows=1 width=196) (never executed)

  • Filter: ("primary" AND ((addressable_type)::text = 'Participant'::text))
47. 184.992 406,582.336 ↓ 1,155.0 1,155 752

Subquery Scan on *SELECT* 2 (cost=375.12..508.84 rows=1 width=708) (actual time=0.352..540.668 rows=1,155 loops=752)

48. 34,615.312 406,397.344 ↓ 1,155.0 1,155 752

Nested Loop Left Join (cost=375.12..508.83 rows=1 width=708) (actual time=0.352..540.422 rows=1,155 loops=752)

  • Join Filter: (a_1.addressable_id = p_1.id)
  • Rows Removed by Join Filter: 839686
49. 1,069.344 263,212.032 ↓ 1,155.0 1,155 752

Nested Loop Left Join (cost=375.12..487.81 rows=1 width=412) (actual time=0.252..350.016 rows=1,155 loops=752)

  • Join Filter: (ws_1.websiteable_id = p_1.id)
  • Rows Removed by Join Filter: 1153
50. 24,021.136 262,142.688 ↓ 1,155.0 1,155 752

Nested Loop Left Join (cost=375.12..471.55 rows=1 width=380) (actual time=0.250..348.594 rows=1,155 loops=752)

  • Join Filter: (em_1.emailable_id = p_1.id)
  • Rows Removed by Join Filter: 596276
51. 32,249.520 172,979.552 ↓ 1,155.0 1,155 752

Nested Loop Left Join (cost=375.12..455.28 rows=1 width=348) (actual time=0.175..230.026 rows=1,155 loops=752)

  • Join Filter: (ph_1.phoneable_id = p_1.id)
  • Rows Removed by Join Filter: 836225
52. 515.120 27,817.232 ↓ 1,155.0 1,155 752

Nested Loop (cost=375.12..424.62 rows=1 width=316) (actual time=0.012..36.991 rows=1,155 loops=752)

53. 14,770.032 26,433.552 ↓ 1,155.0 1,155 752

Nested Loop (cost=374.85..416.29 rows=1 width=88) (actual time=0.009..35.151 rows=1,155 loops=752)

  • Join Filter: (p_1.id = d_3.participant_id)
  • Rows Removed by Join Filter: 337947
54. 121.824 372.240 ↓ 8.8 1,155 752

Subquery Scan on d_3 (cost=374.85..397.10 rows=131 width=8) (actual time=0.002..0.495 rows=1,155 loops=752)

  • Filter: d_3."primary
  • Rows Removed by Filter: 46
55. 166.192 250.416 ↓ 4.6 1,201 752

Unique (cost=374.85..394.48 rows=262 width=38) (actual time=0.002..0.333 rows=1,201 loops=752)

56. 83.535 84.224 ↑ 2.2 1,201 752

Sort (cost=374.85..381.39 rows=2,618 width=38) (actual time=0.002..0.112 rows=1,201 loops=752)

  • Sort Key: d_4.loan_id, d_4.participant_id
  • Sort Method: quicksort Memory: 142kB
57. 0.114 0.689 ↑ 2.2 1,201 1

Result (cost=0.00..226.22 rows=2,618 width=38) (actual time=0.004..0.689 rows=1,201 loops=1)

58. 0.084 0.575 ↑ 2.2 1,201 1

Append (cost=0.00..200.04 rows=2,618 width=9) (actual time=0.003..0.575 rows=1,201 loops=1)

59. 0.127 0.127 ↑ 1.1 1,201 1

Seq Scan on debtors d_4 (cost=0.00..30.09 rows=1,309 width=9) (actual time=0.003..0.127 rows=1,201 loops=1)

60. 0.000 0.364 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_2 (cost=95.68..156.86 rows=1,309 width=9) (actual time=0.364..0.364 rows=0 loops=1)

61. 0.003 0.364 ↓ 0.0 0 1

Hash Join (cost=95.68..143.77 rows=1,309 width=38) (actual time=0.364..0.364 rows=0 loops=1)

  • Hash Cond: (d_5.loan_id = pl_1.id)
62. 0.002 0.002 ↑ 1,309.0 1 1

Seq Scan on debtors d_5 (cost=0.00..30.09 rows=1,309 width=9) (actual time=0.002..0.002 rows=1 loops=1)

63. 0.000 0.359 ↓ 0.0 0 1

Hash (cost=92.09..92.09 rows=287 width=12) (actual time=0.359..0.359 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
64. 0.054 0.359 ↓ 0.0 0 1

Hash Join (cost=47.46..92.09 rows=287 width=12) (actual time=0.359..0.359 rows=0 loops=1)

  • Hash Cond: (l_2.participation_loan_id = pl_1.id)
65. 0.075 0.075 ↓ 4.0 1,155 1

Seq Scan on loans l_2 (cost=0.00..43.87 rows=287 width=8) (actual time=0.002..0.075 rows=1,155 loops=1)

66. 0.119 0.230 ↓ 4.0 1,155 1

Hash (cost=43.87..43.87 rows=287 width=4) (actual time=0.230..0.230 rows=1,155 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 57kB
67. 0.111 0.111 ↓ 4.0 1,155 1

Seq Scan on loans pl_1 (cost=0.00..43.87 rows=287 width=4) (actual time=0.002..0.111 rows=1,155 loops=1)

68. 11,291.141 11,291.280 ↓ 147.0 294 868,560

Materialize (cost=0.00..15.26 rows=2 width=84) (actual time=0.000..0.013 rows=294 loops=868,560)

69. 0.139 0.139 ↓ 357.5 715 1

Seq Scan on participants p_1 (cost=0.00..15.25 rows=2 width=84) (actual time=0.005..0.139 rows=715 loops=1)

  • Filter: ((data_type)::text = 'Participant::Person'::text)
  • Rows Removed by Filter: 16
70. 868.560 868.560 ↑ 1.0 1 868,560

Index Scan using participant_people_pkey on participant_people pp (cost=0.27..8.29 rows=1 width=236) (actual time=0.001..0.001 rows=1 loops=868,560)

  • Index Cond: (id = p_1.data_id)
71. 112,912.800 112,912.800 ↓ 362.5 725 868,560

Seq Scan on phones ph_1 (cost=0.00..30.64 rows=2 width=36) (actual time=0.001..0.130 rows=725 loops=868,560)

  • Filter: ("primary" AND ((phoneable_type)::text = 'Participant'::text))
  • Rows Removed by Filter: 206
72. 65,142.000 65,142.000 ↓ 517.0 517 868,560

Seq Scan on emails em_1 (cost=0.00..16.25 rows=1 width=36) (actual time=0.001..0.075 rows=517 loops=868,560)

  • Filter: ("primary" AND ((emailable_type)::text = 'Participant'::text))
73. 0.000 0.000 ↑ 1.0 1 868,560

Seq Scan on websites ws_1 (cost=0.00..16.25 rows=1 width=36) (actual time=0.000..0.000 rows=1 loops=868,560)

  • Filter: ("primary" AND ((websiteable_type)::text = 'Participant'::text))
74. 108,570.000 108,570.000 ↓ 728.0 728 868,560

Seq Scan on addresses a_1 (cost=0.00..21.00 rows=1 width=196) (actual time=0.001..0.125 rows=728 loops=868,560)

  • Filter: ("primary" AND ((addressable_type)::text = 'Participant'::text))
  • Rows Removed by Filter: 108
75. 7.520 7.520 ↑ 1.0 1 752

Seq Scan on organizations o (cost=0.00..13.01 rows=1 width=21) (actual time=0.005..0.010 rows=1 loops=752)

76. 0.040 0.040 ↑ 1.0 1 20

Index Scan using bank_accounts_pkey on bank_accounts ba (cost=0.15..0.34 rows=1 width=68) (actual time=0.002..0.002 rows=1 loops=20)

  • Index Cond: (id = sf.bank_account_id)
77. 3.600 3.600 ↑ 5.0 1 20

Function Scan on fn_prior_transaction pt (cost=0.25..12.75 rows=5 width=36) (actual time=0.180..0.180 rows=1 loops=20)

  • Filter: (loan_id = twlqt.loan_id)
Planning time : 3.531 ms
Execution time : 408,717.002 ms