explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ISd0

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 530.225 ↑ 1.0 10 1

Limit (cost=17,999.25..26,961.74 rows=10 width=304) (actual time=311.580..530.225 rows=10 loops=1)

2.          

CTE reference

3. 0.086 0.275 ↓ 2.4 17 1

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

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

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

5. 0.029 0.126 ↓ 2.5 57 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
6. 0.097 0.097 ↓ 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.059..0.097 rows=57 loops=1)

  • Index Cond: (hierarchy = 'compte_tva_pro'::text)
7. 0.099 530.213 ↑ 2.9 30 1

Result (cost=23.47..78,893.46 rows=88 width=304) (actual time=165.235..530.213 rows=30 loops=1)

8. 0.204 0.704 ↑ 2.9 30 1

Sort (cost=23.47..23.69 rows=88 width=231) (actual time=0.693..0.704 rows=30 loops=1)

  • Sort Key: c.name, c.id, c."customData
  • Sort Method: quicksort Memory: 48kB
9. 0.090 0.500 ↑ 1.7 52 1

HashAggregate (cost=19.99..20.87 rows=88 width=231) (actual time=0.481..0.500 rows=52 loops=1)

  • Group Key: c.id
10. 0.045 0.410 ↑ 1.7 52 1

Hash Left Join (cost=0.51..19.77 rows=88 width=231) (actual time=0.338..0.410 rows=52 loops=1)

  • Hash Cond: (c.id = ref.target_id)
11. 0.066 0.066 ↑ 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.029..0.066 rows=52 loops=1)

  • Index Cond: (hierarchy = 'compte_tva_pro'::text)
12. 0.006 0.299 ↓ 2.4 17 1

Hash (cost=0.14..0.14 rows=7 width=16) (actual time=0.299..0.299 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.293 0.293 ↓ 2.4 17 1

CTE Scan on reference ref (cost=0.00..0.14 rows=7 width=16) (actual time=0.262..0.293 rows=17 loops=1)

14.          

SubPlan (forResult)

15. 0.030 0.210 ↓ 0.0 0 30

Limit (cost=0.17..0.18 rows=1 width=32) (actual time=0.006..0.007 rows=0 loops=30)

16. 0.090 0.180 ↓ 0.0 0 30

Sort (cost=0.17..0.18 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=30)

  • Sort Key: ((reference.id)::text)
  • Sort Method: quicksort Memory: 25kB
17. 0.090 0.090 ↓ 0.0 0 30

CTE Scan on reference (cost=0.00..0.16 rows=1 width=32) (actual time=0.002..0.003 rows=0 loops=30)

  • Filter: (target_id = c.id)
  • Rows Removed by Filter: 17
18. 0.000 0.120 ↓ 0.0 0 30

Limit (cost=0.17..0.17 rows=1 width=48) (actual time=0.004..0.004 rows=0 loops=30)

19. 0.060 0.120 ↓ 0.0 0 30

Sort (cost=0.17..0.17 rows=1 width=48) (actual time=0.004..0.004 rows=0 loops=30)

  • Sort Key: reference_1.id
  • Sort Method: quicksort Memory: 25kB
20. 0.060 0.060 ↓ 0.0 0 30

CTE Scan on reference reference_1 (cost=0.00..0.16 rows=1 width=48) (actual time=0.001..0.002 rows=0 loops=30)

  • Filter: (target_id = c.id)
  • Rows Removed by Filter: 17
21. 0.030 0.120 ↓ 0.0 0 30

Limit (cost=0.17..0.17 rows=1 width=17) (actual time=0.004..0.004 rows=0 loops=30)

22. 0.030 0.090 ↓ 0.0 0 30

Sort (cost=0.17..0.17 rows=1 width=17) (actual time=0.003..0.003 rows=0 loops=30)

  • Sort Key: reference_2.id
  • Sort Method: quicksort Memory: 25kB
23. 0.060 0.060 ↓ 0.0 0 30

CTE Scan on reference reference_2 (cost=0.00..0.16 rows=1 width=17) (actual time=0.001..0.002 rows=0 loops=30)

  • Filter: (target_id = c.id)
  • Rows Removed by Filter: 17
24. 244.080 528.960 ↑ 1.0 1 30

Aggregate (cost=895.70..895.71 rows=1 width=8) (actual time=17.632..17.632 rows=1 loops=30)

25. 284.880 284.880 ↓ 2.7 38,171 30

Index Only Scan using classification_label_id_source_id_idx on classification (cost=0.43..860.53 rows=14,068 width=16) (actual time=0.011..9.496 rows=38,171 loops=30)

  • Index Cond: (label_id = c.id)
  • Heap Fetches: 45237
Planning time : 0.879 ms
Execution time : 530.403 ms