explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HCBe

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 146,010.531 ↑ 1.0 1 1

Limit (cost=11,680,895.45..11,680,895.48 rows=1 width=40) (actual time=146,010.530..146,010.531 rows=1 loops=1)

2. 0.004 146,010.529 ↑ 1.0 1 1

Subquery Scan on snsTbl__0002 (cost=11,680,895.45..11,680,895.48 rows=1 width=40) (actual time=146,010.529..146,010.529 rows=1 loops=1)

3. 0.008 146,010.525 ↑ 1.0 1 1

Aggregate (cost=11,680,895.45..11,680,895.46 rows=1 width=64) (actual time=146,010.525..146,010.525 rows=1 loops=1)

4. 0.003 146,010.517 ↑ 1.0 2 1

Append (cost=5,840,441.23..11,680,895.41 rows=2 width=16) (actual time=56,130.514..146,010.517 rows=2 loops=1)

5. 0.002 56,130.514 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=5,840,441.23..5,840,441.26 rows=1 width=16) (actual time=56,130.513..56,130.514 rows=1 loops=1)

6. 0.001 56,130.512 ↑ 1.0 1 1

Subquery Scan on snsTbl__0002_1 (cost=5,840,441.23..5,840,441.25 rows=1 width=12) (actual time=56,130.512..56,130.512 rows=1 loops=1)

7. 0.000 56,130.511 ↑ 1.0 1 1

Aggregate (cost=5,840,441.23..5,840,441.24 rows=1 width=8) (actual time=56,130.510..56,130.511 rows=1 loops=1)

  • Filter: (count(DISTINCT interaction.id) IS NOT NULL)
8. 13.614 56,133.620 ↓ 0.0 0 1

Gather (cost=551,496.00..5,840,415.48 rows=5,150 width=8) (actual time=56,130.495..56,133.620 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 52,266.727 56,120.006 ↓ 0.0 0 3 / 3

Parallel Bitmap Heap Scan on interaction (cost=550,496.00..5,838,900.48 rows=2,146 width=8) (actual time=56,120.006..56,120.006 rows=0 loops=3)

  • Recheck Cond: ((((account_id = 1) AND (kb_id = 0)) OR ((account_id = 1) AND (kb_id = 1)) OR ((account_id = 1) AND (kb_id = 2)) OR ((account_id = 1) AND (kb_id = 3))) AND (start_date_time >= '2020-07-13 00:00:00'::timestamp without time zone))
  • Rows Removed by Index Recheck: 2,136,133
  • Filter: ((metrics ->> 'kbName'::text) = 'English'::text)
  • Rows Removed by Filter: 475,953
  • Heap Blocks: exact=11,475 lossy=463,954
10. 27.654 3,853.279 ↓ 0.0 0 1 / 3

BitmapAnd (cost=550,496.00..550,496.00 rows=1,428,495 width=0) (actual time=11,559.838..11,559.838 rows=0 loops=1)

11. 0.002 2,534.439 ↓ 0.0 0 1 / 3

BitmapOr (cost=228,491.07..228,491.07 rows=8,994,366 width=0) (actual time=7,603.317..7,603.317 rows=0 loops=1)

12. 60.882 60.882 ↓ 1.0 1,311,653 1 / 3

Bitmap Index Scan on interaction_accountid_kbid_index (cost=0.00..32,324.86 rows=1,272,429 width=0) (actual time=182.646..182.646 rows=1,311,653 loops=1)

  • Index Cond: ((account_id = 1) AND (kb_id = 0))
13. 67.950 67.950 ↓ 1.0 2,622,319 1 / 3

Bitmap Index Scan on interaction_accountid_kbid_index (cost=0.00..64,412.64 rows=2,535,607 width=0) (actual time=203.850..203.850 rows=2,622,319 loops=1)

  • Index Cond: ((account_id = 1) AND (kb_id = 1))
14. 41.754 41.754 ↓ 1.0 2,622,645 1 / 3

Bitmap Index Scan on interaction_accountid_kbid_index (cost=0.00..65,474.62 rows=2,577,405 width=0) (actual time=125.263..125.263 rows=2,622,645 loops=1)

  • Index Cond: ((account_id = 1) AND (kb_id = 2))
15. 2,363.851 2,363.851 ↓ 1.0 2,624,405 1 / 3

Bitmap Index Scan on interaction_accountid_kbid_index (cost=0.00..66,273.81 rows=2,608,924 width=0) (actual time=7,091.552..7,091.552 rows=2,624,405 loops=1)

  • Index Cond: ((account_id = 1) AND (kb_id = 3))
16. 1,291.186 1,291.186 ↑ 1.0 13,774,578 1 / 3

Bitmap Index Scan on interaction_startdatetime_enddatetime_index (cost=0.00..322,003.39 rows=14,060,110 width=0) (actual time=3,873.558..3,873.558 rows=13,774,578 loops=1)

  • Index Cond: (start_date_time >= '2020-07-13 00:00:00'::timestamp without time zone)
17. 0.001 89,880.000 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=5,840,454.11..5,840,454.14 rows=1 width=16) (actual time=89,879.999..89,880.000 rows=1 loops=1)

18. 0.002 89,879.999 ↑ 1.0 1 1

Subquery Scan on snsTbl__0001 (cost=5,840,454.11..5,840,454.13 rows=1 width=12) (actual time=89,879.998..89,879.999 rows=1 loops=1)

19. 0.000 89,879.997 ↑ 1.0 1 1

Aggregate (cost=5,840,454.11..5,840,454.12 rows=1 width=8) (actual time=89,879.997..89,879.997 rows=1 loops=1)

  • Filter: ((count(DISTINCT interaction_1.id) IS NOT NULL) AND (count(DISTINCT interaction_1.id) IS NOT NULL))
20. 3.570 89,882.864 ↓ 0.0 0 1

Gather (cost=551,496.00..5,840,415.48 rows=5,150 width=8) (actual time=89,879.982..89,882.864 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
21. 34,586.693 89,879.294 ↓ 0.0 0 1

Parallel Bitmap Heap Scan on interaction interaction_1 (cost=550,496.00..5,838,900.48 rows=2,146 width=8) (actual time=89,879.294..89,879.294 rows=0 loops=1)

  • Recheck Cond: ((((account_id = 1) AND (kb_id = 0)) OR ((account_id = 1) AND (kb_id = 1)) OR ((account_id = 1) AND (kb_id = 2)) OR ((account_id = 1) AND (kb_id = 3))) AND (start_date_time >= '2020-07-13 00:00:00'::timestamp without time zone))
  • Rows Removed by Index Recheck: 6,408,399
  • Filter: ((metrics ->> 'kbName'::text) = 'English'::text)
  • Rows Removed by Filter: 1,427,860
  • Heap Blocks: exact=32,600 lossy=1,300,588
22. 91.890 55,292.601 ↓ 0.0 0 1

BitmapAnd (cost=550,496.00..550,496.00 rows=1,428,495 width=0) (actual time=55,292.601..55,292.601 rows=0 loops=1)

23. 0.003 51,351.791 ↓ 0.0 0 1

BitmapOr (cost=228,491.07..228,491.07 rows=8,994,366 width=0) (actual time=51,351.791..51,351.791 rows=0 loops=1)

24. 260.903 260.903 ↓ 1.0 1,311,653 1

Bitmap Index Scan on interaction_accountid_kbid_index (cost=0.00..32,324.86 rows=1,272,429 width=0) (actual time=260.903..260.903 rows=1,311,653 loops=1)

  • Index Cond: ((account_id = 1) AND (kb_id = 0))
25. 7,725.051 7,725.051 ↓ 1.0 2,622,319 1

Bitmap Index Scan on interaction_accountid_kbid_index (cost=0.00..64,412.64 rows=2,535,607 width=0) (actual time=7,725.051..7,725.051 rows=2,622,319 loops=1)

  • Index Cond: ((account_id = 1) AND (kb_id = 1))
26. 30,304.312 30,304.312 ↓ 1.0 2,622,645 1

Bitmap Index Scan on interaction_accountid_kbid_index (cost=0.00..65,474.62 rows=2,577,405 width=0) (actual time=30,304.312..30,304.312 rows=2,622,645 loops=1)

  • Index Cond: ((account_id = 1) AND (kb_id = 2))
27. 13,061.522 13,061.522 ↓ 1.0 2,624,405 1

Bitmap Index Scan on interaction_accountid_kbid_index (cost=0.00..66,273.81 rows=2,608,924 width=0) (actual time=13,061.522..13,061.522 rows=2,624,405 loops=1)

  • Index Cond: ((account_id = 1) AND (kb_id = 3))
28. 3,848.920 3,848.920 ↑ 1.0 13,774,578 1

Bitmap Index Scan on interaction_startdatetime_enddatetime_index (cost=0.00..322,003.39 rows=14,060,110 width=0) (actual time=3,848.920..3,848.920 rows=13,774,578 loops=1)

  • Index Cond: (start_date_time >= '2020-07-13 00:00:00'::timestamp without time zone)