explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7wPh

Settings
# exclusive inclusive rows x rows loops node
1. 0.134 1,708.309 ↑ 1.0 50 1

Limit (cost=46,449.38..46,449.51 rows=50 width=118) (actual time=1,708.169..1,708.309 rows=50 loops=1)

2. 0.576 1,708.175 ↑ 118.3 50 1

Sort (cost=46,449.38..46,464.17 rows=5,915 width=118) (actual time=1,708.167..1,708.175 rows=50 loops=1)

  • Sort Key: (count(DISTINCT a.user_id)), c.licence DESC
  • Sort Method: top-N heapsort Memory: 44kB
3. 40.551 1,707.599 ↑ 7.1 833 1

GroupAggregate (cost=46,119.81..46,252.89 rows=5,915 width=118) (actual time=1,658.728..1,707.599 rows=833 loops=1)

  • Group Key: o.name_ru, c.contract_number, c.licence
4. 126.432 1,667.048 ↓ 8.7 51,196 1

Sort (cost=46,119.81..46,134.59 rows=5,915 width=135) (actual time=1,658.656..1,667.048 rows=51,196 loops=1)

  • Sort Key: o.name_ru, c.contract_number, c.licence DESC
  • Sort Method: quicksort Memory: 13550kB
5. 5.119 1,540.616 ↓ 8.7 51,196 1

Gather (cost=1,114.72..45,749.23 rows=5,915 width=135) (actual time=3.481..1,540.616 rows=51,196 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
6. 17.547 1,535.497 ↓ 7.4 25,598 2

Merge Join (cost=114.72..44,157.73 rows=3,479 width=135) (actual time=2.941..1,535.497 rows=25,598 loops=2)

  • Merge Cond: ((c.db_id)::text = (o.id)::text)
7. 1,468.303 1,514.090 ↓ 4.5 25,598 2

Nested Loop Left Join (cost=114.44..58,901.84 rows=5,748 width=95) (actual time=2.917..1,514.090 rows=25,598 loops=2)

8. 12.086 45.606 ↓ 1.4 8,012 2

Merge Join (cost=114.01..2,248.08 rows=5,748 width=95) (actual time=2.743..45.606 rows=8,012 loops=2)

  • Merge Cond: ((u.db_id)::text = (c.db_id)::text)
9. 27.924 27.924 ↑ 1.2 14,416 2

Parallel Index Scan Backward using users_db_id_index on users u (cost=0.41..2,005.20 rows=17,832 width=50) (actual time=0.055..27.924 rows=14,416 loops=2)

  • Filter: (is_deleted IS NOT TRUE)
  • Rows Removed by Filter: 2497
10. 4.784 5.596 ↓ 7.2 8,428 2

Sort (cost=113.60..116.53 rows=1,174 width=45) (actual time=2.683..5.596 rows=8,428 loops=2)

  • Sort Key: c.db_id
  • Sort Method: quicksort Memory: 184kB
  • Worker 0: Sort Method: quicksort Memory: 184kB
11. 0.812 0.812 ↓ 1.0 1,187 2

Seq Scan on contracts_new c (cost=0.00..53.74 rows=1,174 width=45) (actual time=0.023..0.812 rows=1,187 loops=2)

12. 0.181 0.181 ↓ 2.0 2 16,023

Index Only Scan using auth_tokens_user_id_created_at_index on auth_tokens a (cost=0.43..9.85 rows=1 width=25) (actual time=0.174..0.181 rows=2 loops=16,023)

  • Index Cond: (user_id = (u.id)::text)
  • Filter: (((created_at)::date <= CURRENT_DATE) AND ((created_at)::date >= (date_trunc('month'::text, (CURRENT_DATE)::timestamp with time zone))::date))
  • Rows Removed by Filter: 70
  • Heap Fetches: 1155650
13. 3.860 3.860 ↑ 1.1 4,210 2

Index Scan using organizations_pkey on organizations o (cost=0.28..420.17 rows=4,459 width=115) (actual time=0.020..3.860 rows=4,210 loops=2)

Planning time : 2.299 ms
Execution time : 1,709.826 ms