explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HMf5 : 2

Settings
# exclusive inclusive rows x rows loops node
1. 0.051 1,528.768 ↑ 1.0 335 1

Subquery Scan on results (cost=137,203.96..137,208.14 rows=335 width=124) (actual time=1,528.710..1,528.768 rows=335 loops=1)

2.          

CTE arhiv_tmp

3. 5.509 721.788 ↓ 2.8 12,096 1

Unique (cost=103,489.77..103,759.72 rows=4,365 width=36) (actual time=709.881..721.788 rows=12,096 loops=1)

4. 50.173 716.279 ↓ 1.7 45,605 1

Sort (cost=103,489.77..103,557.26 rows=26,995 width=36) (actual time=709.879..716.279 rows=45,605 loops=1)

  • Sort Key: arh.pr_id, arh."DateValue
  • Sort Method: external sort Disk: 1696kB
5. 11.237 666.106 ↓ 1.7 45,605 1

Hash Join (cost=98,524.26..100,764.38 rows=26,995 width=36) (actual time=626.994..666.106 rows=45,605 loops=1)

  • Hash Cond: (arh.pr_id = p.prp_id)
6. 4.899 652.507 ↓ 1.1 49,190 1

Merge Left Join (cost=98,279.63..100,133.56 rows=43,643 width=36) (actual time=624.625..652.507 rows=49,190 loops=1)

  • Merge Cond: (arh.pr_id = arangcontract3.prp_id)
7. 4.540 647.603 ↓ 1.1 49,190 1

Merge Left Join (cost=98,120.67..99,392.99 rows=43,643 width=32) (actual time=624.619..647.603 rows=49,190 loops=1)

  • Merge Cond: (arh.pr_id = aranguser3.prp_id)
8. 5.987 643.060 ↓ 1.1 49,190 1

Merge Left Join (cost=97,961.70..98,652.42 rows=43,643 width=28) (actual time=624.615..643.060 rows=49,190 loops=1)

  • Merge Cond: (arh.pr_id = arangmetrol3.prp_id)
9. 46.031 637.060 ↓ 1.1 49,190 1

Sort (cost=97,802.74..97,911.85 rows=43,643 width=24) (actual time=624.599..637.060 rows=49,190 loops=1)

  • Sort Key: arh.pr_id
  • Sort Method: external merge Disk: 1632kB
10. 591.029 591.029 ↓ 1.1 49,190 1

Seq Scan on "Arhiv_cnt" arh (cost=0.00..93,543.29 rows=43,643 width=24) (actual time=570.897..591.029 rows=49,190 loops=1)

  • Filter: (("DateValue" >= '2019-06-24 00:00:00'::timestamp without time zone) AND ("DateValue" <= '2019-07-01 00:00:00'::timestamp without time zone) AND (typ_arh = 2))
11. 0.012 0.013 ↓ 0.0 0 1

Sort (cost=158.96..164.31 rows=2,140 width=8) (actual time=0.013..0.013 rows=0 loops=1)

  • Sort Key: arangmetrol3.prp_id
  • Sort Method: quicksort Memory: 25kB
12. 0.001 0.001 ↓ 0.0 0 1

Hash Left Join (cost=1.05..40.58 rows=2,140 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Hash Cond: (arangmetrol3.range_id = arang033.range_id)
13. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on "RangesToParam" arangmetrol3 (cost=0.00..31.40 rows=2,140 width=8) (actual time=0.000..0.000 rows=0 loops=1)

14. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.04..1.04 rows=1 width=4) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Seq Scan on "AnalyticRanges" arang033 (cost=0.00..1.04 rows=1 width=4) (never executed)

  • Filter: (range_typ = 2)
16. 0.002 0.003 ↓ 0.0 0 1

Sort (cost=158.96..164.31 rows=2,140 width=8) (actual time=0.003..0.003 rows=0 loops=1)

  • Sort Key: aranguser3.prp_id
  • Sort Method: quicksort Memory: 25kB
17. 0.001 0.001 ↓ 0.0 0 1

Hash Left Join (cost=1.05..40.58 rows=2,140 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Hash Cond: (aranguser3.range_id = arang023.range_id)
18. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on "RangesToParam" aranguser3 (cost=0.00..31.40 rows=2,140 width=8) (actual time=0.000..0.000 rows=0 loops=1)

19. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.04..1.04 rows=1 width=4) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Seq Scan on "AnalyticRanges" arang023 (cost=0.00..1.04 rows=1 width=4) (never executed)

  • Filter: (range_typ = 1)
21. 0.004 0.005 ↓ 0.0 0 1

Sort (cost=158.96..164.31 rows=2,140 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Sort Key: arangcontract3.prp_id
  • Sort Method: quicksort Memory: 25kB
22. 0.001 0.001 ↓ 0.0 0 1

Hash Left Join (cost=1.05..40.58 rows=2,140 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Hash Cond: (arangcontract3.range_id = arang013.range_id)
23. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on "RangesToParam" arangcontract3 (cost=0.00..31.40 rows=2,140 width=8) (actual time=0.000..0.000 rows=0 loops=1)

24. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.04..1.04 rows=1 width=4) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Seq Scan on "AnalyticRanges" arang013 (cost=0.00..1.04 rows=1 width=4) (never executed)

  • Filter: (range_typ = 0)
26. 0.336 2.362 ↓ 1.3 2,376 1

Hash (cost=221.70..221.70 rows=1,834 width=4) (actual time=2.362..2.362 rows=2,376 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 84kB
27. 0.694 2.026 ↓ 1.3 2,376 1

HashAggregate (cost=203.36..221.70 rows=1,834 width=4) (actual time=1.794..2.026 rows=2,376 loops=1)

28. 0.411 1.332 ↓ 1.3 2,376 1

Hash Semi Join (cost=12.36..198.78 rows=1,834 width=4) (actual time=0.103..1.332 rows=2,376 loops=1)

  • Hash Cond: (p.plc_id = "Places_cnt".plc_id)
29. 0.823 0.823 ↓ 1.3 2,378 1

Seq Scan on "ParamResPlc_cnt" p (cost=0.00..161.08 rows=1,878 width=8) (actual time=0.002..0.823 rows=2,378 loops=1)

  • Filter: ("ParamRes_id" = ANY ('{0,2,995,44,999,377,1003,1,955,308,959,310,963}'::integer[]))
30. 0.041 0.098 ↓ 1.0 336 1

Hash (cost=8.18..8.18 rows=335 width=4) (actual time=0.098..0.098 rows=336 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
31. 0.057 0.057 ↓ 1.0 336 1

Seq Scan on "Places_cnt" (cost=0.00..8.18 rows=335 width=4) (actual time=0.003..0.057 rows=336 loops=1)

  • Filter: ((place_id = 338) OR (plc_id = 338))
32. 1.385 1,528.717 ↑ 1.0 335 1

Sort (cost=33,444.24..33,445.07 rows=335 width=82) (actual time=1,528.709..1,528.717 rows=335 loops=1)

  • Sort Key: (max(((pt."Name" || ' '::text) || p."Name")))
  • Sort Method: quicksort Memory: 72kB
33. 689.550 1,527.332 ↑ 1.0 335 1

HashAggregate (cost=33,426.84..33,430.19 rows=335 width=82) (actual time=1,527.171..1,527.332 rows=335 loops=1)

34. 70.357 837.782 ↓ 1.2 410,958 1

Hash Left Join (cost=874.08..20,026.84 rows=335,000 width=82) (actual time=745.350..837.782 rows=410,958 loops=1)

  • Hash Cond: (prp2.prp_id = a2.pr_id)
35. 12.587 762.612 ↑ 5.4 61,530 1

Merge Right Join (cost=775.59..18,237.88 rows=335,000 width=78) (actual time=740.522..762.612 rows=61,530 loops=1)

  • Merge Cond: (prp3.plc_id = p.plc_id)
36. 0.771 731.380 ↓ 5.7 1,923 1

Sort (cost=209.85..210.69 rows=336 width=28) (actual time=731.210..731.380 rows=1,923 loops=1)

  • Sort Key: prp3.plc_id
  • Sort Method: quicksort Memory: 139kB
37. 0.511 730.609 ↓ 5.7 1,923 1

Hash Left Join (cost=98.49..195.75 rows=336 width=28) (actual time=729.616..730.609 rows=1,923 loops=1)

  • Hash Cond: (prp3.prp_id = a3.pr_id)
38. 0.497 0.497 ↑ 1.0 336 1

Seq Scan on "ParamResPlc_cnt" prp3 (cost=0.00..95.56 rows=336 width=8) (actual time=0.005..0.497 rows=336 loops=1)

  • Filter: ("ParamRes_id" = 2)
39. 2.324 729.601 ↓ 549.8 12,096 1

Hash (cost=98.21..98.21 rows=22 width=24) (actual time=729.601..729.601 rows=12,096 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 520kB
40. 727.277 727.277 ↓ 549.8 12,096 1

CTE Scan on arhiv_tmp a3 (cost=0.00..98.21 rows=22 width=24) (actual time=709.888..727.277 rows=12,096 loops=1)

  • Filter: (typ_arh = 2)
41. 4.816 18.645 ↑ 5.4 61,530 1

Materialize (cost=565.74..13,926.24 rows=335,000 width=54) (actual time=9.308..18.645 rows=61,530 loops=1)

42. 1.956 13.829 ↑ 34.5 9,702 1

Merge Left Join (cost=565.74..13,088.74 rows=335,000 width=54) (actual time=9.305..13.829 rows=9,702 loops=1)

  • Merge Cond: (p.plc_id = prp4.plc_id)
43. 0.164 4.288 ↑ 166.7 2,010 1

Nested Loop (cost=355.95..7,162.09 rows=335,000 width=30) (actual time=2.384..4.288 rows=2,010 loops=1)

44. 0.197 3.789 ↑ 1.0 335 1

Nested Loop Left Join (cost=355.70..461.84 rows=335 width=30) (actual time=2.334..3.789 rows=335 loops=1)

45. 0.187 2.587 ↑ 1.0 335 1

Merge Left Join (cost=355.70..362.31 rows=335 width=27) (actual time=2.322..2.587 rows=335 loops=1)

  • Merge Cond: (p.plc_id = prp2.plc_id)
46. 0.162 1.882 ↑ 1.0 335 1

Sort (cost=246.03..246.87 rows=335 width=23) (actual time=1.842..1.882 rows=335 loops=1)

  • Sort Key: p.plc_id
  • Sort Method: quicksort Memory: 51kB
47. 0.070 1.720 ↑ 1.0 335 1

Hash Join (cost=127.99..231.99 rows=335 width=23) (actual time=0.842..1.720 rows=335 loops=1)

  • Hash Cond: (p.typ_id = pt.typ_id)
48. 0.149 1.640 ↑ 1.0 335 1

Hash Right Join (cost=126.54..225.93 rows=335 width=16) (actual time=0.825..1.640 rows=335 loops=1)

  • Hash Cond: (prp1.plc_id = p.plc_id)
49. 0.497 1.206 ↑ 1.0 336 1

Hash Right Join (cost=99.76..194.63 rows=336 width=8) (actual time=0.466..1.206 rows=336 loops=1)

  • Hash Cond: (pr1.prp_id = prp1.prp_id)
50. 0.254 0.254 ↑ 1.0 4,764 1

Seq Scan on "PointRead" pr1 (cost=0.00..73.64 rows=4,764 width=8) (actual time=0.002..0.254 rows=4,764 loops=1)

51. 0.052 0.455 ↑ 1.0 336 1

Hash (cost=95.56..95.56 rows=336 width=8) (actual time=0.455..0.455 rows=336 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
52. 0.403 0.403 ↑ 1.0 336 1

Seq Scan on "ParamResPlc_cnt" prp1 (cost=0.00..95.56 rows=336 width=8) (actual time=0.003..0.403 rows=336 loops=1)

  • Filter: ("ParamRes_id" = 2)
53. 0.058 0.285 ↑ 1.0 335 1

Hash (cost=22.59..22.59 rows=335 width=12) (actual time=0.285..0.285 rows=335 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
54. 0.084 0.227 ↑ 1.0 335 1

Hash Right Join (cost=11.50..22.59 rows=335 width=12) (actual time=0.186..0.227 rows=335 loops=1)

  • Hash Cond: (pchild.place_id = p.plc_id)
55. 0.020 0.020 ↑ 1.0 345 1

Seq Scan on "Places_cnt" pchild (cost=0.00..6.45 rows=345 width=4) (actual time=0.001..0.020 rows=345 loops=1)

56. 0.059 0.123 ↑ 1.0 335 1

Hash (cost=7.31..7.31 rows=335 width=12) (actual time=0.123..0.123 rows=335 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
57. 0.064 0.064 ↑ 1.0 335 1

Seq Scan on "Places_cnt" p (cost=0.00..7.31 rows=335 width=12) (actual time=0.005..0.064 rows=335 loops=1)

  • Filter: (place_id = 338)
58. 0.005 0.010 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=15) (actual time=0.010..0.010 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
59. 0.005 0.005 ↑ 1.0 20 1

Seq Scan on "PlaceTyp_cnt" pt (cost=0.00..1.20 rows=20 width=15) (actual time=0.002..0.005 rows=20 loops=1)

60. 0.138 0.518 ↑ 1.0 336 1

Sort (cost=109.66..110.50 rows=336 width=8) (actual time=0.477..0.518 rows=336 loops=1)

  • Sort Key: prp2.plc_id
  • Sort Method: quicksort Memory: 40kB
61. 0.380 0.380 ↑ 1.0 336 1

Seq Scan on "ParamResPlc_cnt" prp2 (cost=0.00..95.56 rows=336 width=8) (actual time=0.003..0.380 rows=336 loops=1)

  • Filter: ("ParamRes_id" = 2)
62. 1.005 1.005 ↑ 1.0 1 335

Index Scan using "Device_cnt_pkey" on "Device_cnt" d1 (cost=0.00..0.28 rows=1 width=11) (actual time=0.002..0.003 rows=1 loops=335)

  • Index Cond: (pr1.dev_id = dev_id)
63. 0.335 0.335 ↑ 166.7 6 335

Function Scan on "GetDateRange" dts (cost=0.25..10.25 rows=1,000 width=0) (actual time=0.000..0.001 rows=6 loops=335)

64. 1.193 7.585 ↓ 28.9 9,697 1

Sort (cost=209.79..210.63 rows=335 width=28) (actual time=6.918..7.585 rows=9,697 loops=1)

  • Sort Key: prp4.plc_id
  • Sort Method: quicksort Memory: 124kB
65. 0.348 6.392 ↓ 4.8 1,617 1

Hash Left Join (cost=98.49..195.75 rows=335 width=28) (actual time=5.931..6.392 rows=1,617 loops=1)

  • Hash Cond: (prp4.prp_id = a4.pr_id)
66. 0.395 0.395 ↑ 1.0 335 1

Seq Scan on "ParamResPlc_cnt" prp4 (cost=0.00..95.56 rows=335 width=8) (actual time=0.271..0.395 rows=335 loops=1)

  • Filter: ("ParamRes_id" = 995)
67. 2.338 5.649 ↓ 549.8 12,096 1

Hash (cost=98.21..98.21 rows=22 width=24) (actual time=5.649..5.649 rows=12,096 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 520kB
68. 3.311 3.311 ↓ 549.8 12,096 1

CTE Scan on arhiv_tmp a4 (cost=0.00..98.21 rows=22 width=24) (actual time=0.070..3.311 rows=12,096 loops=1)

  • Filter: (typ_arh = 2)
69. 2.016 4.813 ↓ 549.8 12,096 1

Hash (cost=98.21..98.21 rows=22 width=12) (actual time=4.813..4.813 rows=12,096 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 520kB
70. 2.797 2.797 ↓ 549.8 12,096 1

CTE Scan on arhiv_tmp a2 (cost=0.00..98.21 rows=22 width=12) (actual time=0.013..2.797 rows=12,096 loops=1)

  • Filter: (typ_arh = 2)