explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qBIi

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 19,744.605 ↑ 1.0 1 1

Finalize Aggregate (cost=3,654,411.29..3,654,411.30 rows=1 width=8) (actual time=19,744.604..19,744.605 rows=1 loops=1)

2. 50.910 19,787.379 ↓ 1.5 3 1

Gather (cost=3,654,411.07..3,654,411.28 rows=2 width=8) (actual time=19,742.478..19,787.379 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 70.190 19,736.469 ↑ 1.0 1 3

Partial Aggregate (cost=3,653,411.07..3,653,411.08 rows=1 width=8) (actual time=19,736.469..19,736.469 rows=1 loops=3)

4. 2,774.115 19,666.279 ↑ 1,772.8 97,129 3

Parallel Hash Join (cost=160,432.79..3,222,932.87 rows=172,191,282 width=0) (actual time=18,906.276..19,666.279 rows=97,129 loops=3)

  • Hash Cond: (at.input_element_id = ie.id)
5. 5,151.756 16,611.404 ↓ 11.9 2,060,990 3

Parallel Hash Join (cost=141,725.26..689,990.28 rows=172,646 width=24) (actual time=386.931..16,611.404 rows=2,060,990 loops=3)

  • Hash Cond: (at.posting_element_id = pe.id)
6. 4,887.192 11,313.396 ↑ 1.3 4,678,615 3

Parallel Append (cost=79.03..532,973.54 rows=5,855,428 width=32) (actual time=167.439..11,313.396 rows=4,678,615 loops=3)

  • Subplans Removed: 7
7. 6,426.202 6,426.202 ↑ 1.3 4,678,615 3

Parallel Seq Scan on audit_trail_2019_q3 at (cost=0.00..307,891.07 rows=5,855,420 width=32) (actual time=167.436..6,426.202 rows=4,678,615 loops=3)

  • Filter: ((created_date_time >= '2019-07-04'::date) AND (created_date_time < '2019-08-04 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 562082
8. 0.002 0.002 ↓ 0.0 0 1

Parallel Seq Scan on audit_trail_default at_1 (cost=0.00..16.88 rows=2 width=32) (actual time=0.001..0.002 rows=0 loops=1)

  • Filter: ((created_date_time >= '2019-07-04'::date) AND (created_date_time < '2019-08-04 00:00:00'::timestamp without time zone))
9. 33.365 146.252 ↑ 1.3 40,433 3

Parallel Hash (cost=141,013.57..141,013.57 rows=50,613 width=24) (actual time=146.252..146.252 rows=40,433 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 7712kB
10. 112.519 112.887 ↑ 1.3 40,433 3

Parallel Bitmap Heap Scan on posting_element pe (cost=59.26..141,013.57 rows=50,613 width=24) (actual time=7.403..112.887 rows=40,433 loops=3)

  • Recheck Cond: ((created_date_time >= '2019-07-04'::date) AND (created_date_time < '2019-08-04 00:00:00'::timestamp without time zone) AND (legal_entity_id = 2435))
  • Rows Removed by Index Recheck: 43290
  • Heap Blocks: lossy=3578
11. 0.368 0.368 ↑ 1.7 72,960 1

Bitmap Index Scan on idx_posting_element_created_date_time_legal_entity_id (cost=0.00..28.90 rows=125,454 width=0) (actual time=0.368..0.368 rows=72,960 loops=1)

  • Index Cond: ((created_date_time >= '2019-07-04'::date) AND (created_date_time < '2019-08-04 00:00:00'::timestamp without time zone) AND (legal_entity_id = 2435))
12. 69.250 280.760 ↑ 1.1 76,514 3

Parallel Hash (cost=17,180.55..17,180.55 rows=83,118 width=24) (actual time=280.760..280.760 rows=76,514 loops=3)

  • Buckets: 65536 Batches: 4 Memory Usage: 3712kB
13. 68.726 211.510 ↑ 1.1 76,514 3

Parallel Append (cost=0.43..17,180.55 rows=83,118 width=24) (actual time=0.030..211.510 rows=76,514 loops=3)

  • Subplans Removed: 7
14. 142.784 142.784 ↑ 1.1 76,514 3

Parallel Index Scan using input_element_ie_2019_q3_purchase_date_idx on input_element_ie_2019_q3 ie (cost=0.43..16,706.08 rows=83,111 width=24) (actual time=0.025..142.784 rows=76,514 loops=3)

  • Index Cond: ((purchase_date >= '2019-07-04'::date) AND (purchase_date <= '2019-07-04'::date))
  • Filter: ((created_date_time >= '2019-07-04'::date) AND (created_date_time < '2019-08-04 00:00:00'::timestamp without time zone) AND (legal_entity_id = 2435))
Planning time : 3.019 ms
Execution time : 19,788.078 ms