explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GSTn

Settings
# exclusive inclusive rows x rows loops node
1. 0.054 601.215 ↑ 1.0 335 1

Subquery Scan on results (cost=124,398.25..124,402.43 rows=335 width=124) (actual time=601.149..601.215 rows=335 loops=1)

2.          

CTE arhiv_tmp

3. 0.314 582.141 ↓ 7.4 1,594 1

Unique (cost=91,633.47..91,648.61 rows=214 width=36) (actual time=581.758..582.141 rows=1,594 loops=1)

4. 1.062 581.827 ↓ 1.4 2,153 1

Sort (cost=91,633.47..91,637.25 rows=1,514 width=36) (actual time=581.756..581.827 rows=2,153 loops=1)

  • Sort Key: arh.pr_id, arh."DateValue
  • Sort Method: quicksort Memory: 265kB
5. 0.734 580.765 ↓ 1.4 2,153 1

Hash Semi Join (cost=91,422.64..91,553.49 rows=1,514 width=36) (actual time=578.897..580.765 rows=2,153 loops=1)

  • Hash Cond: (arh.pr_id = p.prp_id)
6. 0.273 577.656 ↓ 1.3 2,861 1

Merge Left Join (cost=91,200.94..91,307.38 rows=2,133 width=36) (actual time=576.515..577.656 rows=2,861 loops=1)

  • Merge Cond: (arh.pr_id = arangcontract3.prp_id)
7. 0.281 577.374 ↓ 1.3 2,861 1

Merge Left Join (cost=91,041.98..91,114.71 rows=2,133 width=32) (actual time=576.505..577.374 rows=2,861 loops=1)

  • Merge Cond: (arh.pr_id = aranguser3.prp_id)
8. 0.390 577.090 ↓ 1.3 2,861 1

Merge Left Join (cost=90,883.01..90,922.05 rows=2,133 width=28) (actual time=576.500..577.090 rows=2,861 loops=1)

  • Merge Cond: (arh.pr_id = arangmetrol3.prp_id)
9. 1.473 576.686 ↓ 1.3 2,861 1

Sort (cost=90,724.05..90,729.38 rows=2,133 width=24) (actual time=576.484..576.686 rows=2,861 loops=1)

  • Sort Key: arh.pr_id
  • Sort Method: quicksort Memory: 320kB
10. 575.213 575.213 ↓ 1.3 2,861 1

Seq Scan on "Arhiv_cnt" arh (cost=0.00..90,606.11 rows=2,133 width=24) (actual time=139.205..575.213 rows=2,861 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.013 0.014 ↓ 0.0 0 1

Sort (cost=158.96..164.31 rows=2,140 width=8) (actual time=0.014..0.014 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.008 0.009 ↓ 0.0 0 1

Sort (cost=158.96..164.31 rows=2,140 width=8) (actual time=0.009..0.009 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.491 2.375 ↓ 1.3 2,376 1

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

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

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
30. 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))
31. 1.500 601.161 ↑ 1.0 335 1

Sort (cost=32,749.64..32,750.48 rows=335 width=82) (actual time=601.148..601.161 rows=335 loops=1)

  • Sort Key: (max(((pt."Name" || ' '::text) || p."Name")))
  • Sort Method: quicksort Memory: 72kB
32. 8.000 599.661 ↑ 1.0 335 1

HashAggregate (cost=32,732.24..32,735.59 rows=335 width=82) (actual time=599.539..599.661 rows=335 loops=1)

33. 1.065 591.661 ↑ 70.3 4,764 1

Hash Left Join (cost=576.96..19,332.24 rows=335,000 width=82) (actual time=587.446..591.661 rows=4,764 loops=1)

  • Hash Cond: (prp2.prp_id = a2.pr_id)
34. 0.666 590.083 ↑ 114.4 2,928 1

Merge Right Join (cost=572.13..18,070.46 rows=335,000 width=78) (actual time=586.923..590.083 rows=2,928 loops=1)

  • Merge Cond: (prp3.plc_id = p.plc_id)
35. 0.182 583.526 ↓ 1.5 489 1

Sort (cost=115.76..116.60 rows=336 width=28) (actual time=583.494..583.526 rows=489 loops=1)

  • Sort Key: prp3.plc_id
  • Sort Method: quicksort Memory: 47kB
36. 0.108 583.344 ↓ 1.5 489 1

Hash Left Join (cost=4.83..101.66 rows=336 width=28) (actual time=582.839..583.344 rows=489 loops=1)

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

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

  • Filter: ("ParamRes_id" = 2)
38. 0.266 582.829 ↓ 1,594.0 1,594 1

Hash (cost=4.81..4.81 rows=1 width=24) (actual time=582.829..582.829 rows=1,594 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 69kB
39. 582.563 582.563 ↓ 1,594.0 1,594 1

CTE Scan on arhiv_tmp a3 (cost=0.00..4.81 rows=1 width=24) (actual time=581.764..582.563 rows=1,594 loops=1)

  • Filter: (typ_arh = 2)
40. 0.532 5.891 ↑ 114.4 2,928 1

Materialize (cost=456.37..13,852.91 rows=335,000 width=54) (actual time=3.426..5.891 rows=2,928 loops=1)

41. 0.531 5.359 ↑ 166.7 2,010 1

Merge Left Join (cost=456.37..13,015.41 rows=335,000 width=54) (actual time=3.424..5.359 rows=2,010 loops=1)

  • Merge Cond: (p.plc_id = prp4.plc_id)
42. 0.056 3.592 ↑ 166.7 2,010 1

Nested Loop (cost=340.67..7,182.86 rows=335,000 width=30) (actual time=2.305..3.592 rows=2,010 loops=1)

43. 0.057 3.201 ↑ 1.0 335 1

Nested Loop Left Join (cost=340.42..482.61 rows=335 width=30) (actual time=2.265..3.201 rows=335 loops=1)

44. 0.070 2.474 ↑ 1.0 335 1

Merge Left Join (cost=340.42..383.08 rows=335 width=27) (actual time=2.257..2.474 rows=335 loops=1)

  • Merge Cond: (p.plc_id = pchild.place_id)
45. 0.136 2.355 ↑ 1.0 335 1

Merge Left Join (cost=340.42..347.03 rows=335 width=27) (actual time=2.175..2.355 rows=335 loops=1)

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

Sort (cost=230.75..231.59 rows=335 width=23) (actual time=1.693..1.717 rows=335 loops=1)

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

Hash Join (cost=112.71..216.70 rows=335 width=23) (actual time=0.625..1.570 rows=335 loops=1)

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

Hash Right Join (cost=111.26..210.65 rows=335 width=16) (actual time=0.610..1.450 rows=335 loops=1)

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

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

  • Hash Cond: (pr1.prp_id = prp1.prp_id)
50. 0.267 0.267 ↑ 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.267 rows=4,764 loops=1)

51. 0.050 0.436 ↑ 1.0 336 1

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

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

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

  • Filter: ("ParamRes_id" = 2)
53. 0.047 0.103 ↑ 1.0 335 1

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

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

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

  • Filter: (place_id = 338)
55. 0.004 0.008 ↑ 1.0 20 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
56. 0.004 0.004 ↑ 1.0 20 1

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

57. 0.120 0.502 ↑ 1.0 336 1

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

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

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

  • Filter: ("ParamRes_id" = 2)
59. 0.049 0.049 ↑ 1.0 345 1

Index Scan using "Places_cnt_place_id_plc_id_typ_id_idx" on "Places_cnt" pchild (cost=0.00..31.00 rows=345 width=4) (actual time=0.009..0.049 rows=345 loops=1)

60. 0.670 0.670 ↑ 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.001..0.002 rows=1 loops=335)

  • Index Cond: (pr1.dev_id = dev_id)
61. 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)

62. 0.226 1.236 ↓ 6.0 2,005 1

Sort (cost=115.71..116.54 rows=335 width=28) (actual time=1.118..1.236 rows=2,005 loops=1)

  • Sort Key: prp4.plc_id
  • Sort Method: quicksort Memory: 40kB
63. 0.059 1.010 ↑ 1.0 335 1

Hash Left Join (cost=4.83..101.66 rows=335 width=28) (actual time=0.851..1.010 rows=335 loops=1)

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

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

  • Filter: ("ParamRes_id" = 995)
65. 0.290 0.573 ↓ 1,594.0 1,594 1

Hash (cost=4.81..4.81 rows=1 width=24) (actual time=0.573..0.573 rows=1,594 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 69kB
66. 0.283 0.283 ↓ 1,594.0 1,594 1

CTE Scan on arhiv_tmp a4 (cost=0.00..4.81 rows=1 width=24) (actual time=0.002..0.283 rows=1,594 loops=1)

  • Filter: (typ_arh = 2)
67. 0.251 0.513 ↓ 1,594.0 1,594 1

Hash (cost=4.81..4.81 rows=1 width=12) (actual time=0.513..0.513 rows=1,594 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 69kB
68. 0.262 0.262 ↓ 1,594.0 1,594 1

CTE Scan on arhiv_tmp a2 (cost=0.00..4.81 rows=1 width=12) (actual time=0.001..0.262 rows=1,594 loops=1)

  • Filter: (typ_arh = 2)