explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7YkL

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 2,596.575 ↓ 0.0 0 1

Limit (cost=218,170.44..218,171.69 rows=500 width=138) (actual time=2,596.575..2,596.575 rows=0 loops=1)

2. 0.006 2,596.574 ↓ 0.0 0 1

Sort (cost=218,170.44..218,801.29 rows=252,342 width=138) (actual time=2,596.574..2,596.574 rows=0 loops=1)

  • Sort Key: "*SELECT* 1".created_at
  • Sort Method: quicksort Memory: 25kB
3. 19.336 2,596.568 ↓ 0.0 0 1

Hash Join (cost=50,699.84..205,596.51 rows=252,342 width=138) (actual time=2,596.568..2,596.568 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1".campaign_id = cam.id)
4. 199.435 2,575.569 ↑ 2.5 132,925 1

Hash Join (cost=48,175.90..200,949.17 rows=326,836 width=117) (actual time=326.639..2,575.569 rows=132,925 loops=1)

  • Hash Cond: ("*SELECT* 1".user_id = u.id)
5. 72.272 2,049.859 ↑ 1.0 773,112 1

Append (cost=0.42..150,738.78 rows=775,202 width=64) (actual time=0.024..2,049.859 rows=773,112 loops=1)

6. 104.114 1,903.484 ↑ 1.0 771,324 1

Subquery Scan on *SELECT* 1 (cost=0.42..141,454.47 rows=772,733 width=64) (actual time=0.023..1,903.484 rows=771,324 loops=1)

  • Filter: (NOT "*SELECT* 1".is_iap_processed)
  • Rows Removed by Filter: 107357
7. 735.197 1,799.370 ↓ 1.0 878,681 1

WindowAgg (cost=0.42..132,668.75 rows=878,572 width=65) (actual time=0.022..1,799.370 rows=878,681 loops=1)

8. 1,064.173 1,064.173 ↓ 1.0 878,681 1

Index Scan using user_reward_user_id_campaign_id_created_at_idx on user_reward (cost=0.42..115,097.31 rows=878,572 width=57) (actual time=0.013..1,064.173 rows=878,681 loops=1)

  • Filter: (status = 'active'::tr.status_enum)
  • Rows Removed by Filter: 555
9. 2.706 73.996 ↑ 1.4 1,773 1

Subquery Scan on *SELECT* 2 (cost=0.41..5,405.92 rows=2,453 width=64) (actual time=0.154..73.996 rows=1,773 loops=1)

  • Filter: (NOT "*SELECT* 2".is_iap_processed)
  • Rows Removed by Filter: 45316
10. 36.731 71.290 ↓ 1.0 47,089 1

WindowAgg (cost=0.41..4,935.17 rows=47,075 width=65) (actual time=0.059..71.290 rows=47,089 loops=1)

11. 34.559 34.559 ↓ 1.0 47,089 1

Index Scan using user_loto_user_id_campaign_id_created_at_idx on user_loto (cost=0.41..3,993.67 rows=47,075 width=57) (actual time=0.043..34.559 rows=47,089 loops=1)

  • Filter: (status = 'active'::tr.status_enum)
  • Rows Removed by Filter: 957
12. 0.006 0.107 ↑ 1.1 15 1

Subquery Scan on *SELECT* 3 (cost=1.72..2.37 rows=16 width=64) (actual time=0.092..0.107 rows=15 loops=1)

  • Filter: (NOT "*SELECT* 3".is_iap_processed)
  • Rows Removed by Filter: 5
13. 0.027 0.101 ↑ 1.0 20 1

WindowAgg (cost=1.72..2.17 rows=20 width=65) (actual time=0.083..0.101 rows=20 loops=1)

14. 0.039 0.074 ↑ 1.0 20 1

Sort (cost=1.72..1.77 rows=20 width=57) (actual time=0.073..0.074 rows=20 loops=1)

  • Sort Key: user_physical_reward.user_id, user_physical_reward.campaign_id, user_physical_reward.created_at
  • Sort Method: quicksort Memory: 27kB
15. 0.035 0.035 ↑ 1.0 20 1

Seq Scan on user_physical_reward (cost=0.00..1.29 rows=20 width=57) (actual time=0.026..0.035 rows=20 loops=1)

  • Filter: (status = 'active'::tr.status_enum)
  • Rows Removed by Filter: 3
16. 47.852 326.275 ↓ 1.0 166,803 1

Hash (cost=46,111.50..46,111.50 rows=165,118 width=85) (actual time=326.274..326.275 rows=166,803 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 22818kB
17. 278.423 278.423 ↓ 1.0 166,803 1

Seq Scan on "user" u (cost=0.00..46,111.50 rows=165,118 width=85) (actual time=0.009..278.423 rows=166,803 loops=1)

  • Filter: ((admost_id IS NOT NULL) AND (btrim(admost_id) <> ''::text))
  • Rows Removed by Filter: 226242
18. 0.173 1.663 ↓ 1.0 760 1

Hash (cost=2,514.54..2,514.54 rows=752 width=65) (actual time=1.663..1.663 rows=760 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 81kB
19. 1.490 1.490 ↓ 1.0 760 1

Index Scan using "PK_campaign" on campaign cam (cost=0.28..2,514.54 rows=752 width=65) (actual time=0.024..1.490 rows=760 loops=1)

  • Filter: ((customer_id <> 'db51c430-205f-4cec-8d5c-dc3b2610a079'::uuid) AND (type <> 'brand_cooperation'::tr.campaign_type_enum))
  • Rows Removed by Filter: 214
Planning time : 0.844 ms
Execution time : 2,596.828 ms