explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bsc

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 26,813.582 ↑ 21.3 44,971 1

HashAggregate (cost=2,746,204.19..2,755,766.40 rows=956,221 width=92) (actual time=26,783.719..26,813.582 rows=44,971 loops=1)

  • Output: (date(date_trunc('month'::text, d.data))), i.id, cc.id, sp.id, s.id, (simplify_duration(((d.solutie).data - date(d.data)))), (simplify_first_term_duration(days_to_first_term(d.id))), ((SubPlan 1)), su
  • Group Key: date(date_trunc('month'::text, d.data)), i.id, cc.id, sp.id, s.id, simplify_duration(((d.solutie).data - date(d.data))), simplify_first_term_duration(days_to_first_term(d.id)), (SubPlan 1)
2. 26,214.537 26,214.625 ↑ 5.9 163,213 1

Merge Right Join (cost=165,573.05..2,707,955.35 rows=956,221 width=76) (actual time=1,624.836..26,214.625 rows=163,213 loops=1)

  • Output: date(date_trunc('month'::text, d.data)), i.id, cc.id, sp.id, s.id, simplify_duration(((d.solutie).data - date(d.data))), simplify_first_term_duration(days_to_first_term(d.id)), (SubPlan 1), 1, (
  • Merge Cond: (s.nume = (COALESCE((d.solutie).nume, ''::text)))
3. 0.088 0.088 ↑ 74.7 17 1

Sort (cost=88.17..91.35 rows=1,270 width=36) (actual time=0.076..0.088 rows=17 loops=1)

4. 21,202.944 26,214.625 ↑ 5.9 163,213 1

Merge Right Join (cost=165,573.05..2,707,955.35 rows=956,221 width=76) (actual time=1,624.836..26,214.625 rows=163,213 loops=1)

  • Output: date(date_trunc('month'::text, d.data)), i.id, cc.id, sp.id, s.id, simplify_duration(((d.solutie).data - date(d.data))), simplify_first_term_duration(days_to_first_term(d.id)), (SubPlan 1), 1, (
  • Merge Cond: (s.nume = (COALESCE((d.solutie).nume, ''::text)))
5. 0.063 0.088 ↑ 74.7 17 1

Sort (cost=88.17..91.35 rows=1,270 width=36) (actual time=0.076..0.088 rows=17 loops=1)

  • Output: s.id, s.nume
  • Sort Key: s.nume
  • Sort Method: quicksort Memory: 25kB
6. 0.025 0.025 ↑ 74.7 17 1

Seq Scan on analytics.solutii s (cost=0.00..22.70 rows=1,270 width=36) (actual time=0.018..0.025 rows=17 loops=1)

  • Output: s.id, s.nume
7. 295.914 1,747.333 ↓ 1.1 163,213 1

Sort (cost=165,484.88..165,861.34 rows=150,586 width=70) (actual time=1,624.429..1,747.333 rows=163,213 loops=1)

  • Output: d.data, d.solutie, d.id, d.id_obiect_rolii, i.id, cc.id, sp.id, (COALESCE((d.solutie).nume, ''::text))
  • Sort Key: (COALESCE((d.solutie).nume, ''::text))
  • Sort Method: quicksort Memory: 27739kB
8. 798.311 1,451.419 ↓ 1.1 163,213 1

Hash Left Join (cost=62.44..152,534.31 rows=150,586 width=70) (actual time=0.446..1,451.419 rows=163,213 loops=1)

  • Output: d.data, d.solutie, d.id, d.id_obiect_rolii, i.id, cc.id, sp.id, COALESCE((d.solutie).nume, ''::text)
  • Hash Cond: (lower(unaccent((d.stadiuprocesual)::text)) = sp.simple_id)
9. 114.694 653.071 ↓ 1.1 163,213 1

Hash Left Join (cost=38.27..150,063.11 rows=150,586 width=71) (actual time=0.368..653.071 rows=163,213 loops=1)

  • Output: d.data, d.solutie, d.id, d.id_obiect_rolii, d.stadiuprocesual, i.id, cc.id
  • Hash Cond: ((d.categoriecaz)::text = cc.text_id)
10. 149.142 538.354 ↓ 1.1 163,213 1

Hash Left Join (cost=9.14..147,963.43 rows=150,586 width=78) (actual time=0.336..538.354 rows=163,213 loops=1)

  • Output: d.data, d.solutie, d.id, d.id_obiect_rolii, d.categoriecaz, d.stadiuprocesual, i.id
  • Hash Cond: ((d.institutie)::text = i.ecris_name)
11. 388.917 388.917 ↓ 1.1 163,213 1

Index Scan using fki_data_a on public.dosar d (cost=0.56..145,884.29 rows=150,586 width=94) (actual time=0.024..388.917 rows=163,213 loops=1)

  • Output: d.data, d.solutie, d.id, d.id_obiect_rolii, d.institutie, d.categoriecaz, d.stadiuprocesual
  • Index Cond: ((d.data >= '2019-01-01 00:00:00'::timestamp without time zone) AND (d.data < '2019-02-01 00:00:00'::timestamp without time zone))
12. 0.162 0.295 ↑ 1.0 247 1

Hash (cost=5.48..5.48 rows=248 width=24) (actual time=0.295..0.295 rows=247 loops=1)

  • Output: i.id, i.ecris_name
  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
13. 0.133 0.133 ↑ 1.0 248 1

Seq Scan on analytics.institutii i (cost=0.00..5.48 rows=248 width=24) (actual time=0.008..0.133 rows=248 loops=1)

  • Output: i.id, i.ecris_name
14. 0.009 0.023 ↑ 65.4 13 1

Hash (cost=18.50..18.50 rows=850 width=36) (actual time=0.023..0.023 rows=13 loops=1)

  • Output: cc.id, cc.text_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.014 0.014 ↑ 65.4 13 1

Seq Scan on analytics.categoriecaz cc (cost=0.00..18.50 rows=850 width=36) (actual time=0.006..0.014 rows=13 loops=1)

  • Output: cc.id, cc.text_id
16. 0.019 0.037 ↑ 24.2 26 1

Hash (cost=16.30..16.30 rows=630 width=36) (actual time=0.037..0.037 rows=26 loops=1)

  • Output: sp.id, sp.simple_id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
17. 0.018 0.018 ↑ 24.2 26 1

Seq Scan on analytics.stadiuprocesual sp (cost=0.00..16.30 rows=630 width=36) (actual time=0.006..0.018 rows=26 loops=1)

  • Output: sp.id, sp.simple_id
18.          

SubPlan (forMerge Right Join)

19. 816.065 3,264.260 ↑ 1.0 1 163,213

Aggregate (cost=1.62..1.63 rows=1 width=32) (actual time=0.019..0.020 rows=1 loops=163,213)

  • Output: COALESCE(array_agg(dor.obiect_rolii_id), '{0}'::integer[])
20. 816.065 2,448.195 ↑ 3.0 1 163,213

Sort (cost=1.57..1.58 rows=3 width=4) (actual time=0.015..0.015 rows=1 loops=163,213)

  • Output: dor.obiect_rolii_id
  • Sort Key: dor.obiect_rolii_id
  • Sort Method: quicksort Memory: 25kB
21. 489.639 1,632.130 ↑ 3.0 1 163,213

HashAggregate (cost=1.52..1.55 rows=3 width=4) (actual time=0.010..0.010 rows=1 loops=163,213)

  • Output: dor.obiect_rolii_id
  • Group Key: dor.obiect_rolii_id
22. 163.213 1,142.491 ↑ 3.0 1 163,213

Append (cost=0.43..1.51 rows=3 width=4) (actual time=0.007..0.007 rows=1 loops=163,213)

23. 816.065 816.065 ↓ 0.0 0 163,213

Index Only Scan using dosar_obiect_rolii_pkey on public.dosar_obiect_rolii dor (cost=0.43..1.47 rows=2 width=4) (actual time=0.005..0.005 rows=0 loops=163,213)

  • Output: dor.obiect_rolii_id
  • Index Cond: (dor.iddosar = d.id)
  • Heap Fetches: 10538
24. 163.213 163.213 ↑ 1.0 1 163,213

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=163,213)

  • Output: d.id_obiect_rolii
  • One-Time Filter: (d.id_obiect_rolii IS NOT NULL)