explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VFkI

Settings
# exclusive inclusive rows x rows loops node
1. 31.466 451,377.550 ↓ 6.0 6 1

GroupAggregate (cost=3,644,975.87..3,644,975.91 rows=1 width=89) (actual time=451,358.181..451,377.550 rows=6 loops=1)

  • Group Key: country.country_desc, (CASE WHEN (auth_transaction_log.bill_transaction_type_id IS NULL) THEN 'False'::text ELSE 'True'::text END)
2.          

CTE a

3. 0.011 146,063.515 ↑ 1.7 27 1

Unique (cost=972,022.84..972,023.41 rows=45 width=11) (actual time=146,057.310..146,063.515 rows=27 loops=1)

4. 4.413 146,063.504 ↑ 1.7 27 1

Group (cost=972,022.84..972,023.18 rows=45 width=11) (actual time=146,057.309..146,063.504 rows=27 loops=1)

  • Group Key: solicitation_template_1.country_id, clients_1.cln_client_id
5. 46.119 146,059.091 ↓ 521.2 23,456 1

Sort (cost=972,022.84..972,022.96 rows=45 width=11) (actual time=146,057.308..146,059.091 rows=23,456 loops=1)

  • Sort Key: solicitation_template_1.country_id, clients_1.cln_client_id
  • Sort Method: quicksort Memory: 1,868kB
6. 36.582 146,012.972 ↓ 521.2 23,456 1

Hash Join (cost=19,229.00..972,021.61 rows=45 width=11) (actual time=518.085..146,012.972 rows=23,456 loops=1)

  • Hash Cond: (campaigns_1.cam_website_id = websites_1.wbs_website_id)
7. 61.239 145,973.210 ↓ 411.5 23,456 1

Nested Loop (cost=18,994.95..971,786.89 rows=57 width=11) (actual time=514.841..145,973.210 rows=23,456 loops=1)

  • Join Filter: (solicitation_1.sol_id = campaigns_1.cam_campaign_id)
8. 81.312 145,794.691 ↓ 360.9 23,456 1

Hash Join (cost=18,994.65..971,765.91 rows=65 width=17) (actual time=514.810..145,794.691 rows=23,456 loops=1)

  • Hash Cond: (solicitation_1.sol_template_id = solicitation_template_1.sol_template_id)
  • Join Filter: (((auth_transaction_log_1.date_created >= '2020-08-19 00:00:00'::timestamp without time zone) AND (solicitation_template_1.country_id = '4'::numeric)) OR ((auth_transaction_log_1.date_created >= '2020-08-24 00:00:00'::timestamp without time zone) AND (solicitation_template_1.country_id = '6'::numeric)) OR ((auth_transaction_log_1.date_created >= '2020-09-03 00:00:00'::timestamp without time zone) AND (solicitation_template_1.country_id = '3'::numeric)))
  • Rows Removed by Join Filter: 26
9. 35.533 145,711.166 ↓ 13.4 23,489 1

Nested Loop (cost=18,809.75..971,569.86 rows=1,752 width=25) (actual time=512.566..145,711.166 rows=23,489 loops=1)

10. 72.692 145,534.699 ↓ 13.4 23,489 1

Nested Loop (cost=18,809.45..971,008.86 rows=1,752 width=14) (actual time=512.542..145,534.699 rows=23,489 loops=1)

11. 144,688.436 145,180.139 ↓ 4.1 23,489 1

Bitmap Heap Scan on auth_transaction_log auth_transaction_log_1 (cost=18,808.89..956,318.45 rows=5,676 width=14) (actual time=512.461..145,180.139 rows=23,489 loops=1)

  • Recheck Cond: ((date_created >= '2020-08-19 00:00:00'::timestamp without time zone) OR (date_created >= '2020-08-24 00:00:00'::timestamp without time zone) OR (date_created >= '2020-09-03 00:00:00'::timestamp without time zone))
  • Rows Removed by Index Recheck: 87,799
  • Filter: (bill_transaction_type_id = '17'::numeric)
  • Rows Removed by Filter: 572,712
  • Heap Blocks: exact=34,530 lossy=105,786
12. 0.004 491.703 ↓ 0.0 0 1

BitmapOr (cost=18,808.89..18,808.89 rows=1,452,178 width=0) (actual time=491.703..491.703 rows=0 loops=1)

13. 324.063 324.063 ↓ 1.2 656,511 1

Bitmap Index Scan on idx_authtransactionlog_dtcrtd (cost=0.00..7,008.73 rows=541,240 width=0) (actual time=324.063..324.063 rows=656,511 loops=1)

  • Index Cond: (date_created >= '2020-08-19 00:00:00'::timestamp without time zone)
14. 97.806 97.806 ↓ 1.2 617,591 1

Bitmap Index Scan on idx_authtransactionlog_dtcrtd (cost=0.00..6,502.99 rows=502,208 width=0) (actual time=97.806..97.806 rows=617,591 loops=1)

  • Index Cond: (date_created >= '2020-08-24 00:00:00'::timestamp without time zone)
15. 69.830 69.830 ↓ 1.3 528,300 1

Bitmap Index Scan on idx_authtransactionlog_dtcrtd (cost=0.00..5,292.91 rows=408,730 width=0) (actual time=69.830..69.830 rows=528,300 loops=1)

  • Index Cond: (date_created >= '2020-09-03 00:00:00'::timestamp without time zone)
16. 281.868 281.868 ↑ 1.0 1 23,489

Index Scan using member_pkey on member member_1 (cost=0.56..2.58 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=23,489)

  • Index Cond: (mem_id = auth_transaction_log_1.mem_id)
  • Filter: (date(join_date) >= '2020-08-19'::date)
17. 140.934 140.934 ↑ 1.0 1 23,489

Index Scan using pk_solicitation on solicitation solicitation_1 (cost=0.29..0.31 rows=1 width=11) (actual time=0.005..0.006 rows=1 loops=23,489)

  • Index Cond: (sol_id = member_1.sol_id)
18. 0.198 2.213 ↓ 1.3 660 1

Hash (cost=178.60..178.60 rows=505 width=10) (actual time=2.213..2.213 rows=660 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
19. 2.015 2.015 ↓ 1.3 660 1

Seq Scan on solicitation_template solicitation_template_1 (cost=0.00..178.60 rows=505 width=10) (actual time=0.405..2.015 rows=660 loops=1)

  • Filter: ((country_id = '4'::numeric) OR (country_id = '6'::numeric) OR (country_id = '3'::numeric))
  • Rows Removed by Filter: 4,910
20. 117.280 117.280 ↑ 1.0 1 23,456

Index Scan using pk_campaigns on campaigns campaigns_1 (cost=0.29..0.31 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=23,456)

  • Index Cond: (cam_campaign_id = member_1.sol_id)
21. 0.702 3.180 ↓ 1.3 3,721 1

Hash (cost=198.11..198.11 rows=2,876 width=12) (actual time=3.180..3.180 rows=3,721 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 198kB
22. 1.473 2.478 ↓ 1.3 3,721 1

Hash Join (cost=58.79..198.11 rows=2,876 width=12) (actual time=0.743..2.478 rows=3,721 loops=1)

  • Hash Cond: (websites_1.wbs_client_id = clients_1.cln_client_id)
23. 0.280 0.280 ↓ 1.0 3,723 1

Seq Scan on websites websites_1 (cost=0.00..96.77 rows=3,677 width=12) (actual time=0.003..0.280 rows=3,723 loops=1)

24. 0.434 0.725 ↓ 1.3 2,346 1

Hash (cost=36.74..36.74 rows=1,764 width=6) (actual time=0.725..0.725 rows=2,346 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 121kB
25. 0.291 0.291 ↓ 1.3 2,346 1

Index Only Scan using pk_clients on clients clients_1 (cost=0.28..36.74 rows=1,764 width=6) (actual time=0.038..0.291 rows=2,346 loops=1)

  • Heap Fetches: 0
26.          

CTE b

27. 1,389.368 1,389.368 ↓ 2.8 9,021 1

Seq Scan on fd_reconciliation (cost=0.00..43,007.72 rows=3,217 width=32) (actual time=1,127.273..1,389.368 rows=9,021 loops=1)

  • Filter: ((date_created > '2020-09-18 00:00:00'::timestamp without time zone) AND ((record_type)::text = '009'::text))
  • Rows Removed by Filter: 288,020
28. 59.497 451,346.084 ↓ 34,797.0 34,797 1

Sort (cost=2,629,944.74..2,629,944.75 rows=1 width=81) (actual time=451,340.040..451,346.084 rows=34,797 loops=1)

  • Sort Key: country.country_desc, (CASE WHEN (auth_transaction_log.bill_transaction_type_id IS NULL) THEN 'False'::text ELSE 'True'::text END)
  • Sort Method: quicksort Memory: 3,664kB
29. 62,276.314 451,286.587 ↓ 34,797.0 34,797 1

Nested Loop Left Join (cost=1,785.63..2,629,944.73 rows=1 width=81) (actual time=355,308.403..451,286.587 rows=34,797 loops=1)

  • Join Filter: (b.special_ref_2 = bill_history.bill_id)
  • Rows Removed by Join Filter: 313,903,655
30. 32.003 359,224.041 ↓ 34,797.0 34,797 1

Nested Loop (cost=1,785.63..2,629,840.18 rows=1 width=22) (actual time=353,914.411..359,224.041 rows=34,797 loops=1)

31. 93.644 358,432.886 ↓ 47,447.0 47,447 1

Nested Loop (cost=1,785.06..2,629,836.60 rows=1 width=21) (actual time=353,914.178..358,432.886 rows=47,447 loops=1)

  • Join Filter: (auth_transaction_log.mem_id = member_term_detail.mem_id)
32. 66.260 357,828.762 ↓ 63,810.0 63,810 1

Nested Loop (cost=1,784.49..2,629,835.68 rows=1 width=26) (actual time=353,912.725..357,828.762 rows=63,810 loops=1)

33. 340.028 357,571.072 ↓ 63,810.0 63,810 1

Nested Loop (cost=1,784.22..2,629,835.37 rows=1 width=48) (actual time=353,912.708..357,571.072 rows=63,810 loops=1)

  • Join Filter: (member.join_date < (('now'::cstring)::date - (bill_cycle.unit_count)::integer))
  • Rows Removed by Join Filter: 110,617
34. 177.166 356,882.190 ↓ 43,606.8 174,427 1

Hash Join (cost=1,784.07..2,629,834.63 rows=4 width=61) (actual time=353,906.179..356,882.190 rows=174,427 loops=1)

  • Hash Cond: ((country.country_id = a.country_id) AND (websites.wbs_client_id = a.cln_client_id))
35. 153.166 210,641.487 ↓ 28.4 261,580 1

Nested Loop (cost=1,782.50..2,629,764.05 rows=9,195 width=55) (actual time=207,842.622..210,641.487 rows=261,580 loops=1)

36. 1,330.078 209,703.581 ↓ 28.4 261,580 1

Nested Loop (cost=1,782.22..2,626,469.56 rows=9,195 width=55) (actual time=207,842.600..209,703.581 rows=261,580 loops=1)

  • Join Filter: (solicitation_template.country_id = country.country_id)
  • Rows Removed by Join Filter: 3,923,700
37. 0.079 0.079 ↑ 1.0 16 1

Index Scan using pk_country on country (cost=0.14..3.38 rows=16 width=14) (actual time=0.015..0.079 rows=16 loops=1)

38. 821.853 208,373.424 ↓ 28.4 261,580 16

Materialize (cost=1,782.08..2,624,282.37 rows=9,195 width=41) (actual time=23.847..13,023.339 rows=261,580 loops=16)

39. 447.573 207,551.571 ↓ 28.4 261,580 1

Nested Loop (cost=1,782.08..2,624,236.40 rows=9,195 width=41) (actual time=381.468..207,551.571 rows=261,580 loops=1)

  • Join Filter: (((auth_transaction_log.date_created >= '2020-08-19 00:00:00'::timestamp without time zone) AND (solicitation_template.country_id = '4'::numeric)) OR ((auth_transaction_log.date_created >= '2020-08-24 00:00:00'::timestamp without time zone) AND (solicitation_template.country_id = '6'::numeric)) OR ((auth_transaction_log.date_created >= '2020-09-03 00:00:00'::timestamp without time zone) AND (solicitation_template.country_id = '3'::numeric)))
  • Rows Removed by Join Filter: 43,181
40. 65.520 203,672.449 ↑ 3.8 311,959 1

Nested Loop (cost=1,781.65..1,830,479.50 rows=1,185,282 width=30) (actual time=380.318..203,672.449 rows=311,959 loops=1)

41. 53.196 833.181 ↓ 6.2 39,244 1

Nested Loop (cost=1,781.08..10,226.68 rows=6,341 width=34) (actual time=54.204..833.181 rows=39,244 loops=1)

42. 71.758 309.057 ↓ 5.4 39,244 1

Hash Join (cost=1,780.79..6,801.60 rows=7,265 width=22) (actual time=53.400..309.057 rows=39,244 loops=1)

  • Hash Cond: (sol_term_detail.sol_id = solicitation.sol_id)
43. 184.048 184.048 ↓ 1.4 99,716 1

Seq Scan on sol_term_detail (cost=0.00..4,687.36 rows=69,547 width=11) (actual time=0.010..184.048 rows=99,716 loops=1)

  • Filter: (term_num_seq = '1'::numeric)
  • Rows Removed by Filter: 105,454
44. 12.321 53.251 ↓ 3.9 39,246 1

Hash (cost=1,655.45..1,655.45 rows=10,027 width=11) (actual time=53.251..53.251 rows=39,246 loops=1)

  • Buckets: 65,536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2,199kB
45. 7.670 40.930 ↓ 3.9 39,246 1

Nested Loop (cost=0.29..1,655.45 rows=10,027 width=11) (actual time=0.424..40.930 rows=39,246 loops=1)

46. 2.240 2.240 ↓ 1.3 660 1

Seq Scan on solicitation_template (cost=0.00..178.60 rows=505 width=10) (actual time=0.408..2.240 rows=660 loops=1)

  • Filter: ((country_id = '4'::numeric) OR (country_id = '6'::numeric) OR (country_id = '3'::numeric))
  • Rows Removed by Filter: 4,910
47. 31.020 31.020 ↓ 2.5 59 660

Index Scan using idx_sol_sol_template_id on solicitation (cost=0.29..2.68 rows=24 width=11) (actual time=0.010..0.047 rows=59 loops=660)

  • Index Cond: (sol_template_id = solicitation_template.sol_template_id)
48. 470.928 470.928 ↑ 1.0 1 39,244

Index Scan using campaigns_pkey on campaigns (cost=0.29..0.46 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=39,244)

  • Index Cond: (cam_campaign_id = solicitation.sol_id)
49. 202,773.748 202,773.748 ↑ 111.4 8 39,244

Index Scan using idx_mem_sol_id on member (cost=0.56..278.15 rows=891 width=20) (actual time=4.922..5.167 rows=8 loops=39,244)

  • Index Cond: (sol_id = solicitation.sol_id)
  • Filter: (date(join_date) >= '2020-08-19'::date)
  • Rows Removed by Filter: 367
50. 3,431.549 3,431.549 ↑ 1.0 1 311,959

Index Scan using idx_authlog_memid on auth_transaction_log (cost=0.43..0.64 rows=1 width=19) (actual time=0.011..0.011 rows=1 loops=311,959)

  • Index Cond: (mem_id = member.mem_id)
  • Filter: (((bill_transaction_type_id = '17'::numeric) OR (bill_transaction_type_id IS NULL)) AND ((date_created >= '2020-08-19 00:00:00'::timestamp without time zone) OR (date_created >= '2020-08-24 00:00:00'::timestamp without time zone) OR (date_created >= '2020-09-03 00:00:00'::timestamp without time zone)))
51. 784.740 784.740 ↑ 1.0 1 261,580

Index Scan using pk_websites on websites (cost=0.28..0.35 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=261,580)

  • Index Cond: (wbs_website_id = campaigns.cam_website_id)
52. 0.016 146,063.537 ↑ 1.7 27 1

Hash (cost=0.90..0.90 rows=45 width=32) (actual time=146,063.537..146,063.537 rows=27 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
53. 146,063.521 146,063.521 ↑ 1.7 27 1

CTE Scan on a (cost=0.00..0.90 rows=45 width=32) (actual time=146,057.312..146,063.521 rows=27 loops=1)

54. 348.854 348.854 ↑ 1.0 1 174,427

Index Scan using pk_bill_cycle on bill_cycle (cost=0.14..0.16 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=174,427)

  • Index Cond: (bill_cycle_id = sol_term_detail.bill_cycle_id)
55. 191.430 191.430 ↑ 1.0 1 63,810

Index Only Scan using pk_clients on clients (cost=0.28..0.30 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=63,810)

  • Index Cond: (cln_client_id = websites.wbs_client_id)
  • Heap Fetches: 0
56. 510.480 510.480 ↑ 1.0 1 63,810

Index Scan using idx_member_term_detail_conc on member_term_detail (cost=0.57..0.91 rows=1 width=13) (actual time=0.008..0.008 rows=1 loops=63,810)

  • Index Cond: ((mem_id = member.mem_id) AND (term_num_seq = '2'::numeric))
57. 759.152 759.152 ↑ 1.0 1 47,447

Index Scan using idx_bill_hst_mbr_term_id on bill_history (cost=0.57..3.57 rows=1 width=15) (actual time=0.015..0.016 rows=1 loops=47,447)

  • Index Cond: (member_term_detail_id = member_term_detail.member_term_detail_id)
  • Filter: ((parent_bill_id IS NULL) AND (date_created >= '2020-08-19 00:00:00'::timestamp without time zone) AND (bill_type_id = '2'::numeric) AND (bill_cycle_stage_id = '3'::numeric))
  • Rows Removed by Filter: 1
58. 29,786.232 29,786.232 ↓ 2.8 9,021 34,797

CTE Scan on b (cost=0.00..64.34 rows=3,217 width=32) (actual time=0.033..0.856 rows=9,021 loops=34,797)

Planning time : 40.164 ms
Execution time : 451,386.849 ms