explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gtcl

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

Limit (cost=337,491.07..337,492.32 rows=500 width=138) (actual time=2,976.275..2,976.275 rows=0 loops=1)

2. 0.052 2,976.274 ↓ 0.0 0 1

Sort (cost=337,491.07..338,369.89 rows=351,528 width=138) (actual time=2,976.274..2,976.274 rows=0 loops=1)

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

Hash Join (cost=262,668.23..319,974.81 rows=351,528 width=138) (actual time=2,976.222..2,976.222 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1".campaign_id = cam.id)
4. 203.023 2,954.232 ↑ 3.4 132,900 1

Hash Join (cost=259,984.76..314,334.18 rows=455,122 width=117) (actual time=1,635.805..2,954.232 rows=132,900 loops=1)

  • Hash Cond: ("*SELECT* 1".user_id = u.id)
5. 53.159 2,271.201 ↑ 1.4 773,083 1

Append (cost=207,704.87..259,211.93 rows=1,082,795 width=64) (actual time=1,154.217..2,271.201 rows=773,083 loops=1)

6. 98.124 2,139.249 ↑ 1.4 771,295 1

Subquery Scan on *SELECT* 1 (cost=207,704.87..247,249.92 rows=1,080,371 width=64) (actual time=1,154.205..2,139.249 rows=771,295 loops=1)

  • Filter: (NOT "*SELECT* 1".is_iap_processed)
  • Rows Removed by Filter: 107345
7. 636.496 2,041.125 ↑ 1.4 878,640 1

WindowAgg (cost=207,704.87..235,082.21 rows=1,216,771 width=65) (actual time=1,154.197..2,041.125 rows=878,640 loops=1)

8. 982.915 1,404.629 ↑ 1.4 878,640 1

Sort (cost=207,704.87..210,746.79 rows=1,216,771 width=57) (actual time=1,154.116..1,404.629 rows=878,640 loops=1)

  • Sort Key: user_reward.user_id, user_reward.campaign_id, user_reward.created_at
  • Sort Method: external merge Disk: 57624kB
9. 421.714 421.714 ↑ 1.4 878,640 1

Seq Scan on user_reward (cost=0.00..48,776.76 rows=1,216,771 width=57) (actual time=0.016..421.714 rows=878,640 loops=1)

  • Filter: (status = 'active'::tr.status_enum)
  • Rows Removed by Filter: 555
10. 2.715 78.650 ↑ 1.4 1,773 1

Subquery Scan on *SELECT* 2 (cost=5,053.29..6,545.66 rows=2,414 width=64) (actual time=41.415..78.650 rows=1,773 loops=1)

  • Filter: (NOT "*SELECT* 2".is_iap_processed)
  • Rows Removed by Filter: 45316
11. 31.347 75.935 ↓ 1.0 47,089 1

WindowAgg (cost=5,053.29..6,086.47 rows=45,919 width=65) (actual time=41.356..75.935 rows=47,089 loops=1)

12. 28.737 44.588 ↓ 1.0 47,089 1

Sort (cost=5,053.29..5,168.09 rows=45,919 width=57) (actual time=41.308..44.588 rows=47,089 loops=1)

  • Sort Key: user_loto.user_id, user_loto.campaign_id, user_loto.created_at
  • Sort Method: quicksort Memory: 8158kB
13. 15.851 15.851 ↓ 1.0 47,089 1

Seq Scan on user_loto (cost=0.00..1,497.60 rows=45,919 width=57) (actual time=0.073..15.851 rows=47,089 loops=1)

  • Filter: (status = 'active'::tr.status_enum)
  • Rows Removed by Filter: 957
14. 0.025 0.143 ↓ 1.5 15 1

Subquery Scan on *SELECT* 3 (cost=1.72..2.37 rows=10 width=64) (actual time=0.128..0.143 rows=15 loops=1)

  • Filter: (NOT "*SELECT* 3".is_iap_processed)
  • Rows Removed by Filter: 5
15. 0.053 0.118 ↑ 1.0 20 1

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

16. 0.025 0.065 ↑ 1.0 20 1

Sort (cost=1.72..1.77 rows=20 width=57) (actual time=0.064..0.065 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
17. 0.040 0.040 ↑ 1.0 20 1

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

  • Filter: (status = 'active'::tr.status_enum)
  • Rows Removed by Filter: 3
18. 68.540 480.008 ↑ 1.5 166,792 1

Hash (cost=49,155.90..49,155.90 rows=249,920 width=85) (actual time=480.008..480.008 rows=166,792 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 22816kB
19. 411.468 411.468 ↑ 1.5 166,792 1

Seq Scan on "user" u (cost=0.00..49,155.90 rows=249,920 width=85) (actual time=0.026..411.468 rows=166,792 loops=1)

  • Filter: ((admost_id IS NOT NULL) AND (btrim(admost_id) <> ''::text))
  • Rows Removed by Filter: 226241
20. 0.240 4.113 ↑ 1.1 760 1

Hash (cost=2,673.33..2,673.33 rows=811 width=65) (actual time=4.113..4.113 rows=760 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 81kB
21. 3.873 3.873 ↑ 1.1 760 1

Index Scan using "PK_campaign" on campaign cam (cost=0.28..2,673.33 rows=811 width=65) (actual time=0.049..3.873 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 : 3.322 ms
Execution time : 2,995.882 ms