explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8beF

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5.244 152,666.563 ↓ 98.0 98 1

Nested Loop Left Join (cost=82,530.92..283,159.78 rows=1 width=708) (actual time=2,346.763..152,666.563 rows=98 loops=1)

  • Buffers: shared hit=9,918,838, temp read=221 written=222
2.          

Initplan (for Nested Loop Left Join)

3. 0.008 0.008 ↑ 1.0 1 1

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

  • Filter: (code = 5)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=1
4. 0.003 0.003 ↑ 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.003 rows=1 loops=1)

  • Filter: (code = 5)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=1
5. 0.642 763.464 ↓ 98.0 98 1

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

  • Buffers: shared hit=287,747, temp read=221 written=222
6. 0.441 762.038 ↓ 98.0 98 1

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

  • Buffers: shared hit=287,453, temp read=221 written=222
7. 0.681 758.853 ↓ 98.0 98 1

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

  • Buffers: shared hit=287,061, temp read=221 written=222
8. 0.433 757.486 ↓ 98.0 98 1

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

  • Buffers: shared hit=286,865, temp read=221 written=222
9. 0.565 756.033 ↓ 102.0 102 1

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

  • Buffers: shared hit=286,457, temp read=221 written=222
10. 0.584 753.938 ↓ 102.0 102 1

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

  • Merge Cond: (a.id_rel_stab_lp = rel.id)
  • Buffers: shared hit=286,048, temp read=221 written=222
11. 0.504 751.885 ↑ 3.4 124 1

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

  • Buffers: shared hit=285,833, temp read=221 written=222
12. 0.260 751.381 ↑ 3.4 124 1

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

  • Buffers: shared hit=285,831, 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.006 0.006 ↑ 1.0 1 1

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

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

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

  • Buffers: shared hit=1
17. 0.422 751.093 ↑ 3.4 124 1

Sort (cost=82,446.14..82,447.20 rows=425 width=574) (actual time=751.022..751.093 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,831, temp read=221 written=222
18. 14.777 750.671 ↑ 3.3 127 1

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

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

Sort (cost=82,404.21..82,405.27 rows=425 width=562) (actual time=731.997..735.894 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,819, temp read=221 written=222
20. 2.304 700.420 ↓ 45.2 19,206 1

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

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=285,819
21. 4.973 698.116 ↓ 38.4 9,603 2 / 2

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

  • Hash Cond: (stab.id_asl = asl_1.code)
  • Buffers: shared hit=285,819
22. 4.658 693.113 ↓ 38.4 9,603 2 / 2

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

  • Hash Cond: (rel_1.id = c_1.id_relazione_stabilimento_linea_produttiva)
  • Buffers: shared hit=285,817
23. 1.694 688.041 ↓ 38.4 9,603 2 / 2

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

  • Buffers: shared hit=285,807
24. 3.934 657.538 ↓ 38.4 9,603 2 / 2

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

  • Hash Cond: (a_1.id_taglia = t.code)
  • Buffers: shared hit=208,979
25. 2.435 653.579 ↓ 38.4 9,603 2 / 2

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

  • Filter: (o_1.trashed_date IS NULL)
  • Rows Removed by Filter: 410
  • Buffers: shared hit=208,977
26. 4.229 621.106 ↓ 39.7 10,012 2 / 2

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

  • Buffers: shared hit=128,876
27. 51.579 586.839 ↓ 39.7 10,012 2 / 2

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

  • Hash Cond: (a_1.id_detentore = rel_1.id)
  • Buffers: shared hit=48,722
28. 534.168 534.168 ↑ 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.021..534.168 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.063 1.092 ↓ 1.0 231 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=217
30. 1.029 1.029 ↓ 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.033..1.029 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. 30.038 30.038 ↑ 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.003..0.003 rows=1 loops=20,025)

  • Index Cond: (rel_1.id_stabilimento = id)
  • Buffers: shared hit=80,154
32. 30.038 30.038 ↑ 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.003..0.003 rows=1 loops=20,025)

  • Index Cond: (stab.id_operatore = id)
  • Buffers: shared hit=80,101
33. 0.007 0.025 ↑ 1.0 5 2 / 2

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

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

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

  • Buffers: shared hit=2
35. 28.809 28.809 ↑ 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.002..0.003 rows=1 loops=19,206)

  • Index Cond: (id_operatore = o_1.id)
  • Buffers: shared hit=76,828
36. 0.056 0.414 ↑ 1.0 217 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=10
37. 0.358 0.358 ↑ 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.016..0.358 rows=217 loops=2)

  • Buffers: shared hit=10
38. 0.011 0.030 ↑ 1.0 23 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=2
39. 0.019 0.019 ↑ 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.015..0.019 rows=23 loops=2)

  • Buffers: shared hit=2
40. 0.212 1.469 ↓ 102.0 102 1

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

  • Sort Key: rel.id
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=215
41. 0.261 1.257 ↓ 102.0 102 1

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

  • Join Filter: (blocco.id_canile = rel.id)
  • Rows Removed by Join Filter: 1,929
  • Buffers: shared hit=215
42. 0.051 0.384 ↓ 102.0 102 1

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

  • Hash Cond: (rel.id = info.id_relazione_stabilimento_linea_produttiva)
  • Buffers: shared hit=113
43. 0.253 0.253 ↓ 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.024..0.253 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.016 0.080 ↓ 1.6 102 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=5
45. 0.064 0.064 ↓ 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.064 rows=102 loops=1)

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

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

  • Filter: ((trashed IS NOT TRUE) AND (trashed_date IS NULL))
  • Rows Removed by Filter: 10
  • Buffers: shared hit=102
47. 1.530 1.530 ↑ 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.015..0.015 rows=1 loops=102)

  • Index Cond: (id = rel.id_stabilimento)
  • Buffers: shared hit=409
48. 1.020 1.020 ↑ 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=0.010..0.010 rows=1 loops=102)

  • Index Cond: (id = s.id_operatore)
  • Filter: (trashed_date IS NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=408
49. 0.686 0.686 ↑ 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.007..0.007 rows=1 loops=98)

  • Index Cond: (code = s.id_asl)
  • Buffers: shared hit=196
50. 2.744 2.744 ↑ 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=0.028..0.028 rows=1 loops=98)

  • Index Cond: (id = s.id_indirizzo)
  • Buffers: shared hit=392
51. 0.784 0.784 ↑ 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.008..0.008 rows=1 loops=98)

  • Index Cond: (id = i.comune)
  • Buffers: shared hit=294
52. 0.490 0.490 ↑ 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.005..0.005 rows=1 loops=98)

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

SubPlan (for Nested Loop Left Join)

54. 1.764 75,972.442 ↑ 1.0 1 98

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

  • Buffers: shared hit=4,815,473
55. 15.237 75,970.678 ↓ 2.4 12 98

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

  • Buffers: shared hit=4,815,473
56. 75,872.286 75,872.286 ↓ 34.0 170 98

Seq Scan on animale a_2 (cost=0.00..100,284.56 rows=5 width=4) (actual time=199.265..774.207 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. 83.155 83.155 ↓ 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.005..0.005 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=66,589
58. 6.076 75,924.912 ↑ 1.0 1 98

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

  • Buffers: shared hit=4,815,423
59. 29.320 75,918.836 ↓ 154.0 154 98

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

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

Seq Scan on animale a_3 (cost=0.00..100,284.56 rows=5 width=4) (actual time=200.145..773.704 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 : 17.902 ms
Execution time : 152,668.639 ms