explain.depesz.com

PostgreSQL's explain analyze made readable

Result: upri

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 0.382 ↑ 3.0 1 1

GroupAggregate (cost=114.93..115.03 rows=3 width=23) (actual time=0.382..0.382 rows=1 loops=1)

  • Group Key: dmdev_grouping.value
2. 0.003 0.379 ↓ 1.7 5 1

Unique (cost=114.93..114.96 rows=3 width=86) (actual time=0.378..0.379 rows=5 loops=1)

3. 0.016 0.376 ↓ 2.3 7 1

Sort (cost=114.93..114.94 rows=3 width=86) (actual time=0.376..0.376 rows=7 loops=1)

  • Sort Key: dmdev_grouping.value, document.uuid, document_revision.title
  • Sort Method: quicksort Memory: 25kB
4. 0.008 0.360 ↓ 2.3 7 1

Hash Semi Join (cost=79.35..114.91 rows=3 width=86) (actual time=0.345..0.360 rows=7 loops=1)

  • Hash Cond: ((doc_cat.parent_uuid)::text = (recursive_category.uuid)::text)
5. 0.015 0.315 ↓ 2.3 7 1

Hash Join (cost=61.10..96.61 rows=3 width=602) (actual time=0.302..0.315 rows=7 loops=1)

  • Hash Cond: ((dmdev_grouping.metadata_entry_uuid)::text = (dmde_grouping.uuid)::text)
6. 0.082 0.082 ↓ 1.0 105 1

Seq Scan on metadata_value dmdev_grouping (cost=0.00..35.09 rows=104 width=52) (actual time=0.003..0.082 rows=105 loops=1)

  • Filter: is_default
  • Rows Removed by Filter: 504
7. 0.005 0.218 ↓ 2.3 7 1

Hash (cost=61.07..61.07 rows=3 width=624) (actual time=0.218..0.218 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
8. 0.005 0.213 ↓ 2.3 7 1

Nested Loop (cost=25.40..61.07 rows=3 width=624) (actual time=0.182..0.213 rows=7 loops=1)

9. 0.005 0.194 ↓ 1.8 7 1

Nested Loop (cost=25.26..56.60 rows=4 width=627) (actual time=0.174..0.194 rows=7 loops=1)

  • Join Filter: ((doc_cat.document_uuid)::text = (document.uuid)::text)
10. 0.026 0.175 ↓ 3.5 7 1

Hash Join (cost=25.12..55.76 rows=2 width=1,143) (actual time=0.165..0.175 rows=7 loops=1)

  • Hash Cond: ((dmdev_date.metadata_entry_uuid)::text = (dmde_date.uuid)::text)
11. 0.028 0.046 ↑ 1.0 100 1

Bitmap Heap Scan on metadata_value dmdev_date (cost=5.05..35.30 rows=100 width=37) (actual time=0.022..0.046 rows=100 loops=1)

  • Recheck Cond: (value < '2020-10-13'::text)
  • Heap Blocks: exact=21
12. 0.018 0.018 ↑ 1.0 100 1

Bitmap Index Scan on metadata_value_idx (cost=0.00..5.03 rows=100 width=0) (actual time=0.018..0.018 rows=100 loops=1)

  • Index Cond: (value < '2020-10-13'::text)
13. 0.003 0.103 ↓ 2.5 5 1

Hash (cost=20.04..20.04 rows=2 width=1,180) (actual time=0.103..0.103 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
14. 0.012 0.100 ↓ 2.5 5 1

Hash Join (cost=13.59..20.04 rows=2 width=1,180) (actual time=0.097..0.100 rows=5 loops=1)

  • Hash Cond: ((dmde_grouping.document_metadata_uuid)::text = (dmd_grouping.uuid)::text)
15. 0.014 0.014 ↑ 1.0 41 1

Seq Scan on metadata_entry dmde_grouping (cost=0.00..6.28 rows=41 width=74) (actual time=0.003..0.014 rows=41 loops=1)

  • Filter: ((metadata_uuid)::text = '777921a8-baa5-4e72-a6b9-5af1715847e3'::text)
  • Rows Removed by Filter: 61
16. 0.003 0.074 ↓ 1.7 5 1

Hash (cost=13.56..13.56 rows=3 width=1,180) (actual time=0.074..0.074 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
17. 0.010 0.071 ↓ 1.7 5 1

Hash Join (cost=7.10..13.56 rows=3 width=1,180) (actual time=0.067..0.071 rows=5 loops=1)

  • Hash Cond: ((dmde_date.document_metadata_uuid)::text = (dmd_date.uuid)::text)
18. 0.016 0.016 ↑ 1.0 41 1

Seq Scan on metadata_entry dmde_date (cost=0.00..6.28 rows=41 width=74) (actual time=0.001..0.016 rows=41 loops=1)

  • Filter: ((metadata_uuid)::text = '49a7266e-19c5-4f1e-84c7-fef6a5fe9a27'::text)
  • Rows Removed by Filter: 61
19. 0.004 0.045 ↑ 1.0 5 1

Hash (cost=7.04..7.04 rows=5 width=1,180) (actual time=0.045..0.045 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
20. 0.009 0.041 ↑ 1.0 5 1

Hash Join (cost=4.11..7.04 rows=5 width=1,180) (actual time=0.039..0.041 rows=5 loops=1)

  • Hash Cond: ((dmd_grouping.document_uuid)::text = (doc_cat.document_uuid)::text)
21. 0.007 0.007 ↑ 1.3 49 1

Seq Scan on document_metadata dmd_grouping (cost=0.00..2.64 rows=64 width=74) (actual time=0.002..0.007 rows=49 loops=1)

22. 0.002 0.025 ↑ 1.0 5 1

Hash (cost=4.04..4.04 rows=5 width=1,106) (actual time=0.025..0.025 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.012 0.023 ↑ 1.0 5 1

Hash Join (cost=1.11..4.04 rows=5 width=1,106) (actual time=0.020..0.023 rows=5 loops=1)

  • Hash Cond: ((dmd_date.document_uuid)::text = (doc_cat.document_uuid)::text)
24. 0.006 0.006 ↑ 1.3 49 1

Seq Scan on document_metadata dmd_date (cost=0.00..2.64 rows=64 width=74) (actual time=0.001..0.006 rows=49 loops=1)

25. 0.003 0.005 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=1,032) (actual time=0.005..0.005 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.002 0.002 ↓ 1.8 9 1

Seq Scan on category doc_cat (cost=0.00..1.05 rows=5 width=1,032) (actual time=0.001..0.002 rows=9 loops=1)

27. 0.014 0.014 ↑ 1.0 1 7

Index Scan using document_pkey on document (cost=0.14..0.41 rows=1 width=74) (actual time=0.002..0.002 rows=1 loops=7)

  • Index Cond: ((uuid)::text = (dmd_date.document_uuid)::text)
  • Filter: ((NOT template) AND (NOT is_archived))
28. 0.014 0.014 ↑ 1.0 1 7

Index Scan using doc_pkey on document_revision (cost=0.14..1.10 rows=1 width=71) (actual time=0.002..0.002 rows=1 loops=7)

  • Index Cond: ((uuid)::text = (document.current_working_revision_uuid)::text)
  • Filter: (is_released OR (version > 0))
29. 0.004 0.037 ↑ 6.4 8 1

Hash (cost=17.61..17.61 rows=51 width=516) (actual time=0.037..0.037 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
30. 0.033 0.033 ↑ 6.4 8 1

CTE Scan on recursive_category (cost=16.08..17.10 rows=51 width=516) (actual time=0.010..0.033 rows=8 loops=1)

31.          

CTE recursive_category

32. 0.007 0.030 ↑ 6.4 8 1

Recursive Union (cost=0.00..16.08 rows=51 width=1,548) (actual time=0.009..0.030 rows=8 loops=1)

33. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on category (cost=0.00..1.06 rows=1 width=1,548) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: ((uuid)::text = '77788088-8e44-4861-a325-888f01d77734'::text)
  • Rows Removed by Filter: 8
34. 0.009 0.016 ↑ 1.2 4 2

Hash Join (cost=1.11..1.40 rows=5 width=1,548) (actual time=0.007..0.008 rows=4 loops=2)

  • Hash Cond: ((recursive_category_1.uuid)::text = (category_1.parent_uuid)::text)
35. 0.000 0.000 ↑ 2.5 4 2

WorkTable Scan on recursive_category recursive_category_1 (cost=0.00..0.20 rows=10 width=516) (actual time=0.000..0.000 rows=4 loops=2)

36. 0.004 0.007 ↓ 1.6 8 1

Hash (cost=1.05..1.05 rows=5 width=1,548) (actual time=0.007..0.007 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
37. 0.003 0.003 ↓ 1.8 9 1

Seq Scan on category category_1 (cost=0.00..1.05 rows=5 width=1,548) (actual time=0.002..0.003 rows=9 loops=1)

Planning time : 2.276 ms
Execution time : 0.590 ms