explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ruby

Settings
# exclusive inclusive rows x rows loops node
1. 14.049 953.402 ↑ 142.3 48 1

Aggregate (cost=315,244.05..315,466 rows=6,829 width=591) (actual time=939.385..953.402 rows=48 loops=1)

  • Buffers: shared hit=1,890 read=70,172
2. 3.904 939.353 ↑ 3.9 1,754 1

Sort (cost=315,244.05..315,261.13 rows=6,829 width=645) (actual time=939.026..939.353 rows=1,754 loops=1)

  • Sort Key: f.id, u.id
  • Sort Method: quicksort Memory: 1,820kB
  • Buffers: shared hit=1,890 read=70,172
3. 0.488 935.449 ↑ 3.9 1,754 1

Nested Loop (cost=287,621.39..312,845.63 rows=6,829 width=645) (actual time=931.035..935.449 rows=1,754 loops=1)

  • Buffers: shared hit=1,890 read=70,172
4. 0.069 931.745 ↑ 2.1 48 1

Hash Join (cost=287,620.83..288,433.46 rows=100 width=627) (actual time=930.971..931.745 rows=48 loops=1)

  • Buffers: shared hit=1,530 read=69,920
5. 0.142 931.591 ↑ 2.1 48 1

Nested Loop (cost=287,615.02..288,427.35 rows=100 width=539) (actual time=930.862..931.591 rows=48 loops=1)

  • Buffers: shared hit=1,530 read=69,915
6. 95.890 930.777 ↑ 2.1 48 1

Limit (cost=287,614.6..287,614.85 rows=100 width=100) (actual time=930.755..930.777 rows=48 loops=1)

  • Buffers: shared hit=1,378 read=69,875
7. 0.097 834.887 ↑ 488.7 48 1

Sort (cost=287,614.6..287,673.24 rows=23,456 width=100) (actual time=834.876..834.887 rows=48 loops=1)

  • Sort Key: (convert_to_number(orderattr.value)) DESC
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=1,378 read=69,875
8. 27.537 834.790 ↑ 488.7 48 1

Hash Join (cost=155,403.59..286,718.13 rows=23,456 width=100) (actual time=326.249..834.79 rows=48 loops=1)

  • Buffers: shared hit=1,378 read=69,875
9. 500.284 527.502 ↑ 1.0 76,959 1

Bitmap Heap Scan on Attribute orderattr (cost=1,808.19..127,048.8 rows=79,969 width=49) (actual time=46.179..527.502 rows=76,959 loops=1)

  • Heap Blocks: exact=58,281
  • Buffers: shared hit=548 read=58,031
10. 27.218 27.218 ↑ 1.0 76,959 1

Bitmap Index Scan on attribute_type_idx (cost=0..1,788.2 rows=79,969 width=0) (actual time=27.218..27.218 rows=76,959 loops=1)

  • Index Cond: ((orderattr.type)::text = 'duration'::text)
  • Buffers: shared read=298
11. 0.035 279.751 ↑ 260.5 48 1

Hash (cost=153,439.08..153,439.08 rows=12,505 width=68) (actual time=279.749..279.751 rows=48 loops=1)

  • Buffers: shared hit=830 read=11,844
12. 0.036 279.716 ↑ 260.5 48 1

Subquery Scan on fileids (cost=678.95..153,439.08 rows=12,505 width=68) (actual time=279.605..279.716 rows=48 loops=1)

  • Buffers: shared hit=830 read=11,844
13. 5.888 279.680 ↑ 260.5 48 1

SetOp (cost=678.95..153,314.03 rows=12,505 width=72) (actual time=279.578..279.68 rows=48 loops=1)

  • Buffers: shared hit=830 read=11,844
14. 2.588 273.792 ↑ 2.0 14,620 1

Append (cost=678.95..153,240.94 rows=29,237 width=72) (actual time=160.939..273.792 rows=14,620 loops=1)

  • Buffers: shared hit=830 read=11,844
15. 0.133 161.820 ↑ 25.7 486 1

Result (cost=678.95..105,135.97 rows=12,505 width=72) (actual time=160.937..161.82 rows=486 loops=1)

  • Buffers: shared hit=509 read=5,240
16. 9.911 161.687 ↑ 25.7 486 1

SetOp (cost=678.95..105,010.92 rows=12,505 width=72) (actual time=160.91..161.687 rows=486 loops=1)

  • Buffers: shared hit=509 read=5,240
17. 2.975 151.776 ↑ 2.5 15,746 1

Append (cost=678.95..104,912.9 rows=39,208 width=72) (actual time=2.715..151.776 rows=15,746 loops=1)

  • Buffers: shared hit=509 read=5,240
18. 3.070 10.018 ↑ 1.0 12,529 1

Subquery Scan on *SELECT* 1 (cost=678.95..2,686.75 rows=12,552 width=30) (actual time=2.714..10.018 rows=12,529 loops=1)

  • Buffers: shared hit=244 read=115
19. 4.317 6.948 ↑ 1.0 12,529 1

Bitmap Heap Scan on _ProjectsFiles _ProjectsFiles (cost=678.95..2,561.23 rows=12,552 width=26) (actual time=2.711..6.948 rows=12,529 loops=1)

  • Heap Blocks: exact=234
  • Buffers: shared hit=244 read=115
20. 2.631 2.631 ↑ 1.0 12,529 1

Bitmap Index Scan on "_ProjectsFiles_B" (cost=0..675.81 rows=12,552 width=0) (actual time=2.631..2.631 rows=12,529 loops=1)

  • Index Cond: (("_ProjectsFiles"."B")::text = ANY ('{ckcqhm9j4o9sf0768ck49sa5y,ckeiz4ud3cyac0768t87hx7ua,ckdbe4y67hlks07683qsardlc,ckdr3wqyb4nsx0768vq0ghnog}'::text[]))
  • Buffers: shared hit=10 read=115
21. 0.768 138.783 ↑ 8.3 3,217 1

Subquery Scan on *SELECT* 2 (cost=27,836.03..102,030.12 rows=26,656 width=30) (actual time=116.557..138.783 rows=3,217 loops=1)

  • Buffers: shared hit=265 read=5,125
22. 22.023 138.015 ↑ 8.3 3,217 1

Bitmap Heap Scan on Attribute Attribute (cost=27,836.03..101,763.56 rows=26,656 width=26) (actual time=116.554..138.015 rows=3,217 loops=1)

  • Heap Blocks: exact=2,732
  • Buffers: shared hit=265 read=5,125
23. 115.992 115.992 ↑ 8.3 3,217 1

Bitmap Index Scan on attribute_value_num_idx (cost=0..27,829.37 rows=26,656 width=0) (actual time=115.992..115.992 rows=3,217 loops=1)

  • Index Cond: ((convert_to_number("Attribute".value) < '6000'::numeric) AND (("Attribute".type)::text = 'duration'::text))
  • Buffers: shared read=2,658
24. 3.311 109.384 ↑ 1.2 14,134 1

Subquery Scan on *SELECT* 3 (cost=357.67..47,958.78 rows=16,732 width=30) (actual time=5.316..109.384 rows=14,134 loops=1)

  • Buffers: shared hit=321 read=6,604
25. 102.219 106.073 ↑ 1.2 14,134 1

Bitmap Heap Scan on Attribute Attribute_1 (cost=357.67..47,791.46 rows=16,732 width=26) (actual time=5.313..106.073 rows=14,134 loops=1)

  • Heap Blocks: exact=6,870
  • Buffers: shared hit=321 read=6,604
26. 3.854 3.854 ↑ 1.2 14,134 1

Bitmap Index Scan on attribute_value_trgm_gin (cost=0..353.49 rows=16,732 width=0) (actual time=3.854..3.854 rows=14,134 loops=1)

  • Index Cond: ("Attribute_1".value ~~* '%sas%'::text)
  • Buffers: shared hit=1 read=54
27. 0.672 0.672 ↑ 1.0 1 48

Index Scan using "File_pkey" on File f (cost=0.42..8.12 rows=1 width=471) (actual time=0.014..0.014 rows=1 loops=48)

  • Index Cond: ((f.id)::text = (fileids.id)::text)
  • Buffers: shared hit=152 read=40
28. 0.023 0.085 ↓ 1.3 47 1

Hash (cost=5.36..5.36 rows=36 width=88) (actual time=0.085..0.085 rows=47 loops=1)

  • Buffers: shared read=5
29. 0.062 0.062 ↓ 1.3 47 1

Seq Scan on User u (cost=0..5.36 rows=36 width=88) (actual time=0.02..0.062 rows=47 loops=1)

  • Buffers: shared read=5
30. 3.216 3.216 ↑ 1.8 37 48

Index Scan using attribute_file_idx on Attribute a (cost=0.56..243.44 rows=68 width=112) (actual time=0.023..0.067 rows=37 loops=48)

  • Index Cond: ((a.file)::text = (fileids.id)::text)
  • Buffers: shared hit=360 read=252
Planning time : 1.91 ms
Execution time : 965.218 ms