explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kGL6

Settings
# exclusive inclusive rows x rows loops node
1. 1,834.615 1,834.615 ↓ 35.2 62,662 1

CTE Scan on d1 (cost=272,575.32..272,610.96 rows=1,782 width=16) (actual time=146.142..1,834.615 rows=62,662 loops=1)

2.          

CTE d1

3. 20.645 1,824.869 ↓ 35.2 62,662 1

Recursive Union (cost=11,938.75..272,575.32 rows=1,782 width=10) (actual time=146.140..1,824.869 rows=62,662 loops=1)

4. 12.842 163.398 ↓ 2,559.9 30,719 1

Unique (cost=11,938.75..11,938.84 rows=12 width=10) (actual time=146.137..163.398 rows=30,719 loops=1)

5. 17.510 150.556 ↓ 7,926.8 95,122 1

Sort (cost=11,938.75..11,938.78 rows=12 width=10) (actual time=146.135..150.556 rows=95,122 loops=1)

  • Sort Key: i.id, i.type
  • Sort Method: quicksort Memory: 7,531kB
6. 18.494 133.046 ↓ 7,926.8 95,122 1

Hash Join (cost=9,945.47..11,938.54 rows=12 width=10) (actual time=109.796..133.046 rows=95,122 loops=1)

  • Hash Cond: (i.id = dn.items_id)
7. 4.832 11.826 ↑ 1.0 62,664 1

Bitmap Heap Scan on items i (cost=852.15..2,610.11 rows=62,664 width=10) (actual time=7.055..11.826 rows=62,664 loops=1)

  • Recheck Cond: ((type = 'D'::bpchar) OR (type = 'R'::bpchar))
  • Heap Blocks: exact=406
8. 0.001 6.994 ↓ 0.0 0 1

BitmapOr (cost=852.15..852.15 rows=62,664 width=0) (actual time=6.994..6.994 rows=0 loops=1)

9. 6.985 6.985 ↑ 1.0 62,663 1

Bitmap Index Scan on items_type_index (cost=0.00..818.39 rows=62,663 width=0) (actual time=6.985..6.985 rows=62,663 loops=1)

  • Index Cond: (type = 'D'::bpchar)
10. 0.008 0.008 ↑ 1.0 1 1

Bitmap Index Scan on items_type_index (cost=0.00..2.42 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: (type = 'R'::bpchar)
11. 9.885 102.726 ↓ 3,963.4 95,122 1

Hash (cost=9,093.02..9,093.02 rows=24 width=8) (actual time=102.726..102.726 rows=95,122 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 4,740kB
12. 30.354 92.841 ↓ 3,963.4 95,122 1

Hash Join (cost=6,393.04..9,093.02 rows=24 width=8) (actual time=54.147..92.841 rows=95,122 loops=1)

  • Hash Cond: (dn.id = di.name_id)
13. 8.356 8.356 ↑ 1.0 128,272 1

Seq Scan on dirs_items_name dn (cost=0.00..2,218.72 rows=128,272 width=16) (actual time=0.007..8.356 rows=128,272 loops=1)

14. 10.237 54.131 ↓ 3,963.4 95,122 1

Hash (cost=6,392.74..6,392.74 rows=24 width=8) (actual time=54.131..54.131 rows=95,122 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 4,740kB
15. 8.757 43.894 ↓ 3,963.4 95,122 1

Gather (cost=1,040.52..6,392.74 rows=24 width=8) (actual time=0.345..43.894 rows=95,122 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
16. 18.729 35.137 ↓ 3,397.2 47,561 2 / 2

Hash Join (cost=40.52..5,390.34 rows=14 width=8) (actual time=0.144..35.137 rows=47,561 loops=2)

  • Hash Cond: (di.prevs_id = r.id)
17. 16.339 16.339 ↑ 1.2 193,888 2 / 2

Parallel Seq Scan on dirs_items di (cost=0.00..4,751.04 rows=228,104 width=16) (actual time=0.009..16.339 rows=193,888 loops=2)

18. 0.008 0.069 ↑ 1.0 24 2 / 2

Hash (cost=40.22..40.22 rows=24 width=8) (actual time=0.069..0.069 rows=24 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.061 0.061 ↑ 1.0 24 2 / 2

Index Scan using revs_items_id_index on revs r (cost=0.42..40.22 rows=24 width=8) (actual time=0.025..0.061 rows=24 loops=2)

  • Index Cond: (items_id = 2)
20. 11.988 1,640.826 ↓ 20.1 3,549 9

Unique (cost=26,058.76..26,060.08 rows=177 width=10) (actual time=180.523..182.314 rows=3,549 loops=9)

21. 19.692 1,628.838 ↓ 60.2 10,648 9

Sort (cost=26,058.76..26,059.20 rows=177 width=10) (actual time=180.520..180.982 rows=10,648 loops=9)

  • Sort Key: i_1.id, i_1.type
  • Sort Method: quicksort Memory: 25kB
22. 57.528 1,609.146 ↓ 60.2 10,648 9

Hash Join (cost=24,057.43..26,052.15 rows=177 width=10) (actual time=171.628..178.794 rows=10,648 loops=9)

  • Hash Cond: (i_1.id = dn_1.items_id)
23. 41.967 79.911 ↑ 1.0 62,664 9

Bitmap Heap Scan on items i_1 (cost=852.15..2,610.11 rows=62,664 width=10) (actual time=4.260..8.879 rows=62,664 loops=9)

  • Recheck Cond: ((type = 'D'::bpchar) OR (type = 'R'::bpchar))
  • Heap Blocks: exact=3,654
24. 0.027 37.944 ↓ 0.0 0 9

BitmapOr (cost=852.15..852.15 rows=62,664 width=0) (actual time=4.216..4.216 rows=0 loops=9)

25. 37.845 37.845 ↑ 1.0 62,663 9

Bitmap Index Scan on items_type_index (cost=0.00..818.39 rows=62,663 width=0) (actual time=4.205..4.205 rows=62,663 loops=9)

  • Index Cond: (type = 'D'::bpchar)
26. 0.072 0.072 ↑ 1.0 1 9

Bitmap Index Scan on items_type_index (cost=0.00..2.42 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=9)

  • Index Cond: (type = 'R'::bpchar)
27. 31.509 1,471.707 ↓ 89.6 32,517 9

Hash (cost=23,200.74..23,200.74 rows=363 width=8) (actual time=163.523..163.523 rows=32,517 loops=9)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
28. 128.718 1,440.198 ↓ 89.6 32,517 9

Hash Join (cost=20,176.69..23,200.74 rows=363 width=8) (actual time=140.705..160.022 rows=32,517 loops=9)

  • Hash Cond: (dn_1.id = di_1.name_id)
29. 76.572 76.572 ↑ 1.0 128,272 9

Seq Scan on dirs_items_name dn_1 (cost=0.00..2,218.72 rows=128,272 width=16) (actual time=0.005..8.508 rows=128,272 loops=9)

30. 35.595 1,234.908 ↓ 89.6 32,517 9

Hash (cost=20,172.16..20,172.16 rows=363 width=8) (actual time=137.212..137.212 rows=32,517 loops=9)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
31. 300.879 1,199.313 ↓ 89.6 32,517 9

Hash Join (cost=12,366.59..20,172.16 rows=363 width=8) (actual time=79.246..133.257 rows=32,517 loops=9)

  • Hash Cond: (di_1.prevs_id = r_1.id)
32. 218.007 218.007 ↑ 1.0 387,777 9

Seq Scan on dirs_items di_1 (cost=0.00..6,347.77 rows=387,777 width=16) (actual time=0.004..24.223 rows=387,777 loops=9)

33. 23.967 680.427 ↓ 58.0 20,888 9

Hash (cost=12,362.09..12,362.09 rows=360 width=8) (actual time=75.603..75.603 rows=20,888 loops=9)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
34. 327.717 656.460 ↓ 58.0 20,888 9

Hash Join (cost=3.90..12,362.09 rows=360 width=8) (actual time=3.994..72.940 rows=20,888 loops=9)

  • Hash Cond: (r_1.items_id = n.id)
35. 318.150 318.150 ↑ 1.0 384,843 9

Seq Scan on revs r_1 (cost=0.00..10,911.43 rows=384,843 width=16) (actual time=0.004..35.350 rows=384,843 loops=9)

36. 6.417 10.593 ↓ 58.0 6,962 9

Hash (cost=2.40..2.40 rows=120 width=8) (actual time=1.177..1.177 rows=6,962 loops=9)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
37. 4.176 4.176 ↓ 58.0 6,962 9

WorkTable Scan on d1 n (cost=0.00..2.40 rows=120 width=8) (actual time=0.001..0.464 rows=6,962 loops=9)