explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ijuh

Settings
# exclusive inclusive rows x rows loops node
1. 9.735 9,135.752 ↑ 1.0 5,000 1

Limit (cost=0.00..236,373,776.74 rows=5,000 width=14) (actual time=0.015..9,135.752 rows=5,000 loops=1)

2. 34.073 9,126.017 ↑ 75.2 5,000 1

Seq Scan on uat_device (cost=0.00..17,764,671,191.11 rows=375,775 width=14) (actual time=0.013..9,126.017 rows=5,000 loops=1)

  • Filter: (dvc_role <> ALL ('{1,3,4,5,7}'::numeric[]))
  • Rows Removed by Filter: 53
3.          

SubPlan (for Seq Scan)

4. 15.384 2,411.442 ↑ 1.0 1 3,846

Aggregate (cost=14,693.40..14,693.41 rows=1 width=8) (actual time=0.626..0.627 rows=1 loops=3,846)

5. 31.619 2,396.058 ↓ 0.0 0 3,846

Nested Loop (cost=14,684.66..14,692.72 rows=273 width=8) (actual time=0.623..0.623 rows=0 loops=3,846)

6. 19.230 2,326.830 ↑ 1.0 1 3,846

HashAggregate (cost=12,640.86..12,640.87 rows=1 width=7) (actual time=0.604..0.605 rows=1 loops=3,846)

  • Group Key: internal.lgr_ticket
7. 15.384 2,307.600 ↑ 1.0 1 3,846

Limit (cost=0.57..12,640.85 rows=1 width=7) (actual time=0.599..0.600 rows=1 loops=3,846)

8. 2,292.216 2,292.216 ↑ 1.0 1 3,846

Index Scan using idx_lgr_rev_svcid_dvcid on uat_log_revalidation internal (cost=0.57..12,640.85 rows=1 width=7) (actual time=0.596..0.596 rows=1 loops=3,846)

  • Index Cond: ((lgr_dvcid = uat_device.dvc_id) AND (lgr_rev_svcid = '52'::numeric))
  • Filter: ((uat_device.dvc_cbn = lgr_date) AND ((lgr_action)::text = 'Starting Revalidation'::text))
  • Rows Removed by Filter: 73
9. 6.838 37.609 ↓ 0.0 0 3,419

Bitmap Heap Scan on uat_log_revalidation external (cost=2,043.80..2,051.83 rows=2 width=15) (actual time=0.011..0.011 rows=0 loops=3,419)

  • Recheck Cond: ((lgr_action_ticket = '123213'::numeric) AND (lgr_ticket = internal.lgr_ticket))
10. 6.838 30.771 ↓ 0.0 0 3,419

BitmapAnd (cost=2,043.80..2,043.80 rows=2 width=0) (actual time=0.009..0.009 rows=0 loops=3,419)

11. 23.933 23.933 ↓ 0.0 0 3,419

Bitmap Index Scan on idx_lgr_action_ticket (cost=0.00..634.11 rows=28,471 width=0) (actual time=0.007..0.007 rows=0 loops=3,419)

  • Index Cond: (lgr_action_ticket = '123213'::numeric)
12. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_lgr_ticket (cost=0.00..1,402.32 rows=59,433 width=0) (never executed)

  • Index Cond: (lgr_ticket = internal.lgr_ticket)
13. 11.538 6,680.502 ↑ 1.0 1 3,846

Aggregate (cost=32,581.22..32,581.23 rows=1 width=8) (actual time=1.736..1.737 rows=1 loops=3,846)

14. 31.201 6,668.964 ↓ 0.0 0 3,846

Nested Loop (cost=32,572.48..32,580.54 rows=273 width=8) (actual time=1.734..1.734 rows=0 loops=3,846)

15. 23.076 6,599.736 ↑ 1.0 1 3,846

HashAggregate (cost=30,528.68..30,528.69 rows=1 width=7) (actual time=1.715..1.716 rows=1 loops=3,846)

  • Group Key: internal_1.lgr_ticket
16. 11.538 6,576.660 ↑ 1.0 1 3,846

Limit (cost=0.57..30,528.67 rows=1 width=7) (actual time=1.710..1.710 rows=1 loops=3,846)

17. 6,565.122 6,565.122 ↑ 2.0 1 3,846

Index Scan using idx_lgr_rev_svcid_dvcid on uat_log_revalidation internal_1 (cost=0.57..61,056.76 rows=2 width=7) (actual time=1.707..1.707 rows=1 loops=3,846)

  • Index Cond: ((lgr_dvcid = uat_device.dvc_id) AND (lgr_rev_svcid = '53'::numeric))
  • Filter: ((uat_device.dvc_priv = lgr_date) AND ((lgr_action)::text = 'Starting Revalidation'::text))
  • Rows Removed by Filter: 553
18. 10.371 38.027 ↓ 0.0 0 3,457

Bitmap Heap Scan on uat_log_revalidation external_1 (cost=2,043.80..2,051.83 rows=2 width=15) (actual time=0.011..0.011 rows=0 loops=3,457)

  • Recheck Cond: ((lgr_action_ticket = '1231123'::numeric) AND (lgr_ticket = internal_1.lgr_ticket))
19. 6.914 27.656 ↓ 0.0 0 3,457

BitmapAnd (cost=2,043.80..2,043.80 rows=2 width=0) (actual time=0.008..0.008 rows=0 loops=3,457)

20. 20.742 20.742 ↓ 0.0 0 3,457

Bitmap Index Scan on idx_lgr_action_ticket (cost=0.00..634.11 rows=28,471 width=0) (actual time=0.006..0.006 rows=0 loops=3,457)

  • Index Cond: (lgr_action_ticket = '1231123'::numeric)
21. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_lgr_ticket (cost=0.00..1,402.32 rows=59,433 width=0) (never executed)

  • Index Cond: (lgr_ticket = internal_1.lgr_ticket)