explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TdGG

Settings
# exclusive inclusive rows x rows loops node
1. 29.158 14,410.723 ↓ 2.2 9,553 1

Sort (cost=16,601,301.95..16,601,313.00 rows=4,420 width=112) (actual time=14,409.610..14,410.723 rows=9,553 loops=1)

  • Sort Key: subscribers.mandant_id, subscribers.external_id
  • Sort Method: quicksort Memory: 2,784kB
2. 630.084 14,381.565 ↓ 2.2 9,553 1

Nested Loop (cost=0.85..16,601,034.32 rows=4,420 width=112) (actual time=2,392.777..14,381.565 rows=9,553 loops=1)

3. 255.740 7,455.751 ↓ 100.1 899,390 1

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

4. 1,793.696 7,200.011 ↓ 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.169..7,200.011 rows=1,798,780 loops=1)

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

SubPlan (for Index Scan)

6. 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)

7. 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
8. 1,867.435 6,295.730 ↓ 0.0 0 899,390

Index Scan using subscribers_pkey on subscribers (cost=0.42..21.60 rows=1 width=112) (actual time=0.007..0.007 rows=0 loops=899,390)

  • Index Cond: (id = sec1.subscriber_id)
  • Filter: ((NOT deleted) AND (SubPlan 2))
  • Rows Removed by Filter: 1
9.          

SubPlan (for Index Scan)

10. 2,656.977 4,428.295 ↑ 1.0 1 885,659

Index Scan using index_geocodes_on_subscriber_id on geocodes geocodes_1 (cost=8.88..16.91 rows=1 width=1) (actual time=0.005..0.005 rows=1 loops=885,659)

  • Index Cond: (subscriber_id = subscribers.id)
  • Filter: (updated_at = $1)
  • Rows Removed by Filter: 0
11.          

Initplan (for Index Scan)

12. 0.000 1,771.318 ↑ 1.0 1 885,659

Aggregate (cost=8.45..8.46 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=885,659)

13. 1,771.318 1,771.318 ↑ 1.0 1 885,659

Index Scan using index_geocodes_on_subscriber_id on geocodes (cost=0.42..8.44 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=885,659)

  • Index Cond: (subscriber_id = subscribers.id)
Planning time : 1.138 ms
Execution time : 14,411.664 ms