explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JwLK

Settings
# exclusive inclusive rows x rows loops node
1. 0.165 3,911.718 ↓ 0.0 4 1

HashAggregate (cost=0.00..0.00 rows=0 width=0) (actual time=3,911.712..3,911.718 rows=4 loops=1)

  • Output: remote_scan.ca_fs_caption, (pg_catalog.sum(remote_scan.journaldata))::bigint, (pg_catalog.sum(remote_scan.journallinedata))::bigint, sum(remote_scan.debitdata), sum(remote_scan.creditdata), sum(remote_scan.commonratenetpl)
  • Group Key: remote_scan.ca_fs_caption
2. 2,730.333 3,911.553 ↓ 0.0 128 1

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=3,911.537..3,911.553 rows=128 loops=1)

  • Output: remote_scan.ca_fs_caption, remote_scan.journaldata, remote_scan.journallinedata, remote_scan.debitdata, remote_scan.creditdata, remote_scan.commonratenetpl
  • Task Count: 32
  • Tasks Shown: One of 32
  • -> Task
  • Node: host=10.0.0.34 port=5,432 dbname=citus
3. 0.000 1,181.220 ↑ 2.8 4 1

Finalize GroupAggregate (cost=143,449.28..143,452.53 rows=11 width=123) (actual time=1,181.207..1,181.220 rows=4 loops=1)

  • Output: gl.ca_fs_caption, sum(gl.number_of_distinct_journals), sum(gl.number_of_journal_lines), sum(gl.total_reporting_debits), sum(abs(gl.total_reporting_credits)), sum(gl.total_reporting_net_pl)
  • Group Key: gl.ca_fs_caption
  • Buffers: shared hit=106,778
4. 101.400 1,276.228 ↑ 1.8 12 1

Gather Merge (cost=143,449.28..143,451.85 rows=22 width=123) (actual time=1,181.191..1,276.228 rows=12 loops=1)

  • Output: gl.ca_fs_caption, (PARTIAL sum(gl.number_of_distinct_journals)), (PARTIAL sum(gl.number_of_journal_lines)), (PARTIAL sum(gl.total_reporting_debits)), (PARTIAL sum(abs(gl.total_reporting_credits))), (PARTIAL sum(gl.total_reporting_net_pl))
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=308,943
5. 0.037 1,174.828 ↑ 2.8 4 3 / 3

Sort (cost=142,449.26..142,449.28 rows=11 width=123) (actual time=1,174.828..1,174.828 rows=4 loops=3)

  • Output: gl.ca_fs_caption, (PARTIAL sum(gl.number_of_distinct_journals)), (PARTIAL sum(gl.number_of_journal_lines)), (PARTIAL sum(gl.total_reporting_debits)), (PARTIAL sum(abs(gl.total_reporting_credits))), (PARTIAL sum(gl.total_reporting_net_pl))
  • Sort Key: gl.ca_fs_caption
  • Sort Method: quicksort Memory: 26kB
  • Worker 0: Sort Method: quicksort Memory: 26kB
  • Worker 1: Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=308,815
  • Worker 0: actual time=1,171.998..1171.999 rows=4 loops=1
  • Buffers: shared hit=100,937
  • Worker 1: actual time=1,172.143..1172.144 rows=4 loops=1
  • Buffers: shared hit=101,100
6. 86.433 1,174.791 ↑ 2.8 4 3 / 3

Partial HashAggregate (cost=142,448.87..142,449.07 rows=11 width=123) (actual time=1,174.789..1,174.791 rows=4 loops=3)

  • Output: gl.ca_fs_caption, PARTIAL sum(gl.number_of_distinct_journals), PARTIAL sum(gl.number_of_journal_lines), PARTIAL sum(gl.total_reporting_debits), PARTIAL sum(abs(gl.total_reporting_credits)), PARTIAL sum(gl.total_reporting_net_pl)
  • Group Key: gl.ca_fs_caption
  • Buffers: shared hit=308,795
  • Worker 0: actual time=1,171.940..1171.943 rows=4 loops=1
  • Buffers: shared hit=100,927
  • Worker 1: actual time=1,172.099..1172.101 rows=4 loops=1
  • Buffers: shared hit=101,090
7. 139.971 1,088.358 ↓ 1.1 225,873 3 / 3

Hash Join (cost=115,209.89..138,972.83 rows=198,631 width=26) (actual time=901.632..1,088.358 rows=225,873 loops=3)

  • Output: gl.ca_fs_caption, gl.number_of_distinct_journals, gl.number_of_journal_lines, gl.total_reporting_debits, gl.total_reporting_credits, gl.total_reporting_net_pl
  • Inner Unique: true
  • Hash Cond: (gl.gl_business_unit_and_journal_id_computed = gl_1.gl_business_unit_and_journal_id_computed)
  • Buffers: shared hit=308,795
  • Worker 0: actual time=983.957..1113.685 rows=150,275 loops=1
  • Buffers: shared hit=100,927
  • Worker 1: actual time=986.773..1115.720 rows=153,277 loops=1
  • Buffers: shared hit=101,090
8. 48.313 48.313 ↑ 1.2 424,487 3 / 3

Parallel Seq Scan on public.ap_7cc78509_f074_4145_89e3_c64174e2fa7e_fsli_aggr_106221 gl (cost=0.00..22,370.08 rows=530,608 width=50) (actual time=0.028..48.313 rows=424,487 loops=3)

  • Output: gl.gl_business_unit_and_journal_id_computed, gl.ca_fs_caption, gl.number_of_distinct_journals, gl.number_of_journal_lines, gl.total_local_debits, gl.total_local_credits, gl.total_local_net_pl, gl.total_local_amount, gl.total_reporting_debits, gl.total_reporting_credits, gl.total_reporting_net_pl, gl.total_reporting_amount
  • Buffers: shared hit=17,064
  • Worker 0: actual time=0.032..36.422 rows=275,982 loops=1
  • Buffers: shared hit=3,683
  • Worker 1: actual time=0.025..36.092 rows=287,941 loops=1
  • Buffers: shared hit=3,846
9. 93.444 900.074 ↓ 1.7 338,810 3 / 3

Hash (cost=112,784.48..112,784.48 rows=194,033 width=24) (actual time=900.074..900.074 rows=338,810 loops=3)

  • Output: gl_1.gl_business_unit_and_journal_id_computed
  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 22,878kB
  • Buffers: shared hit=291,731
  • Worker 0: actual time=982.404..982.405 rows=338,810 loops=1
  • Buffers: shared hit=97,244
  • Worker 1: actual time=985.155..985.155 rows=338,810 loops=1
  • Buffers: shared hit=97,244
10. 402.540 806.630 ↓ 1.7 338,810 3 / 3

HashAggregate (cost=108,903.82..110,844.15 rows=194,033 width=24) (actual time=730.235..806.630 rows=338,810 loops=3)

  • Output: gl_1.gl_business_unit_and_journal_id_computed
  • Group Key: gl_1.gl_business_unit_and_journal_id_computed
  • Buffers: shared hit=291,731
  • Worker 0: actual time=813.034..888.029 rows=338,810 loops=1
  • Buffers: shared hit=97,244
  • Worker 1: actual time=813.072..888.783 rows=338,810 loops=1
  • Buffers: shared hit=97,244
11. 371.000 404.090 ↓ 1.0 338,812 3 / 3

Bitmap Heap Scan on public.ap_7cc78509_f074_4145_89e3_c64174e2fa7e_mega_gl_106189 gl_1 (cost=4,423.76..108,065.81 rows=335,204 width=24) (actual time=50.652..404.090 rows=338,812 loops=3)

  • Output: gl_1.gl_business_unit_code, gl_1.gl_cost_centre, gl_1.gl_profit_centre, gl_1.gl_account_number, gl_1.gl_journal_id, gl_1.gl_journal_header_description, gl_1.gl_journal_id_line_number, gl_1.gl_journal_line_description, gl_1.gl_entry_date, gl_1.gl_entry_time, gl_1.gl_effective_date, gl_1.gl_last_modified_date, gl_1.gl_last_modified_by, gl_1.gl_fiscal_year, gl_1.gl_period, gl_1.gl_user_id, gl_1.gl_status_indicator, gl_1.gl_source, gl_1.gl_source_description, gl_1.gl_approver_id, gl_1.gl_approved_date, gl_1.gl_reversal_indicator, gl_1.gl_reversal_journal_id, gl_1.gl_amount_cr_dr_indicator, gl_1.gl_foreign_amount_currency, gl_1.gl_foreign_amount, gl_1.gl_local_amount_currency, gl_1.gl_local_amount, gl_1.gl_manual_indicator, gl_1.gl_segment01, gl_1.gl_segment02, gl_1.gl_segment03, gl_1.gl_segment04, gl_1.gl_segment05, gl_1.gl_reporting_amount_currency, gl_1.gl_reporting_amount, gl_1.localamountfabs, gl_1.gl_local_amount_debit, gl_1.gl_local_amount_credit, gl_1.gl_local_amount_string, gl_1.gl_reporting_amount_debit, gl_1.gl_reporting_amount_credit, gl_1.gl_reporting_amount_fabs, gl_1.gl_account_number_name, gl_1.ca_account_name, gl_1.ca_pl_indicator, gl_1.ca_account_type, gl_1.ca_fs_caption, gl_1.ut_user_active_status, gl_1.ut_user_active_status_mod_date, gl_1.ut_first_name, gl_1.ut_last_name, gl_1.ut_title, gl_1.ut_department, gl_1.ut_role_responsibility, gl_1.dr_journal_id, gl_1.dr_business_unit_code, gl_1.dr_flag_dup_any, gl_1.dr_flag_dup_same, gl_1.dr_flag_rev_any, gl_1.dr_flag_revtrue_any, gl_1.dr_flag_dupwithrev_any, gl_1.dr_duplicate_group, gl_1.dr_duplicate_group_count, gl_1.dr_reversal_group, gl_1.dr_reversal_group_count, gl_1.gl_business_unit_and_journal_id, gl_1.gl_business_unit_and_journal_id_computed, gl_1.gl_local_net_pl, gl_1.gl_reporting_net_pl, gl_1.gl_effective_time, gl_1.gl_approved_time, gl_1.gl_last_modified_time, gl_1.gl_effective_date_dow, gl_1.gl_entry_date_dow, gl_1.gl_entry_time_hour, gl_1.gl_effective_time_hour, gl_1.backdated_items_value, gl_1.meets_create_and_approve, gl_1.rank_reporting_byjournal, gl_1.rank_reporting_byjournal_amt, gl_1.rank_local_byjournal, gl_1.rank_local_byjournal_amt, gl_1.rank_reporting_byjournal_bygl_amt, gl_1.rank_reporting_byjournal_bygl, gl_1.rank_local_byjournal_bygl_amt, gl_1.rank_local_byjournal_bygl, gl_1.rank_reporting_byjournal_byfsli_amt, gl_1.rank_reporting_byjournal_byfsli, gl_1.rank_local_byjournal_byfsli_amt, gl_1.rank_local_byjournal_byfsli, gl_1.rank_reporting_line_amt, gl_1.rank_reporting_line, gl_1.rank_local_line_amt, gl_1.rank_local_line, gl_1.rank_reporting_byjournal_pl_amt, gl_1.rank_reporting_byjournal_pl, gl_1.rank_local_byjournal_pl_amt, gl_1.rank_local_byjournal_pl
  • Recheck Cond: (gl_1.ca_fs_caption = 'Cash'::text)
  • Heap Blocks: exact=96,314
  • Buffers: shared hit=291,731
  • Worker 0: actual time=51.401..488.654 rows=338,812 loops=1
  • Buffers: shared hit=97,244
  • Worker 1: actual time=51.759..488.974 rows=338,812 loops=1
  • Buffers: shared hit=97,244
12. 33.090 33.090 ↓ 1.0 338,812 3 / 3

Bitmap Index Scan on test_mega_gl_ca_fs_caption_106189 (cost=0.00..4,339.96 rows=335,204 width=0) (actual time=33.090..33.090 rows=338,812 loops=3)

  • Index Cond: (gl_1.ca_fs_caption = 'Cash'::text)
  • Buffers: shared hit=2,789
  • Worker 0: actual time=33.684..33.684 rows=338,812 loops=1
  • Buffers: shared hit=930
  • Worker 1: actual time=33.773..33.773 rows=338,812 loops=1
  • Buffers: shared hit=930
Planning time : 4.045 ms
Execution time : 3,911.791 ms