explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iydk

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

Limit (cost=218,218.98..218,220.23 rows=500 width=138) (actual time=2,614.257..2,614.257 rows=0 loops=1)

2. 0.005 2,614.256 ↓ 0.0 0 1

Sort (cost=218,218.98..218,853.66 rows=253,874 width=138) (actual time=2,614.256..2,614.256 rows=0 loops=1)

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

Hash Join (cost=50,766.69..205,568.71 rows=253,874 width=138) (actual time=2,614.251..2,614.251 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1".campaign_id = cam.id)
4. 189.953 2,592.769 ↑ 2.5 132,926 1

Hash Join (cost=48,242.74..200,908.48 rows=328,821 width=117) (actual time=331.982..2,592.769 rows=132,926 loops=1)

  • Hash Cond: ("*SELECT* 1".user_id = u.id)
5. 78.798 2,071.222 ↓ 1.0 773,113 1

Append (cost=0.42..150,643.07 rows=770,695 width=64) (actual time=0.034..2,071.222 rows=773,113 loops=1)

6. 105.737 1,916.416 ↓ 1.0 771,325 1

Subquery Scan on *SELECT* 1 (cost=0.42..141,380.29 rows=768,283 width=64) (actual time=0.034..1,916.416 rows=771,325 loops=1)

  • Filter: (NOT "*SELECT* 1".is_iap_processed)
  • Rows Removed by Filter: 107357
7. 736.296 1,810.679 ↓ 1.0 878,682 1

WindowAgg (cost=0.42..132,610.94 rows=876,935 width=65) (actual time=0.032..1,810.679 rows=878,682 loops=1)

8. 1,074.383 1,074.383 ↓ 1.0 878,682 1

Index Scan using user_reward_user_id_campaign_id_created_at_idx on user_reward (cost=0.42..115,072.24 rows=876,935 width=57) (actual time=0.021..1,074.383 rows=878,682 loops=1)

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

Subquery Scan on *SELECT* 2 (cost=0.41..5,406.93 rows=2,396 width=64) (actual time=0.113..75.939 rows=1,773 loops=1)

  • Filter: (NOT "*SELECT* 2".is_iap_processed)
  • Rows Removed by Filter: 45317
10. 37.613 73.144 ↑ 1.0 47,090 1

WindowAgg (cost=0.41..4,935.85 rows=47,108 width=65) (actual time=0.029..73.144 rows=47,090 loops=1)

11. 35.531 35.531 ↑ 1.0 47,090 1

Index Scan using user_loto_user_id_campaign_id_created_at_idx on user_loto (cost=0.41..3,993.69 rows=47,108 width=57) (actual time=0.020..35.531 rows=47,090 loops=1)

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

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

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

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

14. 0.027 0.046 ↑ 1.0 20 1

Sort (cost=1.72..1.77 rows=20 width=57) (actual time=0.045..0.046 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.019 0.019 ↑ 1.0 20 1

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

  • Filter: (status = 'active'::tr.status_enum)
  • Rows Removed by Filter: 3
16. 48.389 331.594 ↑ 1.0 166,803 1

Hash (cost=46,142.60..46,142.60 rows=167,977 width=85) (actual time=331.594..331.594 rows=166,803 loops=1)

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

Seq Scan on "user" u (cost=0.00..46,142.60 rows=167,977 width=85) (actual time=0.011..283.205 rows=166,803 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 81kB
19. 1.554 1.554 ↓ 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.017..1.554 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.848 ms
Execution time : 2,614.482 ms