explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cSOS

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.010 34.023 ↓ 1.4 10 1

Limit (cost=1,088.31..1,192.00 rows=7 width=31) (actual time=29.790..34.023 rows=10 loops=1)

2. 0.035 34.013 ↓ 1.4 10 1

Result (cost=1,088.31..1,192.00 rows=7 width=31) (actual time=29.789..34.013 rows=10 loops=1)

3. 0.093 28.798 ↓ 1.4 10 1

Sort (cost=1,088.31..1,088.32 rows=7 width=23) (actual time=28.793..28.798 rows=10 loops=1)

  • Sort Key: (count(DISTINCT c.id)) DESC
  • Sort Method: top-N heapsort Memory: 26kB
4. 0.597 28.705 ↓ 9.9 69 1

GroupAggregate (cost=1,088.08..1,088.21 rows=7 width=23) (actual time=27.985..28.705 rows=69 loops=1)

  • Group Key: cn.id
5. 1.123 28.108 ↓ 268.3 1,878 1

Sort (cost=1,088.08..1,088.10 rows=7 width=19) (actual time=27.954..28.108 rows=1,878 loops=1)

  • Sort Key: cn.id
  • Sort Method: quicksort Memory: 164kB
6. 1.396 26.985 ↓ 268.3 1,878 1

Nested Loop (cost=0.99..1,087.99 rows=7 width=19) (actual time=3.693..26.985 rows=1,878 loops=1)

7. 0.988 23.710 ↓ 234.9 1,879 1

Nested Loop (cost=0.85..1,086.69 rows=8 width=8) (actual time=3.672..23.710 rows=1,879 loops=1)

8. 10.347 10.347 ↓ 8.7 2,475 1

Index Scan using code_activated_at_index on code c (cost=0.42..405.57 rows=285 width=12) (actual time=0.086..10.347 rows=2,475 loops=1)

  • Index Cond: (activated_at > '2019-06-01 00:00:00'::timestamp without time zone)
  • Filter: ((user_id)::text = '32005703'::text)
  • Rows Removed by Filter: 9496
9. 12.375 12.375 ↑ 1.0 1 2,475

Index Scan using transaction_pkey on transaction t (cost=0.42..2.39 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=2,475)

  • Index Cond: (id = c.transaction_id)
10. 1.879 1.879 ↑ 1.0 1 1,879

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

  • Index Cond: (id = t.country_id)
11.          

SubPlan (forResult)

12. 0.100 5.180 ↑ 1.0 1 10

Aggregate (cost=14.79..14.80 rows=1 width=8) (actual time=0.518..0.518 rows=1 loops=10)

13. 0.200 5.080 ↓ 112.0 112 10

Nested Loop (cost=10.73..14.79 rows=1 width=0) (actual time=0.415..0.508 rows=112 loops=10)

14. 0.110 0.110 ↑ 1.0 1 10

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

  • Index Cond: ((user_id)::text = '32005703'::text)
15. 0.890 4.770 ↓ 112.0 112 10

Bitmap Heap Scan on reseller_click rc (cost=10.58..12.62 rows=1 width=4) (actual time=0.398..0.477 rows=112 loops=10)

  • Recheck Cond: ((country_id = cn.id) AND (reseller_id = r.id))
  • Filter: (created_at > '2019-06-01 00:00:00'::timestamp without time zone)
  • Heap Blocks: exact=424
16. 0.060 3.880 ↓ 0.0 0 10

BitmapAnd (cost=10.58..10.58 rows=2 width=0) (actual time=0.388..0.388 rows=0 loops=10)

17. 0.500 0.500 ↓ 7.0 760 10

Bitmap Index Scan on reseller_click_country_id_index (cost=0.00..2.11 rows=109 width=0) (actual time=0.050..0.050 rows=760 loops=10)

  • Index Cond: (country_id = cn.id)
18. 3.320 3.320 ↓ 9.2 6,027 10

Bitmap Index Scan on reseller_click_reseller_id_index (cost=0.00..8.21 rows=656 width=0) (actual time=0.332..0.332 rows=6,027 loops=10)

  • Index Cond: (reseller_id = r.id)