explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4j22

Settings
# exclusive inclusive rows x rows loops node
1. 0.189 3.968 ↑ 1.0 100 1

Limit (cost=0.00..46,537,407.93 rows=100 width=14) (actual time=0.017..3.968 rows=100 loops=1)

2. 0.323 3.779 ↑ 3,757.8 100 1

Seq Scan on uat_device (cost=0.00..174,875,944,631.80 rows=375,775 width=14) (actual time=0.014..3.779 rows=100 loops=1)

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

SubPlan (for Seq Scan)

4. 0.096 1.680 ↑ 1.0 1 24

Aggregate (cost=223,668.78..223,668.79 rows=1 width=8) (actual time=0.069..0.070 rows=1 loops=24)

5. 0.150 1.584 ↑ 128,460.0 1 24

Nested Loop (cost=12,641.44..223,347.63 rows=128,460 width=8) (actual time=0.053..0.066 rows=1 loops=24)

6. 0.120 0.744 ↑ 1.0 1 24

HashAggregate (cost=12,640.86..12,640.87 rows=1 width=7) (actual time=0.030..0.031 rows=1 loops=24)

  • Group Key: internal.lgr_ticket
7. 0.072 0.624 ↑ 1.0 1 24

Limit (cost=0.57..12,640.85 rows=1 width=7) (actual time=0.025..0.026 rows=1 loops=24)

8. 0.552 0.552 ↑ 1.0 1 24

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.023..0.023 rows=1 loops=24)

  • 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: 8
9. 0.690 0.690 ↑ 919.0 1 23

Index Scan using idx_lgr_ticket on uat_log_revalidation external (cost=0.57..210,697.56 rows=919 width=15) (actual time=0.018..0.030 rows=1 loops=23)

  • Index Cond: (lgr_ticket = internal.lgr_ticket)
  • Filter: (lgr_action_ticket IS NOT NULL)
  • Rows Removed by Filter: 35
10. 0.096 1.776 ↑ 1.0 1 24

Aggregate (cost=241,705.17..241,705.18 rows=1 width=8) (actual time=0.073..0.074 rows=1 loops=24)

11. 0.128 1.680 ↓ 0.0 0 24

Nested Loop (cost=30,529.26..241,384.02 rows=128,460 width=8) (actual time=0.070..0.070 rows=0 loops=24)

12. 0.120 0.816 ↑ 1.0 1 24

HashAggregate (cost=30,528.68..30,528.69 rows=1 width=7) (actual time=0.033..0.034 rows=1 loops=24)

  • Group Key: internal_1.lgr_ticket
13. 0.072 0.696 ↑ 1.0 1 24

Limit (cost=0.57..30,528.67 rows=1 width=7) (actual time=0.028..0.029 rows=1 loops=24)

14. 0.624 0.624 ↑ 2.0 1 24

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=0.026..0.026 rows=1 loops=24)

  • 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: 14
15. 0.736 0.736 ↓ 0.0 0 23

Index Scan using idx_lgr_ticket on uat_log_revalidation external_1 (cost=0.57..210,846.14 rows=919 width=15) (actual time=0.032..0.032 rows=0 loops=23)

  • Index Cond: (lgr_ticket = internal_1.lgr_ticket)
  • Filter: (lgr_action_ticket > '0'::numeric)
  • Rows Removed by Filter: 58