explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZxG3

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 0.763 ↑ 1.0 1 1

HashAggregate (cost=8.44..460,736.47 rows=1 width=14) (actual time=0.762..0.763 rows=1 loops=1)

  • Group Key: uat_device.dvc_id
2. 0.058 0.058 ↑ 1.0 1 1

Index Scan using unique_hostname_ip_customer_platform on uat_device (cost=0.42..8.44 rows=1 width=14) (actual time=0.057..0.058 rows=1 loops=1)

  • Index Cond: ((dvc_hostname)::text = 'BRWS001UAT'::text)
3.          

SubPlan (for HashAggregate)

4. 0.005 0.227 ↑ 1.0 1 1

Aggregate (cost=221,505.83..221,505.84 rows=1 width=8) (actual time=0.226..0.227 rows=1 loops=1)

5. 0.005 0.222 ↓ 0.0 0 1

Nested Loop (cost=12,516.59..221,187.78 rows=127,217 width=8) (actual time=0.222..0.222 rows=0 loops=1)

6. 0.006 0.065 ↑ 1.0 1 1

HashAggregate (cost=12,516.01..12,516.02 rows=1 width=7) (actual time=0.064..0.065 rows=1 loops=1)

  • Group Key: internal.lgr_ticket
7. 0.004 0.059 ↑ 1.0 1 1

Limit (cost=0.57..12,516.00 rows=1 width=7) (actual time=0.058..0.059 rows=1 loops=1)

8. 0.055 0.055 ↑ 1.0 1 1

Index Scan using idx_lgr_rev_svcid_dvcid on uat_log_revalidation internal (cost=0.57..12,516.00 rows=1 width=7) (actual time=0.055..0.055 rows=1 loops=1)

  • 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: 28
9. 0.152 0.152 ↓ 0.0 0 1

Index Scan using idx_lgr_ticket on uat_log_revalidation external (cost=0.57..208,662.66 rows=910 width=15) (actual time=0.152..0.152 rows=0 loops=1)

  • Index Cond: (lgr_ticket = internal.lgr_ticket)
  • Filter: (lgr_action_ticket IS NOT NULL)
  • Rows Removed by Filter: 172
10. 0.008 0.456 ↑ 1.0 1 1

Aggregate (cost=239,222.17..239,222.18 rows=1 width=8) (actual time=0.455..0.456 rows=1 loops=1)

11. 0.013 0.448 ↑ 31,804.2 4 1

Nested Loop (cost=30,232.94..238,904.13 rows=127,217 width=8) (actual time=0.102..0.448 rows=4 loops=1)

12. 0.006 0.061 ↑ 1.0 1 1

HashAggregate (cost=30,232.36..30,232.37 rows=1 width=7) (actual time=0.060..0.061 rows=1 loops=1)

  • Group Key: internal_1.lgr_ticket
13. 0.005 0.055 ↑ 1.0 1 1

Limit (cost=0.57..30,232.35 rows=1 width=7) (actual time=0.054..0.055 rows=1 loops=1)

14. 0.050 0.050 ↑ 2.0 1 1

Index Scan using idx_lgr_rev_svcid_dvcid on uat_log_revalidation internal_1 (cost=0.57..60,464.12 rows=2 width=7) (actual time=0.050..0.050 rows=1 loops=1)

  • 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: 6
15. 0.374 0.374 ↑ 227.5 4 1

Index Scan using idx_lgr_ticket on uat_log_revalidation external_1 (cost=0.57..208,662.66 rows=910 width=15) (actual time=0.037..0.374 rows=4 loops=1)

  • Index Cond: (lgr_ticket = internal_1.lgr_ticket)
  • Filter: (lgr_action_ticket IS NOT NULL)
  • Rows Removed by Filter: 454