explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jUK9

Settings
# exclusive inclusive rows x rows loops node
1. 0.161 1,640.304 ↑ 1.0 50 1

Limit (cost=47,656.66..47,656.78 rows=50 width=118) (actual time=1,640.139..1,640.304 rows=50 loops=1)

2. 0.579 1,640.143 ↑ 118.3 50 1

Sort (cost=47,656.66..47,671.44 rows=5,915 width=118) (actual time=1,640.135..1,640.143 rows=50 loops=1)

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

GroupAggregate (cost=47,327.08..47,460.16 rows=5,915 width=118) (actual time=1,591.091..1,639.564 rows=833 loops=1)

  • Group Key: o.name_ru, c.contract_number, c.licence
4. 115.553 1,599.142 ↓ 8.7 51,174 1

Sort (cost=47,327.08..47,341.86 rows=5,915 width=135) (actual time=1,591.058..1,599.142 rows=51,174 loops=1)

  • Sort Key: o.name_ru, c.contract_number, c.licence DESC
  • Sort Method: quicksort Memory: 13545kB
5. 12.645 1,483.589 ↓ 8.7 51,174 1

Gather (cost=1,114.72..46,956.50 rows=5,915 width=135) (actual time=3.455..1,483.589 rows=51,174 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
6. 17.148 1,470.944 ↓ 7.4 25,587 2

Merge Join (cost=114.72..45,365.00 rows=3,479 width=135) (actual time=2.891..1,470.944 rows=25,587 loops=2)

  • Merge Cond: ((c.db_id)::text = (o.id)::text)
7. 1,406.593 1,450.012 ↓ 4.5 25,587 2

Nested Loop Left Join (cost=114.44..60,531.19 rows=5,748 width=95) (actual time=2.870..1,450.012 rows=25,587 loops=2)

8. 11.377 43.246 ↓ 1.4 8,012 2

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

  • Merge Cond: ((u.db_id)::text = (c.db_id)::text)
9. 26.175 26.175 ↑ 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.041..26.175 rows=14,416 loops=2)

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

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

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

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

12. 0.173 0.173 ↓ 2.0 2 16,023

Index Scan using auth_tokens_user_id_index on auth_tokens a (cost=0.43..10.13 rows=1 width=25) (actual time=0.056..0.173 rows=2 loops=16,023)

  • Index Cond: ((u.id)::text = (user_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
13. 3.784 3.784 ↑ 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.017..3.784 rows=4,210 loops=2)

Planning time : 2.439 ms
Execution time : 1,641.968 ms