explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UDmh

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.012 2,586.789 ↓ 10.0 10 1

Limit (cost=761.01..761.01 rows=1 width=31) (actual time=2,586.778..2,586.789 rows=10 loops=1)

2. 0.085 2,586.777 ↓ 10.0 10 1

Sort (cost=761.01..761.01 rows=1 width=31) (actual time=2,586.775..2,586.777 rows=10 loops=1)

  • Sort Key: (count(DISTINCT c.id)) DESC
  • Sort Method: top-N heapsort Memory: 26kB
3. 749.041 2,586.692 ↓ 58.0 58 1

GroupAggregate (cost=760.97..761.00 rows=1 width=31) (actual time=1,765.294..2,586.692 rows=58 loops=1)

  • Group Key: cn.id
4. 320.991 1,837.651 ↓ 366,810.0 366,810 1

Sort (cost=760.97..760.98 rows=1 width=27) (actual time=1,765.253..1,837.651 rows=366,810 loops=1)

  • Sort Key: cn.id
  • Sort Method: external merge Disk: 16104kB
5. 429.746 1,516.660 ↓ 366,810.0 366,810 1

Nested Loop (cost=1.43..760.96 rows=1 width=27) (actual time=0.128..1,516.660 rows=366,810 loops=1)

  • Join Filter: ((cn.id = rc.country_id) AND (r.id = rc.reseller_id))
  • Rows Removed by Join Filter: 2128169
6. 0.740 43.778 ↓ 362.2 1,811 1

Nested Loop (cost=1.14..744.04 rows=5 width=27) (actual time=0.101..43.778 rows=1,811 loops=1)

7. 0.027 0.027 ↑ 1.0 1 1

Index Scan using reseller_user_id_index on reseller r (cost=0.14..2.16 rows=1 width=4) (actual time=0.026..0.027 rows=1 loops=1)

  • Index Cond: ((user_id)::text = '32005703'::text)
8. 3.232 43.011 ↓ 362.2 1,811 1

Nested Loop (cost=0.99..741.83 rows=5 width=23) (actual time=0.065..43.011 rows=1,811 loops=1)

9. 3.594 36.155 ↓ 362.4 1,812 1

Nested Loop (cost=0.85..741.01 rows=5 width=8) (actual time=0.054..36.155 rows=1,812 loops=1)

10. 21.689 21.689 ↓ 9.4 1,812 1

Index Scan using code_activated_at_index on code c (cost=0.42..276.61 rows=193 width=12) (actual time=0.035..21.689 rows=1,812 loops=1)

  • Index Cond: (activated_at > '2019-06-30 00:00:00'::timestamp without time zone)
  • Filter: ((user_id)::text = '32005703'::text)
  • Rows Removed by Filter: 6843
11. 10.872 10.872 ↑ 1.0 1 1,812

Index Scan using transaction_pkey on transaction t (cost=0.42..2.41 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=1,812)

  • Index Cond: (id = c.transaction_id)
12. 3.624 3.624 ↑ 1.0 1 1,812

Index Scan using country_pkey on country cn (cost=0.14..0.16 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=1,812)

  • Index Cond: (id = t.country_id)
13. 1,043.136 1,043.136 ↓ 22.6 1,378 1,811

Index Scan using reseller_click_country_id_index on reseller_click rc (cost=0.29..2.47 rows=61 width=16) (actual time=0.008..0.576 rows=1,378 loops=1,811)

  • Index Cond: (country_id = t.country_id)
  • Filter: (created_at > '2019-06-01 00:00:00'::timestamp without time zone)