explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WU2L

Settings
# exclusive inclusive rows x rows loops node
1. 9.638 26,032.861 ↑ 1.0 5,000 1

Limit (cost=0.00..2,327,613,308.77 rows=5,000 width=14) (actual time=0.014..26,032.861 rows=5,000 loops=1)

2. 48.279 26,023.223 ↑ 75.2 5,000 1

Seq Scan on uat_device (cost=0.00..174,931,778,220.74 rows=375,775 width=14) (actual time=0.012..26,023.223 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.388 6,913.059 ↑ 1.0 1 3,847

Aggregate (cost=223,817.36..223,817.37 rows=1 width=8) (actual time=1.796..1.797 rows=1 loops=3,847)

5. 22.690 6,897.671 ↓ 0.0 0 3,847

Nested Loop (cost=12,641.44..223,496.21 rows=128,460 width=8) (actual time=1.519..1.793 rows=0 loops=3,847)

6. 23.082 3,858.541 ↑ 1.0 1 3,847

HashAggregate (cost=12,640.86..12,640.87 rows=1 width=7) (actual time=1.002..1.003 rows=1 loops=3,847)

  • Group Key: internal.lgr_ticket
7. 11.541 3,835.459 ↑ 1.0 1 3,847

Limit (cost=0.57..12,640.85 rows=1 width=7) (actual time=0.996..0.997 rows=1 loops=3,847)

8. 3,823.918 3,823.918 ↑ 1.0 1 3,847

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.994..0.994 rows=1 loops=3,847)

  • 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. 3,016.440 3,016.440 ↓ 0.0 0 3,420

Index Scan using idx_lgr_ticket on uat_log_revalidation external (cost=0.57..210,846.14 rows=919 width=15) (actual time=0.574..0.882 rows=0 loops=3,420)

  • Index Cond: (lgr_ticket = internal.lgr_ticket)
  • Filter: (lgr_action_ticket > '0'::numeric)
  • Rows Removed by Filter: 45
10. 26.929 19,061.885 ↑ 1.0 1 3,847

Aggregate (cost=241,705.17..241,705.18 rows=1 width=8) (actual time=4.954..4.955 rows=1 loops=3,847)

11. 52.340 19,034.956 ↑ 32,115.0 4 3,847

Nested Loop (cost=30,529.26..241,384.02 rows=128,460 width=8) (actual time=4.450..4.948 rows=4 loops=3,847)

12. 19.235 11,541.000 ↑ 1.0 1 3,847

HashAggregate (cost=30,528.68..30,528.69 rows=1 width=7) (actual time=2.999..3.000 rows=1 loops=3,847)

  • Group Key: internal_1.lgr_ticket
13. 15.388 11,521.765 ↑ 1.0 1 3,847

Limit (cost=0.57..30,528.67 rows=1 width=7) (actual time=2.994..2.995 rows=1 loops=3,847)

14. 11,506.377 11,506.377 ↑ 2.0 1 3,847

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=2.991..2.991 rows=1 loops=3,847)

  • 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
15. 7,441.616 7,441.616 ↑ 229.8 4 3,458

Index Scan using idx_lgr_ticket on uat_log_revalidation external_1 (cost=0.57..210,846.14 rows=919 width=15) (actual time=1.607..2.152 rows=4 loops=3,458)

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