explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 85sf

Settings
# exclusive inclusive rows x rows loops node
1. 5.839 157,504.149 ↓ 98.0 98 1

Nested Loop Left Join (cost=82,530.92..283,159.78 rows=1 width=708) (actual time=3,030.528..157,504.149 rows=98 loops=1)

  • Buffers: shared hit=9,917,944 read=882, temp read=221 written=222
2.          

Initplan (for Nested Loop Left Join)

3. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on lookup_taglia (cost=0.00..1.06 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1)

  • Filter: (code = 5)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=1
4. 0.002 0.002 ↑ 1.0 1 1

Seq Scan on lookup_taglia lookup_taglia_1 (cost=0.00..1.06 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

  • Filter: (code = 5)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=1
5. 0.713 2,491.499 ↓ 98.0 98 1

Nested Loop Left Join (cost=82,528.65..82,561.81 rows=1 width=656) (actual time=1,351.652..2,491.499 rows=98 loops=1)

  • Buffers: shared hit=287,520 read=215, temp read=221 written=222
6. 0.547 2,436.494 ↓ 98.0 98 1

Nested Loop Left Join (cost=82,528.36..82,561.51 rows=1 width=646) (actual time=1,340.465..2,436.494 rows=98 loops=1)

  • Buffers: shared hit=287,237 read=204, temp read=221 written=222
7. 0.369 1,482.505 ↓ 98.0 98 1

Nested Loop Left Join (cost=82,527.94..82,560.95 rows=1 width=627) (actual time=1,320.531..1,482.505 rows=98 loops=1)

  • Buffers: shared hit=286,990 read=59, temp read=221 written=222
8. 0.391 1,481.646 ↓ 98.0 98 1

Nested Loop (cost=82,527.80..82,560.79 rows=1 width=115) (actual time=1,320.504..1,481.646 rows=98 loops=1)

  • Buffers: shared hit=286,795 read=58, temp read=221 written=222
9. 0.560 1,365.485 ↓ 102.0 102 1

Nested Loop (cost=82,527.38..82,560.33 rows=1 width=85) (actual time=1,320.490..1,365.485 rows=102 loops=1)

  • Buffers: shared hit=286,399 read=46, temp read=221 written=222
10. 0.572 1,322.085 ↓ 102.0 102 1

Merge Right Join (cost=82,526.96..82,557.79 rows=1 width=77) (actual time=1,320.472..1,322.085 rows=102 loops=1)

  • Merge Cond: (a.id_rel_stab_lp = rel.id)
  • Buffers: shared hit=286,004 read=32, temp read=221 written=222
11. 0.563 1,312.881 ↑ 3.4 124 1

Subquery Scan on a (cost=82,483.77..82,513.52 rows=425 width=44) (actual time=1,311.979..1,312.881 rows=124 loops=1)

  • Buffers: shared hit=285,790 read=31, temp read=221 written=222
12. 0.000 1,312.318 ↑ 3.4 124 1

Unique (cost=82,483.77..82,493.33 rows=425 width=574) (actual time=1,311.948..1,312.318 rows=124 loops=1)

  • Buffers: shared hit=285,788 read=31, temp read=221 written=222
13.          

Initplan (for Unique)

14. 0.002 0.002 ↑ 1.0 1 1

Seq Scan on lookup_taglia t2 (cost=0.00..1.06 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

  • Filter: (code = 1)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=1
15. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on lookup_taglia t2_1 (cost=0.00..1.06 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1)

  • Filter: (code = 1)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=1
16. 13.992 13.992 ↑ 2,550.0 1 1

Seq Scan on limite_numero_cani_vivi (cost=0.00..35.50 rows=2,550 width=4) (actual time=13.990..13.992 rows=1 loops=1)

  • Buffers: shared read=1
17. 0.166 1,312.030 ↑ 3.4 124 1

Sort (cost=82,446.14..82,447.20 rows=425 width=574) (actual time=1,311.947..1,312.030 rows=124 loops=1)

  • Sort Key: rel_1.id, asl_1.code, asl_1.description, o_1.ragione_sociale, c_1.mq_disponibili, (sum(CASE WHEN ((a_1.data_nascita + '8 mons'::interval) < now()) THEN t.occupazione ELSE $6 END)), (((sum(CASE WHEN ((a_1.data_nascita + '8 mons'::interval) < now()) THEN t.occupazione ELSE $7 END) / CASE WHEN (c_1.mq_disponibili = 0) THEN NULL::double precision ELSE (c_1.mq_disponibili)::double precision END) * '100'::double precision)), (count(*))
  • Sort Method: quicksort Memory: 44kB
  • Buffers: shared hit=285,788 read=31, temp read=221 written=222
18. 28.501 1,311.864 ↑ 3.3 127 1

GroupAggregate (cost=82,404.21..82,427.59 rows=425 width=574) (actual time=1,293.783..1,311.864 rows=127 loops=1)

  • Group Key: rel_1.id, asl_1.code, o_1.ragione_sociale, c_1.mq_disponibili
  • Buffers: shared hit=285,788 read=31, temp read=221 written=222
19. 46.985 1,283.363 ↓ 45.2 19,206 1

Sort (cost=82,404.21..82,405.27 rows=425 width=562) (actual time=1,279.403..1,283.363 rows=19,206 loops=1)

  • Sort Key: rel_1.id, asl_1.code, o_1.ragione_sociale, c_1.mq_disponibili
  • Sort Method: external merge Disk: 1,768kB
  • Buffers: shared hit=285,786 read=30, temp read=221 written=222
20. 30.982 1,236.378 ↓ 45.2 19,206 1

Gather (cost=1,036.95..82,385.66 rows=425 width=562) (actual time=3.644..1,236.378 rows=19,206 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=285,786 read=30
21. 3.792 1,205.396 ↓ 38.4 9,603 2 / 2

Hash Left Join (cost=36.95..81,343.16 rows=250 width=562) (actual time=0.979..1,205.396 rows=9,603 loops=2)

  • Hash Cond: (stab.id_asl = asl_1.code)
  • Buffers: shared hit=285,786 read=30
22. 4.825 1,201.584 ↓ 38.4 9,603 2 / 2

Hash Left Join (cost=35.43..81,340.86 rows=250 width=46) (actual time=0.945..1,201.584 rows=9,603 loops=2)

  • Hash Cond: (rel_1.id = c_1.id_relazione_stabilimento_linea_produttiva)
  • Buffers: shared hit=285,784 read=30
23. 13.739 1,196.645 ↓ 38.4 9,603 2 / 2

Nested Loop Left Join (cost=25.55..81,330.03 rows=250 width=42) (actual time=0.818..1,196.645 rows=9,603 loops=2)

  • Buffers: shared hit=285,774 read=30
24. 11.078 1,134.891 ↓ 38.4 9,603 2 / 2

Hash Left Join (cost=25.12..81,214.63 rows=250 width=46) (actual time=0.791..1,134.891 rows=9,603 loops=2)

  • Hash Cond: (a_1.id_taglia = t.code)
  • Buffers: shared hit=208,952 read=24
25. 15.890 1,123.793 ↓ 38.4 9,603 2 / 2

Nested Loop Left Join (cost=24.01..81,212.47 rows=250 width=42) (actual time=0.759..1,123.793 rows=9,603 loops=2)

  • Filter: (o_1.trashed_date IS NULL)
  • Rows Removed by Filter: 410
  • Buffers: shared hit=208,950 read=24
26. 15.648 987.753 ↓ 39.7 10,012 2 / 2

Nested Loop Left Join (cost=23.59..81,093.82 rows=252 width=24) (actual time=0.733..987.753 rows=10,012 loops=2)

  • Buffers: shared hit=128,863 read=10
27. 80.797 892.005 ↓ 39.7 10,012 2 / 2

Hash Join (cost=23.17..80,453.41 rows=252 width=20) (actual time=0.699..892.005 rows=10,012 loops=2)

  • Hash Cond: (a_1.id_detentore = rel_1.id)
  • Buffers: shared hit=48,719
28. 810.698 810.698 ↑ 1.4 399,870 2 / 2

Parallel Seq Scan on animale a_1 (cost=0.00..78,944.21 rows=566,101 width=16) (actual time=0.013..810.698 rows=399,870 loops=2)

  • Filter: ((data_cancellazione IS NULL) AND (trashed_date IS NULL) AND (id > 0) AND (id_specie = 1) AND ((flag_furto IS FALSE) OR (stato <> ALL ('{52,4,17,16,18,21,43}'::integer[]))) AND ((flag_smarrimento IS FALSE) OR (stato <> ALL ('{13,15,12,14,44,55,74,78}'::integer[]))) AND ((flag_decesso IS FALSE) OR (stato <> ALL ('{39,10,11,24,40,7,38,41,45,54,73,82}'::integer[]))))
  • Rows Removed by Filter: 131,685
  • Buffers: shared hit=48,458
29. 0.047 0.510 ↓ 1.0 231 2 / 2

Hash (cost=20.38..20.38 rows=223 width=8) (actual time=0.510..0.510 rows=231 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=217
30. 0.463 0.463 ↓ 1.0 231 2 / 2

Index Scan using opu_relazione_stabilimento_linee_produt_id_linea_produttiva_idx on opu_relazione_stabilimento_linee_produttive rel_1 (cost=0.42..20.38 rows=223 width=8) (actual time=0.036..0.463 rows=231 loops=2)

  • Index Cond: (id_linea_produttiva = 5)
  • Filter: (trashed_date IS NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=217
31. 80.100 80.100 ↑ 1.0 1 20,025 / 2

Index Scan using opu_stabilimento_id_idx on opu_stabilimento stab (cost=0.42..2.54 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=20,025)

  • Index Cond: (rel_1.id_stabilimento = id)
  • Buffers: shared hit=80,144 read=10
32. 120.150 120.150 ↑ 1.0 1 20,025 / 2

Index Scan using opu_operatore_id_idx on opu_operatore o_1 (cost=0.42..0.46 rows=1 width=30) (actual time=0.012..0.012 rows=1 loops=20,025)

  • Index Cond: (stab.id_operatore = id)
  • Buffers: shared hit=80,087 read=14
33. 0.008 0.020 ↑ 1.0 5 2 / 2

Hash (cost=1.05..1.05 rows=5 width=12) (actual time=0.019..0.020 rows=5 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
34. 0.012 0.012 ↑ 1.0 5 2 / 2

Seq Scan on lookup_taglia t (cost=0.00..1.05 rows=5 width=12) (actual time=0.010..0.012 rows=5 loops=2)

  • Buffers: shared hit=2
35. 48.015 48.015 ↑ 1.0 1 19,206 / 2

Index Scan using opu_rel_operatore_soggetto_fisico_id_operatore_idx on opu_rel_operatore_soggetto_fisico rels (cost=0.42..0.45 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=19,206)

  • Index Cond: (id_operatore = o_1.id)
  • Buffers: shared hit=76,822 read=6
36. 0.042 0.114 ↑ 1.0 217 2 / 2

Hash (cost=7.17..7.17 rows=217 width=8) (actual time=0.114..0.114 rows=217 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=10
37. 0.072 0.072 ↑ 1.0 217 2 / 2

Seq Scan on opu_informazioni_canile c_1 (cost=0.00..7.17 rows=217 width=8) (actual time=0.010..0.072 rows=217 loops=2)

  • Buffers: shared hit=10
38. 0.008 0.020 ↑ 1.0 23 2 / 2

Hash (cost=1.23..1.23 rows=23 width=520) (actual time=0.020..0.020 rows=23 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=2
39. 0.012 0.012 ↑ 1.0 23 2 / 2

Seq Scan on lookup_site_id asl_1 (cost=0.00..1.23 rows=23 width=520) (actual time=0.008..0.012 rows=23 loops=2)

  • Buffers: shared hit=2
40. 0.198 8.632 ↓ 102.0 102 1

Sort (cost=43.19..43.20 rows=1 width=37) (actual time=8.481..8.632 rows=102 loops=1)

  • Sort Key: rel.id
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=214 read=1
41. 0.183 8.434 ↓ 102.0 102 1

Nested Loop Left Join (cost=8.37..43.18 rows=1 width=37) (actual time=7.432..8.434 rows=102 loops=1)

  • Join Filter: (blocco.id_canile = rel.id)
  • Rows Removed by Join Filter: 1,929
  • Buffers: shared hit=214 read=1
42. 0.055 0.397 ↓ 102.0 102 1

Hash Join (cost=8.37..29.17 rows=1 width=17) (actual time=0.135..0.397 rows=102 loops=1)

  • Hash Cond: (rel.id = info.id_relazione_stabilimento_linea_produttiva)
  • Buffers: shared hit=113
43. 0.254 0.254 ↓ 1.0 231 1

Index Scan using opu_relazione_stabilimento_linee_produt_id_linea_produttiva_idx on opu_relazione_stabilimento_linee_produttive rel (cost=0.42..20.38 rows=223 width=16) (actual time=0.027..0.254 rows=231 loops=1)

  • Index Cond: (id_linea_produttiva = 5)
  • Filter: (trashed_date IS NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=108
44. 0.018 0.088 ↓ 1.6 102 1

Hash (cost=7.17..7.17 rows=62 width=5) (actual time=0.088..0.088 rows=102 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=5
45. 0.070 0.070 ↓ 1.6 102 1

Seq Scan on opu_informazioni_canile info (cost=0.00..7.17 rows=62 width=5) (actual time=0.011..0.070 rows=102 loops=1)

  • Filter: (flag_canile AND (flag_pubblico OR flag_privato))
  • Rows Removed by Filter: 115
  • Buffers: shared hit=5
46. 7.854 7.854 ↓ 19.0 19 102

Seq Scan on blocco_sblocco_canile blocco (cost=0.00..14.00 rows=1 width=24) (actual time=0.072..0.077 rows=19 loops=102)

  • Filter: ((trashed IS NOT TRUE) AND (trashed_date IS NULL))
  • Rows Removed by Filter: 10
  • Buffers: shared hit=101 read=1
47. 42.840 42.840 ↑ 1.0 1 102

Index Scan using opu_stabilimento_id_idx on opu_stabilimento s (cost=0.42..2.54 rows=1 width=16) (actual time=0.420..0.420 rows=1 loops=102)

  • Index Cond: (id = rel.id_stabilimento)
  • Buffers: shared hit=395 read=14
48. 115.770 115.770 ↑ 1.0 1 102

Index Scan using opu_operatore_id_idx on opu_operatore o (cost=0.42..0.46 rows=1 width=38) (actual time=1.135..1.135 rows=1 loops=102)

  • Index Cond: (id = s.id_operatore)
  • Filter: (trashed_date IS NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=396 read=12
49. 0.490 0.490 ↑ 1.0 1 98

Index Scan using lookup_site_id_pkey on lookup_site_id asl (cost=0.14..0.16 rows=1 width=520) (actual time=0.005..0.005 rows=1 loops=98)

  • Index Cond: (code = s.id_asl)
  • Buffers: shared hit=195 read=1
50. 953.442 953.442 ↑ 1.0 1 98

Index Scan using opu_indirizzo_id_idx on opu_indirizzo i (cost=0.42..0.56 rows=1 width=27) (actual time=9.729..9.729 rows=1 loops=98)

  • Index Cond: (id = s.id_indirizzo)
  • Buffers: shared hit=247 read=145
51. 54.292 54.292 ↑ 1.0 1 98

Index Scan using comuni1_id_idx on comuni1 c (cost=0.28..0.30 rows=1 width=18) (actual time=0.554..0.554 rows=1 loops=98)

  • Index Cond: (id = i.comune)
  • Buffers: shared hit=283 read=11
52. 16.072 16.072 ↑ 1.0 1 98

Index Scan using lookup_province_pkey on lookup_province p (cost=0.15..0.17 rows=1 width=13) (actual time=0.164..0.164 rows=1 loops=98)

  • Index Cond: (code = (c.cod_provincia)::integer)
  • Buffers: shared hit=193 read=2
53.          

SubPlan (for Nested Loop Left Join)

54. 1.862 78,572.284 ↑ 1.0 1 98

Aggregate (cost=100,297.81..100,297.82 rows=1 width=8) (actual time=801.758..801.758 rows=1 loops=98)

  • Buffers: shared hit=4,814,808 read=665
55. 20.111 78,570.422 ↓ 2.4 12 98

Nested Loop (cost=0.42..100,297.80 rows=5 width=0) (actual time=340.814..801.739 rows=12 loops=98)

  • Buffers: shared hit=4,814,808 read=665
56. 76,571.222 76,571.222 ↓ 34.0 170 98

Seq Scan on animale a_2 (cost=0.00..100,284.56 rows=5 width=4) (actual time=201.979..781.339 rows=170 loops=98)

  • Filter: ((data_cancellazione IS NULL) AND (trashed_date IS NULL) AND (id_detentore = rel.id) AND (id_specie = 1) AND ((flag_furto IS FALSE) OR (stato <> ALL ('{52,4,17,16,18,21,43}'::integer[]))) AND ((flag_smarrimento IS FALSE) OR (stato <> ALL ('{13,15,12,14,44,55,74,78}'::integer[]))) AND ((flag_decesso IS FALSE) OR (stato <> ALL ('{39,10,11,24,40,7,38,41,45,54,73,82}'::integer[]))))
  • Rows Removed by Filter: 1,062,939
  • Buffers: shared hit=4,748,884
57. 1,979.089 1,979.089 ↓ 0.0 0 16,631

Index Scan using opu_relazione_stabilimento_linee_produttive_id_idx on opu_relazione_stabilimento_linee_produttive rel_proprietario (cost=0.42..2.65 rows=1 width=4) (actual time=0.119..0.119 rows=0 loops=16,631)

  • Index Cond: (id = a_2.id_proprietario)
  • Filter: (id_linea_produttiva = ANY ('{1,4,5,6,8}'::integer[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=65,924 read=665
58. 5.978 76,418.440 ↑ 1.0 1 98

Aggregate (cost=100,297.80..100,297.81 rows=1 width=8) (actual time=779.780..779.780 rows=1 loops=98)

  • Buffers: shared hit=4,815,423
59. 25.400 76,412.462 ↓ 154.0 154 98

Nested Loop (cost=0.42..100,297.80 rows=1 width=0) (actual time=269.267..779.719 rows=154 loops=98)

  • Buffers: shared hit=4,815,423
60. 76,320.538 76,320.538 ↓ 34.0 170 98

Seq Scan on animale a_3 (cost=0.00..100,284.56 rows=5 width=4) (actual time=202.188..778.781 rows=170 loops=98)

  • Filter: ((data_cancellazione IS NULL) AND (trashed_date IS NULL) AND (id_detentore = rel.id) AND (id_specie = 1) AND ((flag_furto IS FALSE) OR (stato <> ALL ('{52,4,17,16,18,21,43}'::integer[]))) AND ((flag_smarrimento IS FALSE) OR (stato <> ALL ('{13,15,12,14,44,55,74,78}'::integer[]))) AND ((flag_decesso IS FALSE) OR (stato <> ALL ('{39,10,11,24,40,7,38,41,45,54,73,82}'::integer[]))))
  • Rows Removed by Filter: 1,062,939
  • Buffers: shared hit=4,748,884
61. 66.524 66.524 ↑ 1.0 1 16,631

Index Scan using opu_relazione_stabilimento_linee_produttive_id_idx on opu_relazione_stabilimento_linee_produttive rel_proprietario_1 (cost=0.42..2.64 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=16,631)

  • Index Cond: (id = a_3.id_proprietario)
  • Filter: (id_linea_produttiva = 3)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=66,539
Planning time : 11.728 ms
Execution time : 157,508.693 ms