explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cloc : Web based staff client billing history

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 225,991.355 ↑ 22,287,870,073,612,796,172,435,456.0 2 1

Nested Loop Left Join (cost=5.51..2,093,924,919,999,451,103,232.00 rows=44,575,740,147,225,592,344,870,912 width=140) (actual time=225,988.034..225,991.355 rows=2 loops=1)

2. 3,180.576 225,991.271 ↑ 452,518,020,701,536,911,360.0 2 1

Nested Loop (cost=5.51..1,050,193,655,900,159,737,856.00 rows=905,036,041,403,073,691,648 width=140) (actual time=225,987.993..225,991.271 rows=2 loops=1)

3. 3,776.478 199,966.610 ↑ 120,419,330,726,403.0 4,568,817 1

Nested Loop Left Join (cost=5.51..354,629,028,297,289,957,376.00 rows=550,173,885,351,412,563,968 width=140) (actual time=0.475..199,966.610 rows=4,568,817 loops=1)

4. 2,867.748 182,483.681 ↑ 120,419,330,726,403.0 4,568,817 1

Nested Loop Left Join (cost=5.08..36,915,498,324,831,752.00 rows=550,173,885,351,412,563,968 width=148) (actual time=0.468..182,483.681 rows=4,568,817 loops=1)

5. 3,968.063 143,065.397 ↑ 2,444,913,624.1 4,568,817 1

Nested Loop Left Join (cost=4.64..1,335,471,069,989.13 rows=11,170,362,929,190,354 width=140) (actual time=0.449..143,065.397 rows=4,568,817 loops=1)

6. 4,913.961 84,271.530 ↑ 49,639.9 4,568,817 1

Nested Loop Left Join (cost=3.78..28,878,528.96 rows=226,795,584,618 width=140) (actual time=0.427..84,271.530 rows=4,568,817 loops=1)

7. 1,523.025 15,394.131 ↑ 1.0 4,568,817 1

Nested Loop (cost=2.91..1,764,603.08 rows=4,604,706 width=140) (actual time=0.361..15,394.131 rows=4,568,817 loops=1)

8. 1,665.163 4,733.472 ↑ 1.0 4,568,817 1

Merge Append (cost=2.63..336,448.22 rows=4,604,706 width=80) (actual time=0.342..4,733.472 rows=4,568,817 loops=1)

  • Sort Key: p.payment_ts
9. 0.005 0.005 ↓ 0.0 0 1

Index Scan using m_p_time_idx on payment p (cost=0.12..4.14 rows=1 width=75) (actual time=0.005..0.005 rows=0 loops=1)

10. 0.009 0.010 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=1 width=75) (actual time=0.010..0.010 rows=0 loops=1)

  • Sort Key: p_1.payment_ts
  • Sort Method: quicksort Memory: 25kB
11. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on bnm_payment p_1 (cost=0.00..0.00 rows=1 width=75) (actual time=0.001..0.001 rows=0 loops=1)

12. 2,552.360 2,552.360 ↑ 1.0 3,819,125 1

Index Scan using money_account_adjustment_payment_ts_idx on account_adjustment p_2 (cost=0.43..141,885.92 rows=3,854,936 width=88) (actual time=0.079..2,552.360 rows=3,819,125 loops=1)

13. 0.007 0.007 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=1 width=75) (actual time=0.007..0.007 rows=0 loops=1)

  • Sort Key: p_3.payment_ts
  • Sort Method: quicksort Memory: 25kB
14. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on bnm_desk_payment p_3 (cost=0.00..0.00 rows=1 width=75) (actual time=0.000..0.000 rows=0 loops=1)

15. 0.827 0.827 ↑ 1.4 203 1

Index Scan using money_credit_payment_payment_ts_idx on credit_payment p_4 (cost=0.15..12.51 rows=291 width=38) (actual time=0.034..0.827 rows=203 loops=1)

16. 71.766 71.766 ↓ 1.0 80,440 1

Index Scan using money_forgive_payment_payment_ts_idx on forgive_payment p_5 (cost=0.29..2,464.97 rows=80,378 width=41) (actual time=0.044..71.766 rows=80,440 loops=1)

17. 7.217 7.217 ↑ 1.0 7,370 1

Index Scan using money_goods_payment_payment_ts_idx on goods_payment p_6 (cost=0.28..230.42 rows=7,409 width=42) (actual time=0.041..7.217 rows=7,370 loops=1)

18. 0.661 0.661 ↑ 1.1 262 1

Index Scan using money_work_payment_payment_ts_idx on work_payment p_7 (cost=0.15..12.65 rows=300 width=43) (actual time=0.020..0.661 rows=262 loops=1)

19. 400.235 400.235 ↑ 1.0 636,165 1

Index Scan using money_cash_payment_ts_idx on cash_payment p_8 (cost=0.42..20,172.07 rows=636,169 width=35) (actual time=0.039..400.235 rows=636,165 loops=1)

20. 22.006 22.006 ↑ 1.0 17,420 1

Index Scan using money_check_payment_ts_idx on check_payment p_9 (cost=0.29..557.86 rows=17,437 width=41) (actual time=0.029..22.006 rows=17,420 loops=1)

21. 13.215 13.215 ↓ 1.0 7,832 1

Index Scan using money_credit_card_payment_ts_idx on credit_card_payment p_10 (cost=0.28..299.04 rows=7,783 width=35) (actual time=0.028..13.215 rows=7,832 loops=1)

22. 9,137.634 9,137.634 ↑ 1.0 1 4,568,817

Index Scan using pg_class_oid_index on pg_class c (cost=0.28..0.30 rows=1 width=68) (actual time=0.001..0.002 rows=1 loops=4,568,817)

  • Index Cond: (oid = p.tableoid)
23. 4,662.261 63,963.438 ↑ 4.0 1 4,568,817

Nested Loop Left Join (cost=0.86..5.85 rows=4 width=8) (actual time=0.009..0.014 rows=1 loops=4,568,817)

24. 4,615.539 41,119.353 ↑ 4.0 1 4,568,817

Nested Loop Left Join (cost=0.43..3.26 rows=4 width=16) (actual time=0.006..0.009 rows=1 loops=4,568,817)

25. 9,137.634 27,412.902 ↑ 4.0 1 4,568,817

Append (cost=0.00..1.11 rows=4 width=8) (actual time=0.003..0.006 rows=1 loops=4,568,817)

26. 0.000 0.000 ↓ 0.0 0 4,568,817

Seq Scan on billable_xact __title_mbt (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=4,568,817)

  • Filter: (id = p.xact)
27. 9,137.634 9,137.634 ↑ 1.0 1 4,568,817

Index Only Scan using circulation_pkey on circulation __title_mbt_1 (cost=0.43..0.51 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=4,568,817)

  • Index Cond: (id = p.xact)
  • Heap Fetches: 3168243
28. 4,568.817 4,568.817 ↓ 0.0 0 4,568,817

Index Only Scan using reservation_pkey on reservation __title_mbt_2 (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=4,568,817)

  • Index Cond: (id = p.xact)
  • Heap Fetches: 0
29. 4,568.817 4,568.817 ↓ 0.0 0 4,568,817

Index Only Scan using grocery_pkey on grocery __title_mbt_3 (cost=0.42..0.44 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=4,568,817)

  • Index Cond: (id = p.xact)
  • Heap Fetches: 134630
30. 9,090.912 9,090.912 ↑ 1.0 1 4,545,456

Index Scan using circulation_pkey on circulation __title_circ (cost=0.43..0.53 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=4,545,456)

  • Index Cond: (id = __title_mbt.id)
31. 4,545.456 18,181.824 ↑ 2.0 1 4,545,456

Append (cost=0.43..0.63 rows=2 width=16) (actual time=0.003..0.004 rows=1 loops=4,545,456)

32. 13,636.368 13,636.368 ↑ 1.0 1 4,545,456

Index Scan using copy_pkey on copy __title_acp (cost=0.43..0.46 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=4,545,456)

  • Index Cond: (id = __title_circ.target_copy)
33. 0.000 0.000 ↓ 0.0 0 4,545,456

Index Scan using unit_pkey on unit __title_acp_1 (cost=0.14..0.16 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=4,545,456)

  • Index Cond: (id = __title_circ.target_copy)
34. 4,638.900 54,825.804 ↑ 4.0 1 4,568,817

Nested Loop Left Join (cost=0.86..5.85 rows=4 width=8) (actual time=0.007..0.012 rows=1 loops=4,568,817)

35. 4,615.539 36,550.536 ↑ 4.0 1 4,568,817

Nested Loop Left Join (cost=0.43..3.26 rows=4 width=16) (actual time=0.005..0.008 rows=1 loops=4,568,817)

36. 4,568.817 22,844.085 ↑ 4.0 1 4,568,817

Append (cost=0.00..1.11 rows=4 width=8) (actual time=0.003..0.005 rows=1 loops=4,568,817)

37. 0.000 0.000 ↓ 0.0 0 4,568,817

Seq Scan on billable_xact __record_id_mbt (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=4,568,817)

  • Filter: (id = p.xact)
38. 9,137.634 9,137.634 ↑ 1.0 1 4,568,817

Index Only Scan using circulation_pkey on circulation __record_id_mbt_1 (cost=0.43..0.51 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=4,568,817)

  • Index Cond: (id = p.xact)
  • Heap Fetches: 3168242
39. 4,568.817 4,568.817 ↓ 0.0 0 4,568,817

Index Only Scan using reservation_pkey on reservation __record_id_mbt_2 (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=4,568,817)

  • Index Cond: (id = p.xact)
  • Heap Fetches: 0
40. 4,568.817 4,568.817 ↓ 0.0 0 4,568,817

Index Only Scan using grocery_pkey on grocery __record_id_mbt_3 (cost=0.42..0.44 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=4,568,817)

  • Index Cond: (id = p.xact)
  • Heap Fetches: 134630
41. 9,090.912 9,090.912 ↑ 1.0 1 4,545,456

Index Scan using circulation_pkey on circulation __record_id_circ (cost=0.43..0.53 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=4,545,456)

  • Index Cond: (id = __record_id_mbt.id)
42. 4,545.456 13,636.368 ↑ 2.0 1 4,545,456

Append (cost=0.43..0.63 rows=2 width=16) (actual time=0.002..0.003 rows=1 loops=4,545,456)

43. 9,090.912 9,090.912 ↑ 1.0 1 4,545,456

Index Scan using copy_pkey on copy __record_id_acp (cost=0.43..0.46 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=4,545,456)

  • Index Cond: (id = __record_id_circ.target_copy)
44. 0.000 0.000 ↓ 0.0 0 4,545,456

Index Scan using unit_pkey on unit __record_id_acp_1 (cost=0.14..0.16 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=4,545,456)

  • Index Cond: (id = __record_id_circ.target_copy)
45. 4,615.539 36,550.536 ↑ 4.0 1 4,568,817

Nested Loop Left Join (cost=0.43..3.26 rows=4 width=16) (actual time=0.005..0.008 rows=1 loops=4,568,817)

46. 4,568.817 22,844.085 ↑ 4.0 1 4,568,817

Append (cost=0.00..1.11 rows=4 width=8) (actual time=0.003..0.005 rows=1 loops=4,568,817)

47. 0.000 0.000 ↓ 0.0 0 4,568,817

Seq Scan on billable_xact __copy_barcode_mbt (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=4,568,817)

  • Filter: (id = p.xact)
48. 9,137.634 9,137.634 ↑ 1.0 1 4,568,817

Index Only Scan using circulation_pkey on circulation __copy_barcode_mbt_1 (cost=0.43..0.51 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=4,568,817)

  • Index Cond: (id = p.xact)
  • Heap Fetches: 3168242
49. 4,568.817 4,568.817 ↓ 0.0 0 4,568,817

Index Only Scan using reservation_pkey on reservation __copy_barcode_mbt_2 (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=4,568,817)

  • Index Cond: (id = p.xact)
  • Heap Fetches: 0
50. 4,568.817 4,568.817 ↓ 0.0 0 4,568,817

Index Only Scan using grocery_pkey on grocery __copy_barcode_mbt_3 (cost=0.42..0.44 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=4,568,817)

  • Index Cond: (id = p.xact)
  • Heap Fetches: 134630
51. 9,090.912 9,090.912 ↑ 1.0 1 4,545,456

Index Scan using circulation_pkey on circulation __copy_barcode_circ (cost=0.43..0.53 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=4,545,456)

  • Index Cond: (id = __copy_barcode_mbt.id)
52. 4,568.817 13,706.451 ↑ 2.0 1 4,568,817

Append (cost=0.43..0.62 rows=2 width=8) (actual time=0.002..0.003 rows=1 loops=4,568,817)

53. 9,137.634 9,137.634 ↑ 1.0 1 4,568,817

Index Only Scan using copy_pkey on copy __copy_barcode_acp (cost=0.43..0.46 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=4,568,817)

  • Index Cond: (id = __copy_barcode_circ.target_copy)
  • Heap Fetches: 3974892
54. 0.000 0.000 ↓ 0.0 0 4,568,817

Index Only Scan using unit_pkey on unit __copy_barcode_acp_1 (cost=0.14..0.16 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=4,568,817)

  • Index Cond: (id = __copy_barcode_circ.target_copy)
  • Heap Fetches: 0
55. 4,568.817 22,844.085 ↓ 0.0 0 4,568,817

Append (cost=0.00..1.22 rows=4 width=8) (actual time=0.005..0.005 rows=0 loops=4,568,817)

56. 0.000 0.000 ↓ 0.0 0 4,568,817

Seq Scan on billable_xact "__xact.usr_mbt" (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=4,568,817)

  • Filter: ((usr = 425135) AND (p.xact = id))
57. 9,137.634 9,137.634 ↓ 0.0 0 4,568,817

Index Scan using circulation_pkey on circulation "__xact.usr_mbt_1" (cost=0.43..0.62 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=4,568,817)

  • Index Cond: (id = p.xact)
  • Filter: (usr = 425135)
  • Rows Removed by Filter: 1
58. 4,568.817 4,568.817 ↓ 0.0 0 4,568,817

Index Scan using reservation_pkey on reservation "__xact.usr_mbt_2" (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=4,568,817)

  • Index Cond: (id = p.xact)
  • Filter: (usr = 425135)
59. 4,568.817 4,568.817 ↓ 0.0 0 4,568,817

Index Scan using grocery_pkey on grocery "__xact.usr_mbt_3" (cost=0.42..0.45 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=4,568,817)

  • Index Cond: (id = p.xact)
  • Filter: (usr = 425135)
  • Rows Removed by Filter: 0
60. 0.034 0.058 ↑ 4.0 1 2

Append (cost=0.00..1.11 rows=4 width=8) (actual time=0.011..0.029 rows=1 loops=2)

61. 0.000 0.000 ↓ 0.0 0 2

Seq Scan on billable_xact "__xact.summary.xact_type_mbt" (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=2)

  • Filter: (id = p.xact)
62. 0.010 0.010 ↑ 1.0 1 2

Index Only Scan using circulation_pkey on circulation "__xact.summary.xact_type_mbt_1" (cost=0.43..0.51 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (id = p.xact)
  • Heap Fetches: 2
63. 0.004 0.004 ↓ 0.0 0 2

Index Only Scan using reservation_pkey on reservation "__xact.summary.xact_type_mbt_2" (cost=0.14..0.16 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=2)

  • Index Cond: (id = p.xact)
  • Heap Fetches: 0
64. 0.010 0.010 ↓ 0.0 0 2

Index Only Scan using grocery_pkey on grocery "__xact.summary.xact_type_mbt_3" (cost=0.42..0.44 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=2)

  • Index Cond: (id = p.xact)
  • Heap Fetches: 0
Planning time : 4.570 ms
Execution time : 225,992.136 ms