explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8KuP

Settings
# exclusive inclusive rows x rows loops node
1. 51.851 9,881.419 ↓ 203.3 9,553 1

Sort (cost=16,454,214.63..16,454,214.75 rows=47 width=514) (actual time=9,879.213..9,881.419 rows=9,553 loops=1)

  • Sort Key: subscribers.mandant_id, subscribers.external_id
  • Sort Method: external merge Disk: 3,864kB
2. 5.995 9,829.568 ↓ 203.3 9,553 1

Nested Loop (cost=1.28..16,454,213.32 rows=47 width=514) (actual time=1,645.082..9,829.568 rows=9,553 loops=1)

  • Join Filter: (geocodes.subscriber_id = subscribers.id)
3. 598.072 9,803.795 ↓ 206.0 9,889 1

Nested Loop (cost=0.85..16,453,987.45 rows=48 width=406) (actual time=1,645.006..9,803.795 rows=9,889 loops=1)

4. 243.199 7,406.943 ↓ 100.1 899,390 1

Unique (cost=0.43..16,406,927.90 rows=8,983 width=4) (actual time=0.089..7,406.943 rows=899,390 loops=1)

5. 1,757.429 7,163.744 ↓ 199.6 1,798,780 1

Index Scan using index_subscriber_ecr_codes_on_subscriber_id on subscriber_ecr_codes sec1 (cost=0.43..16,406,905.36 rows=9,014 width=4) (actual time=0.087..7,163.744 rows=1,798,780 loops=1)

  • Filter: ((ecr_code IS NOT NULL) AND (updated_at = (SubPlan 1)))
  • Rows Removed by Filter: 8,353
6.          

SubPlan (for Index Scan)

7. 1,802.105 5,406.315 ↑ 1.0 1 1,802,105

Aggregate (cost=9.01..9.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,802,105)

8. 3,604.210 3,604.210 ↑ 1.0 1 1,802,105

Index Scan using index_subscriber_ecr_codes_on_subscriber_id on subscriber_ecr_codes sec2 (cost=0.43..9.01 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,802,105)

  • Index Cond: (subscriber_id = sec1.subscriber_id)
  • Filter: ((notruftyp)::text = (sec1.notruftyp)::text)
  • Rows Removed by Filter: 1
9. 1,798.780 1,798.780 ↓ 0.0 0 899,390

Index Scan using index_geocodes_on_subscriber_id on geocodes (cost=0.42..5.22 rows=1 width=402) (actual time=0.002..0.002 rows=0 loops=899,390)

  • Index Cond: (subscriber_id = sec1.subscriber_id)
  • Filter: (quality = ANY ('{23,31,41,51}'::integer[]))
  • Rows Removed by Filter: 1
10. 19.778 19.778 ↑ 1.0 1 9,889

Index Scan using subscribers_pkey on subscribers (cost=0.42..4.69 rows=1 width=112) (actual time=0.002..0.002 rows=1 loops=9,889)

  • Index Cond: (id = sec1.subscriber_id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
Planning time : 1.545 ms
Execution time : 9,882.896 ms