explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qar4

Settings
# exclusive inclusive rows x rows loops node
1. 5.554 256.152 ↑ 1,550.0 1 1

Seq Scan on nq_brkg_group a (cost=0.00..403,422.65 rows=1,550 width=41) (actual time=98.275..256.152 rows=1 loops=1)

  • Filter: ((SubPlan 1) AND (SubPlan 2))
  • Rows Removed by Filter: 6110
2.          

SubPlan (forSeq Scan)

3. 6.111 250.551 ↓ 0.0 0 6,111

Subquery Scan on nqv_group_abv_blw (cost=32.52..32.53 rows=1 width=4) (actual time=0.041..0.041 rows=0 loops=6,111)

4. 18.333 244.440 ↓ 0.0 0 6,111

Sort (cost=32.52..32.52 rows=1 width=8) (actual time=0.040..0.040 rows=0 loops=6,111)

  • Sort Key: g2.group_id, g1.group_id
  • Sort Method: quicksort Memory: 25kB
5. 36.666 226.107 ↓ 0.0 0 6,111

Nested Loop (cost=17.17..32.51 rows=1 width=8) (actual time=0.037..0.037 rows=0 loops=6,111)

  • Join Filter: ((g2.left_index >= g1.left_index) AND (g2.left_index <= g1.right_index) AND (((g1.group_id = a.group_id) AND (g2.group_id = 4853)) OR ((g2.group_id = a.group_id) AND (g1.group_id = 4853))))
  • Rows Removed by Join Filter: 4
6. 30.555 79.443 ↑ 1.0 2 6,111

Bitmap Heap Scan on nq_group g1 (cost=8.59..16.20 rows=2 width=12) (actual time=0.012..0.013 rows=2 loops=6,111)

  • Recheck Cond: ((group_id = a.group_id) OR (group_id = 4853))
  • Heap Blocks: exact=12187
7. 6.111 48.888 ↓ 0.0 0 6,111

BitmapOr (cost=8.59..8.59 rows=2 width=0) (actual time=0.008..0.008 rows=0 loops=6,111)

8. 24.444 24.444 ↑ 1.0 1 6,111

Bitmap Index Scan on nq_group_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=6,111)

  • Index Cond: (group_id = a.group_id)
9. 18.333 18.333 ↑ 1.0 1 6,111

Bitmap Index Scan on nq_group_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=6,111)

  • Index Cond: (group_id = 4853)
10. 30.555 109.998 ↑ 1.0 2 12,222

Materialize (cost=8.59..16.21 rows=2 width=8) (actual time=0.008..0.009 rows=2 loops=12,222)

11. 36.666 79.443 ↑ 1.0 2 6,111

Bitmap Heap Scan on nq_group g2 (cost=8.59..16.20 rows=2 width=8) (actual time=0.011..0.013 rows=2 loops=6,111)

  • Recheck Cond: ((group_id = 4853) OR (group_id = a.group_id))
  • Heap Blocks: exact=12187
12. 6.111 42.777 ↓ 0.0 0 6,111

BitmapOr (cost=8.59..8.59 rows=2 width=0) (actual time=0.007..0.007 rows=0 loops=6,111)

13. 18.333 18.333 ↑ 1.0 1 6,111

Bitmap Index Scan on nq_group_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=6,111)

  • Index Cond: (group_id = 4853)
14. 18.333 18.333 ↑ 1.0 1 6,111

Bitmap Index Scan on nq_group_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=6,111)

  • Index Cond: (group_id = a.group_id)
15. 0.001 0.047 ↑ 1.0 1 1

Subquery Scan on nqv_group_abv_blw_1 (cost=32.52..32.53 rows=1 width=4) (actual time=0.047..0.047 rows=1 loops=1)

16. 0.013 0.046 ↑ 1.0 1 1

Sort (cost=32.52..32.52 rows=1 width=8) (actual time=0.046..0.046 rows=1 loops=1)

  • Sort Key: g2_1.group_id, g1_1.group_id
  • Sort Method: quicksort Memory: 25kB
17. 0.008 0.033 ↑ 1.0 1 1

Nested Loop (cost=17.17..32.51 rows=1 width=8) (actual time=0.032..0.033 rows=1 loops=1)

  • Join Filter: ((g2_1.left_index >= g1_1.left_index) AND (g2_1.left_index <= g1_1.right_index) AND (((g1_1.group_id = a.group_id) AND (g2_1.group_id = 4854)) OR ((g2_1.group_id = a.group_id) AND (g1_1.group_id = 4854))))
18. 0.003 0.011 ↑ 2.0 1 1

Bitmap Heap Scan on nq_group g1_1 (cost=8.59..16.20 rows=2 width=12) (actual time=0.011..0.011 rows=1 loops=1)

  • Recheck Cond: ((group_id = a.group_id) OR (group_id = 4854))
  • Heap Blocks: exact=1
19. 0.001 0.008 ↓ 0.0 0 1

BitmapOr (cost=8.59..8.59 rows=2 width=0) (actual time=0.008..0.008 rows=0 loops=1)

20. 0.005 0.005 ↑ 1.0 1 1

Bitmap Index Scan on nq_group_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (group_id = a.group_id)
21. 0.002 0.002 ↑ 1.0 1 1

Bitmap Index Scan on nq_group_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (group_id = 4854)
22. 0.004 0.014 ↑ 2.0 1 1

Materialize (cost=8.59..16.21 rows=2 width=8) (actual time=0.014..0.014 rows=1 loops=1)

23. 0.003 0.010 ↑ 2.0 1 1

Bitmap Heap Scan on nq_group g2_1 (cost=8.59..16.20 rows=2 width=8) (actual time=0.010..0.010 rows=1 loops=1)

  • Recheck Cond: ((group_id = 4854) OR (group_id = a.group_id))
  • Heap Blocks: exact=1
24. 0.002 0.007 ↓ 0.0 0 1

BitmapOr (cost=8.59..8.59 rows=2 width=0) (actual time=0.006..0.007 rows=0 loops=1)

25. 0.003 0.003 ↑ 1.0 1 1

Bitmap Index Scan on nq_group_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (group_id = 4854)
26. 0.002 0.002 ↑ 1.0 1 1

Bitmap Index Scan on nq_group_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (group_id = a.group_id)
Planning time : 1.617 ms
Execution time : 256.349 ms