explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yn9E

Settings
# exclusive inclusive rows x rows loops node
1. 2.727 827,884.380 ↑ 54.1 690 1

Group (cost=0.84..24,371,298.02 rows=37,340 width=1,975) (actual time=7,737.103..827,884.380 rows=690 loops=1)

  • Group Key: users.id
2. 115.619 827,881.653 ↑ 54.1 690 1

Nested Loop (cost=0.84..24,371,204.67 rows=37,340 width=1,975) (actual time=7,737.099..827,881.653 rows=690 loops=1)

3. 733.601 733.601 ↓ 1.2 60,091 1

Index Scan using index_ad_args_on_user_id on ad_args (cost=0.42..9,193.23 rows=50,397 width=8) (actual time=177.344..733.601 rows=60,091 loops=1)

  • Filter: (btag_net_refer IS NOT NULL)
  • Rows Removed by Filter: 117138
4. 1,535.957 827,032.433 ↓ 0.0 0 60,091

Index Scan using users_pkey on users (cost=0.42..483.40 rows=1 width=1,975) (actual time=13.763..13.763 rows=0 loops=60,091)

  • Index Cond: (id = ad_args.user_id)
  • Filter: ((NOT disabled) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 1
5.          

SubPlan (forIndex Scan)

6. 217.018 24,458.686 ↓ 0.0 0 59,222

Nested Loop (cost=19.36..31.40 rows=1 width=0) (actual time=0.413..0.413 rows=0 loops=59,222)

7. 355.332 355.332 ↑ 1.0 1 59,222

Index Scan using index_accounts_on_user_id_and_currency on accounts (cost=0.42..8.44 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=59,222)

  • Index Cond: (user_id = users.id)
8. 62.204 23,886.336 ↓ 0.0 0 62,204

Bitmap Heap Scan on dense_balance_transactions (cost=18.94..22.95 rows=1 width=8) (actual time=0.384..0.384 rows=0 loops=62,204)

  • Recheck Cond: ((account_id = accounts.id) AND (dense_created_at >= '2019-06-06 13:47:06.497573'::timestamp without time zone) AND (dense_created_at < '2019-06-07 13:47:06.497687'::timestamp without time zone))
  • Heap Blocks: exact=688
9. 115.112 23,824.132 ↓ 0.0 0 62,204

BitmapAnd (cost=18.94..18.94 rows=1 width=0) (actual time=0.383..0.383 rows=0 loops=62,204)

10. 3,421.220 3,421.220 ↑ 6.7 20 62,204

Bitmap Index Scan on index_dense_balance_transactions_on_account_id (cost=0.00..5.43 rows=133 width=0) (actual time=0.055..0.055 rows=20 loops=62,204)

  • Index Cond: (account_id = accounts.id)
11. 20,287.800 20,287.800 ↓ 35.1 16,518 26,010

Bitmap Index Scan on index_dense_balance_transactions_on_dense_created_at (cost=0.00..13.14 rows=471 width=0) (actual time=0.780..0.780 rows=16,518 loops=26,010)

  • Index Cond: ((dense_created_at >= '2019-06-06 13:47:06.497573'::timestamp without time zone) AND (dense_created_at < '2019-06-07 13:47:06.497687'::timestamp without time zone))
12. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.85..3,546.91 rows=471 width=8) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Index Scan using index_dense_balance_transactions_on_dense_created_at on dense_balance_transactions dense_balance_transactions_1 (cost=0.43..28.85 rows=471 width=8) (never executed)

  • Index Cond: ((dense_created_at >= '2019-06-06 13:47:06.497573'::timestamp without time zone) AND (dense_created_at < '2019-06-07 13:47:06.497687'::timestamp without time zone))
14. 0.000 0.000 ↓ 0.0 0

Index Scan using accounts_pkey on accounts accounts_1 (cost=0.42..7.47 rows=1 width=16) (never executed)

  • Index Cond: (id = dense_balance_transactions_1.account_id)
15. 256.197 801,037.790 ↓ 0.0 0 58,534

Nested Loop (cost=438.44..450.48 rows=1 width=0) (actual time=13.685..13.685 rows=0 loops=58,534)

16. 1,053.612 1,053.612 ↑ 1.0 1 58,534

Index Scan using index_comp_point_accounts_on_user_id_and_account_type on comp_point_accounts (cost=0.42..8.44 rows=1 width=8) (actual time=0.013..0.018 rows=1 loops=58,534)

  • Index Cond: (user_id = users.id)
17. 62,878.075 799,727.981 ↓ 0.0 0 81,133

Bitmap Heap Scan on comp_point_transactions (cost=438.02..442.04 rows=1 width=8) (actual time=9.857..9.857 rows=0 loops=81,133)

  • Recheck Cond: ((created_at >= '2019-06-06 13:47:06.497573'::timestamp without time zone) AND (created_at < '2019-06-07 13:47:06.497687'::timestamp without time zone) AND (comp_point_account_id = comp_point_accounts.id))
  • Rows Removed by Index Recheck: 2619
  • Heap Blocks: lossy=2192982
18. 98,576.595 736,849.906 ↓ 0.0 0 81,133

BitmapAnd (cost=438.02..438.02 rows=1 width=0) (actual time=9.082..9.082 rows=0 loops=81,133)

19. 540,508.046 540,508.046 ↓ 107.1 1,329,265 81,133

Bitmap Index Scan on index_comp_point_transactions_on_created_at (cost=0.00..193.03 rows=12,413 width=0) (actual time=6.662..6.662 rows=1,329,265 loops=81,133)

  • Index Cond: ((created_at >= '2019-06-06 13:47:06.497573'::timestamp without time zone) AND (created_at < '2019-06-07 13:47:06.497687'::timestamp without time zone))
20. 97,765.265 97,765.265 ↑ 11.5 980 81,133

Bitmap Index Scan on index_comp_point_transactions_on_comp_point_account_id (cost=0.00..244.73 rows=11,222 width=0) (actual time=1.205..1.205 rows=980 loops=81,133)

  • Index Cond: (comp_point_account_id = comp_point_accounts.id)
21. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=193.45..46,749.10 rows=1 width=8) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on comp_point_transactions comp_point_transactions_1 (cost=193.03..46,740.67 rows=1 width=8) (never executed)

  • Recheck Cond: ((created_at >= '2019-06-06 13:47:06.497573'::timestamp without time zone) AND (created_at < '2019-06-07 13:47:06.497687'::timestamp without time zone))
23. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on index_comp_point_transactions_on_created_at (cost=0.00..193.03 rows=12,413 width=0) (never executed)

  • Index Cond: ((created_at >= '2019-06-06 13:47:06.497573'::timestamp without time zone) AND (created_at < '2019-06-07 13:47:06.497687'::timestamp without time zone))
24. 0.000 0.000 ↓ 0.0 0

Index Scan using comp_point_accounts_pkey on comp_point_accounts comp_point_accounts_1 (cost=0.42..8.44 rows=1 width=16) (never executed)

  • Index Cond: (id = comp_point_transactions_1.comp_point_account_id)
Planning time : 10.131 ms
Execution time : 827,890.781 ms