explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Iitc

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 124,549.544 ↑ 9.0 107 1

Append (cost=5,386.79..3,775,883.32 rows=963 width=40) (actual time=7,195.007..124,549.544 rows=107 loops=1)

2. 0.073 7,195.011 ↑ 9.7 66 1

Sort (cost=5,386.79..5,388.39 rows=642 width=40) (actual time=7,195.007..7,195.011 rows=66 loops=1)

  • Sort Key: ev.value
  • Sort Method: quicksort Memory: 32kB
3. 443.755 7,194.938 ↑ 9.7 66 1

HashAggregate (cost=5,347.22..5,356.85 rows=642 width=40) (actual time=7,194.920..7,194.938 rows=66 loops=1)

  • Group Key: ev.value, ev.name, ev.c_elementvalue_id, ev.accounttype
4. 206.248 6,751.183 ↓ 31.7 607,958 1

Hash Join (cost=28.02..5,059.76 rows=19,164 width=40) (actual time=0.623..6,751.183 rows=607,958 loops=1)

  • Hash Cond: (fa.account_id = ev.c_elementvalue_id)
5. 6,544.649 6,544.649 ↓ 31.7 607,958 1

Index Scan using fact_acct_dateacct on fact_acct fa (cost=0.57..4,768.81 rows=19,164 width=15) (actual time=0.325..6,544.649 rows=607,958 loops=1)

  • Index Cond: ((dateacct >= to_timestamp('2020-08-01 00:00:00.0'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (dateacct <= to_timestamp('2020-08-31 23:59:59.0'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Filter: ((ad_client_id = '1000000'::numeric) AND (c_acctschema_id = '1000000'::numeric) AND (ad_org_id = ANY ('{0,1000000,1000038,1000001,1000002,1000003,1000004,1000005,1000010,1000013,1000014,1000015,1000016,1000017,1000018,1000019,1000021,1000022,1000023,1000024,1000025,1000026,1000027,1000028,1000029,1000030,1000031,1000032,1000034,1000035,1000036,1000037,1000050,1000051,1000052,1000020,1000006,1000007,1000008,1000009,1000033}'::numeric[])))
6. 0.120 0.286 ↑ 1.0 642 1

Hash (cost=19.42..19.42 rows=642 width=32) (actual time=0.286..0.286 rows=642 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 50kB
7. 0.166 0.166 ↑ 1.0 642 1

Seq Scan on c_elementvalue ev (cost=0.00..19.42 rows=642 width=32) (actual time=0.015..0.166 rows=642 loops=1)

8. 0.014 117,354.528 ↑ 7.8 41 1

Subquery Scan on *SELECT* 2 (cost=3,770,484.49..3,770,488.51 rows=321 width=40) (actual time=117,354.517..117,354.528 rows=41 loops=1)

9. 0.045 117,354.514 ↑ 7.8 41 1

Sort (cost=3,770,484.49..3,770,485.30 rows=321 width=40) (actual time=117,354.513..117,354.514 rows=41 loops=1)

  • Sort Key: ev_1.value
  • Sort Method: quicksort Memory: 29kB
10. 24.218 117,354.469 ↑ 7.8 41 1

HashAggregate (cost=3,770,464.71..3,770,471.13 rows=321 width=40) (actual time=117,354.446..117,354.469 rows=41 loops=1)

  • Group Key: ev_1.value, ev_1.name, ev_1.c_elementvalue_id, ev_1.accounttype
  • Filter: ((sum(fa_1.amtacctdr) - sum(fa_1.amtacctcr)) <> '0'::numeric)
  • Rows Removed by Filter: 1
11. 3,837.893 117,330.251 ↑ 622.5 17,569 1

Hash Join (cost=703,686.05..3,606,407.37 rows=10,937,156 width=40) (actual time=37,181.281..117,330.251 rows=17,569 loops=1)

  • Hash Cond: (fa_1.account_id = ev_1.c_elementvalue_id)
12. 76,622.088 112,415.745 ↓ 1.6 34,051,895 1

Bitmap Heap Scan on fact_acct fa_1 (cost=698,521.07..3,409,842.17 rows=21,874,311 width=15) (actual time=35,895.622..112,415.745 rows=34,051,895 loops=1)

  • Recheck Cond: ((ad_client_id = '1000000'::numeric) AND (ad_org_id = ANY ('{0,1000000,1000038,1000001,1000002,1000003,1000004,1000005,1000010,1000013,1000014,1000015,1000016,1000017,1000018,1000019,1000021,1000022,1000023,1000024,1000025,1000026,1000027,1000028,1000029,1000030,1000031,1000032,1000034,1000035,1000036,1000037,1000050,1000051,1000052,1000020,1000006,1000007,1000008,1000009,1000033}'::numeric[])) AND (c_acctschema_id = '1000000'::numeric))
  • Rows Removed by Index Recheck: 63
  • Filter: (dateacct < to_timestamp('2020-08-01 00:00:00.0'::text, 'YYYY-MM-DD HH24:MI:SS'::text))
  • Rows Removed by Filter: 803,196
  • Heap Blocks: exact=281,681 lossy=846,462
13. 35,793.657 35,793.657 ↓ 1.6 34,855,091 1

Bitmap Index Scan on fact_acct_account (cost=0.00..693,052.49 rows=21,911,454 width=0) (actual time=35,793.657..35,793.657 rows=34,855,091 loops=1)

  • Index Cond: ((ad_client_id = '1000000'::numeric) AND (ad_org_id = ANY ('{0,1000000,1000038,1000001,1000002,1000003,1000004,1000005,1000010,1000013,1000014,1000015,1000016,1000017,1000018,1000019,1000021,1000022,1000023,1000024,1000025,1000026,1000027,1000028,1000029,1000030,1000031,1000032,1000034,1000035,1000036,1000037,1000050,1000051,1000052,1000020,1000006,1000007,1000008,1000009,1000033}'::numeric[])) AND (c_acctschema_id = '1000000'::numeric))
14. 0.107 1,076.613 ↓ 1.8 568 1

Hash (cost=5,160.96..5,160.96 rows=321 width=32) (actual time=1,076.613..1,076.613 rows=568 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 45kB
15. 103.142 1,076.506 ↓ 1.8 568 1

Index Scan using c_elementvalue_pkey on c_elementvalue ev_1 (cost=5,107.95..5,160.96 rows=321 width=32) (actual time=1,076.207..1,076.506 rows=568 loops=1)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 74
16.          

SubPlan (for Index Scan)

17. 169.542 973.364 ↓ 31.7 607,958 1

Hash Join (cost=28.02..5,059.76 rows=19,164 width=5) (actual time=0.273..973.364 rows=607,958 loops=1)

  • Hash Cond: (fa_2.account_id = ev_2.c_elementvalue_id)
18. 803.621 803.621 ↓ 31.7 607,958 1

Index Scan using fact_acct_dateacct on fact_acct fa_2 (cost=0.57..4,768.81 rows=19,164 width=7) (actual time=0.064..803.621 rows=607,958 loops=1)

  • Index Cond: ((dateacct >= to_timestamp('2020-08-01 00:00:00.0'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (dateacct <= to_timestamp('2020-08-31 23:59:59.0'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Filter: ((ad_client_id = '1000000'::numeric) AND (c_acctschema_id = '1000000'::numeric) AND (ad_org_id = ANY ('{0,1000000,1000038,1000001,1000002,1000003,1000004,1000005,1000010,1000013,1000014,1000015,1000016,1000017,1000018,1000019,1000021,1000022,1000023,1000024,1000025,1000026,1000027,1000028,1000029,1000030,1000031,1000032,1000034,1000035,1000036,1000037,1000050,1000051,1000052,1000020,1000006,1000007,1000008,1000009,1000033}'::numeric[])))
19. 0.093 0.201 ↑ 1.0 642 1

Hash (cost=19.42..19.42 rows=642 width=10) (actual time=0.201..0.201 rows=642 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
20. 0.108 0.108 ↑ 1.0 642 1

Seq Scan on c_elementvalue ev_2 (cost=0.00..19.42 rows=642 width=10) (actual time=0.002..0.108 rows=642 loops=1)