explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YIvt

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 92.211 ↑ 2.0 26 1

Append (cost=58.56..45,953.68 rows=51 width=79) (actual time=0.034..92.211 rows=26 loops=1)

2. 0.001 0.034 ↑ 25.0 1 1

Subquery Scan on *SELECT* 1 (cost=58.56..58.88 rows=25 width=104) (actual time=0.033..0.034 rows=1 loops=1)

3. 0.001 0.033 ↑ 25.0 1 1

Limit (cost=58.56..58.63 rows=25 width=131) (actual time=0.033..0.033 rows=1 loops=1)

4. 0.008 0.032 ↑ 320.0 1 1

Sort (cost=58.56..59.36 rows=320 width=131) (actual time=0.032..0.032 rows=1 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.004 0.024 ↑ 320.0 1 1

HashAggregate (cost=44.73..49.53 rows=320 width=131) (actual time=0.024..0.024 rows=1 loops=1)

  • Group Key: e.userid, (e.data ->> 'firstname'::text), (e.data ->> 'lastname'::text), e.type
6. 0.020 0.020 ↑ 960.0 1 1

Index Scan using idx_event_type_leaderboard_types on event e (cost=0.12..32.73 rows=960 width=91) (actual time=0.020..0.020 rows=1 loops=1)

  • Index Cond: (type = 'appointment.booked'::text)
  • Filter: (createdat >= (now() - '30 days'::interval))
7. 0.000 0.007 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=1.19..1.21 rows=1 width=104) (actual time=0.007..0.007 rows=0 loops=1)

8. 0.000 0.007 ↓ 0.0 0 1

Limit (cost=1.19..1.20 rows=1 width=131) (actual time=0.007..0.007 rows=0 loops=1)

9. 0.004 0.007 ↓ 0.0 0 1

Sort (cost=1.19..1.20 rows=1 width=131) (actual time=0.007..0.007 rows=0 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 25kB
10. 0.000 0.003 ↓ 0.0 0 1

HashAggregate (cost=1.17..1.18 rows=1 width=131) (actual time=0.003..0.003 rows=0 loops=1)

  • Group Key: e_1.userid, (e_1.data ->> 'firstname'::text), (e_1.data ->> 'lastname'::text), e_1.type
11. 0.003 0.003 ↓ 0.0 0 1

Index Scan using idx_event_type_leaderboard_types on event e_1 (cost=0.12..1.16 rows=1 width=91) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (type = 'sales.closed'::text)
  • Filter: (createdat >= (now() - '30 days'::interval))
12. 0.003 92.164 ↑ 1.0 25 1

Subquery Scan on *SELECT* 3 (cost=45,893.28..45,893.59 rows=25 width=53) (actual time=92.158..92.164 rows=25 loops=1)

13. 0.004 92.161 ↑ 1.0 25 1

Limit (cost=45,893.28..45,893.34 rows=25 width=74) (actual time=92.158..92.161 rows=25 loops=1)

14. 0.015 92.157 ↑ 8,151.2 25 1

Sort (cost=45,893.28..46,402.73 rows=203,779 width=74) (actual time=92.157..92.157 rows=25 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 29kB
15. 7.003 92.142 ↑ 5,822.3 35 1

GroupAggregate (cost=35,048.31..40,142.78 rows=203,779 width=74) (actual time=82.475..92.142 rows=35 loops=1)

  • Group Key: cu.userid, u.firstname, u.lastname, 'leads'::text
16. 14.915 85.139 ↑ 4.5 45,629 1

Sort (cost=35,048.31..35,557.76 rows=203,779 width=53) (actual time=82.439..85.139 rows=45,629 loops=1)

  • Sort Key: cu.userid, u.firstname, u.lastname
  • Sort Method: quicksort Memory: 5101kB
17. 15.252 70.224 ↑ 4.5 45,629 1

Hash Join (cost=3,783.90..13,096.42 rows=203,779 width=53) (actual time=33.092..70.224 rows=45,629 loops=1)

  • Hash Cond: (cu.userid = u.id)
18. 22.034 22.034 ↑ 1.2 174,144 1

Index Only Scan using idx_creditusage_createdat_userid on creditusage cu (cost=0.56..6,511.04 rows=203,799 width=8) (actual time=0.039..22.034 rows=174,144 loops=1)

  • Index Cond: (createdat >= (now() - '30 days'::interval))
  • Heap Fetches: 471
19. 4.272 32.938 ↑ 1.0 30,611 1

Hash (cost=3,399.19..3,399.19 rows=30,732 width=21) (actual time=32.938..32.938 rows=30,611 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1458kB
20. 28.666 28.666 ↑ 1.0 30,611 1

Seq Scan on "user" u (cost=0.00..3,399.19 rows=30,732 width=21) (actual time=0.014..28.666 rows=30,611 loops=1)

  • Filter: (username !~* 'tam@seamlesscontacts.com|tam-|tam+'::text)
  • Rows Removed by Filter: 124
Planning time : 1.572 ms
Execution time : 92.823 ms