explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RA1X

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 4,251.723 ↑ 1.0 10 1

Limit (cost=86,983.58..86,983.61 rows=10 width=335) (actual time=4,251.718..4,251.723 rows=10 loops=1)

2.          

CTE reference

3. 0.040 0.139 ↓ 2.4 17 1

Hash Join (cost=23.97..50.78 rows=7 width=65) (actual time=0.131..0.139 rows=17 loops=1)

  • Hash Cond: (g2l.source_id = ic.id)
4. 0.040 0.040 ↑ 4.9 271 1

Seq Scan on g2l (cost=0.00..23.30 rows=1,330 width=33) (actual time=0.018..0.040 rows=271 loops=1)

5. 0.014 0.059 ↓ 2.5 57 1

Hash (cost=23.68..23.68 rows=23 width=80) (actual time=0.059..0.059 rows=57 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
6. 0.045 0.045 ↓ 2.5 57 1

Index Scan using category_hierarchy_idx on category ic (cost=0.28..23.68 rows=23 width=80) (actual time=0.024..0.045 rows=57 loops=1)

  • Index Cond: (hierarchy = 'compte_tva_pro'::text)
7. 27.564 4,251.717 ↑ 2.9 30 1

Sort (cost=86,932.75..86,932.97 rows=88 width=335) (actual time=4,251.713..4,251.717 rows=30 loops=1)

  • Sort Key: c.name
  • Sort Method: quicksort Memory: 67513kB
8. 1,570.191 4,224.153 ↑ 1.7 52 1

GroupAggregate (cost=85,803.33..86,930.15 rows=88 width=335) (actual time=2,899.305..4,224.153 rows=52 loops=1)

  • Group Key: c.id
9. 307.118 2,653.962 ↓ 26.2 1,309,909 1

Merge Left Join (cost=85,803.33..86,054.28 rows=49,962 width=296) (actual time=2,061.361..2,653.962 rows=1,309,909 loops=1)

  • Merge Cond: (c.id = ref.target_id)
10. 1,530.210 2,307.591 ↓ 26.2 1,309,909 1

Sort (cost=85,803.09..85,928.00 rows=49,962 width=247) (actual time=2,061.187..2,307.591 rows=1,309,909 loops=1)

  • Sort Key: c.id
  • Sort Method: external merge Disk: 405384kB
11. 561.471 777.381 ↓ 26.2 1,309,909 1

Hash Right Join (cost=20.30..81,903.92 rows=49,962 width=247) (actual time=0.152..777.381 rows=1,309,909 loops=1)

  • Hash Cond: (cla.label_id = c.id)
12. 215.779 215.779 ↑ 1.0 2,602,097 1

Seq Scan on classification cla (cost=0.00..75,044.13 rows=2,603,113 width=32) (actual time=0.009..215.779 rows=2,602,097 loops=1)

13. 0.034 0.131 ↑ 1.7 52 1

Hash (cost=19.20..19.20 rows=88 width=231) (actual time=0.131..0.131 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
14. 0.097 0.097 ↑ 1.7 52 1

Index Scan using category_hierarchy_idx on category c (cost=0.28..19.20 rows=88 width=231) (actual time=0.033..0.097 rows=52 loops=1)

  • Index Cond: (hierarchy = 'compte_tva_pro'::text)
15. 39.105 39.253 ↓ 129,015.0 903,105 1

Sort (cost=0.24..0.26 rows=7 width=65) (actual time=0.168..39.253 rows=903,105 loops=1)

  • Sort Key: ref.target_id
  • Sort Method: quicksort Memory: 27kB
16. 0.148 0.148 ↓ 2.4 17 1

CTE Scan on reference ref (cost=0.00..0.14 rows=7 width=65) (actual time=0.134..0.148 rows=17 loops=1)

Planning time : 0.845 ms
Execution time : 4,348.059 ms