explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3HOx

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 36.455 125.869 ↑ 6.8 2,991 1

GroupAggregate (cost=4,940.93..8,914.84 rows=20,379 width=797) (actual time=88.544..125.869 rows=2,991 loops=1)

  • Group Key: o.id, (COALESCE(a.id, c.id)), (COALESCE(a.c_num, c.c_num)), ntr.id, rt.name, crn.id, c.farming_id, po.path, reps.rep_name, reps.rep_surname, reps.rep_lastname, reps.rep_egn, o.rent_place
2. 43.024 89.414 ↑ 1.2 17,363 1

Sort (cost=4,940.93..4,991.88 rows=20,379 width=797) (actual time=88.487..89.414 rows=17,363 loops=1)

  • Sort Key: o.id, (COALESCE(a.id, c.id)), (COALESCE(a.c_num, c.c_num)), ntr.id, rt.name, crn.id, c.farming_id, po.path, reps.rep_name, reps.rep_surname, reps.rep_lastname, reps.rep_egn, o.rent_place
  • Sort Method: quicksort Memory: 5499kB
3. 3.364 46.390 ↑ 1.2 17,363 1

Hash Left Join (cost=2,543.37..3,482.33 rows=20,379 width=797) (actual time=19.003..46.390 rows=17,363 loops=1)

  • Hash Cond: ((cr.id = crn.renta_id) AND (ntr.renta_id = crn.nat_type))
4. 3.001 43.019 ↑ 1.2 17,363 1

Hash Left Join (cost=2,542.02..3,328.09 rows=20,379 width=776) (actual time=18.991..43.019 rows=17,363 loops=1)

  • Hash Cond: (COALESCE(a.id, c.id) = ntr.contract_id)
5. 2.706 40.004 ↑ 1.2 17,363 1

Hash Left Join (cost=2,539.82..3,244.39 rows=20,379 width=244) (actual time=18.973..40.004 rows=17,363 loops=1)

  • Hash Cond: ((pc.id = pu.pc_rel_id) AND (po.owner_id = pu.owner_id))
6. 5.436 37.298 ↑ 1.2 17,363 1

Hash Join (cost=2,515.98..3,067.69 rows=20,379 width=248) (actual time=18.962..37.298 rows=17,363 loops=1)

  • Hash Cond: (po.owner_id = o.id)
7. 12.737 30.537 ↑ 1.2 17,363 1

Hash Right Join (cost=2,403.11..2,649.13 rows=20,379 width=128) (actual time=17.632..30.537 rows=17,363 loops=1)

  • Hash Cond: ((cr.plot_id = pc.plot_id) AND (cr.contract_id = COALESCE(a.id, c.id)))
  • Join Filter: ((cr.owner_id = po.owner_id) OR (((cr.owner_id)::text)::ltree @> po.path))
  • Rows Removed by Join Filter: 21849
8. 0.183 0.260 ↑ 1.0 1,464 1

Bitmap Heap Scan on su_charged_renta cr (cost=26.62..88.95 rows=1,466 width=20) (actual time=0.083..0.260 rows=1,464 loops=1)

  • Recheck Cond: (year = 9)
  • Heap Blocks: exact=29
9. 0.077 0.077 ↑ 1.0 1,464 1

Bitmap Index Scan on su_charged_renta_year_idx (cost=0.00..26.26 rows=1,466 width=0) (actual time=0.077..0.077 rows=1,464 loops=1)

  • Index Cond: (year = 9)
10. 4.643 17.540 ↑ 1.2 17,363 1

Hash (cost=2,070.80..2,070.80 rows=20,379 width=124) (actual time=17.540..17.540 rows=17,363 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 2583kB
11. 6.589 12.897 ↑ 1.2 17,363 1

Hash Join (cost=619.26..2,070.80 rows=20,379 width=124) (actual time=3.394..12.897 rows=17,363 loops=1)

  • Hash Cond: (po.pc_rel_id = pc.id)
12. 2.920 2.920 ↑ 1.0 39,586 1

Seq Scan on su_plots_owners_rel po (cost=0.00..950.86 rows=39,586 width=56) (actual time=0.002..2.920 rows=39,586 loops=1)

13. 0.242 3.388 ↑ 3.5 1,213 1

Hash (cost=565.97..565.97 rows=4,263 width=72) (actual time=3.388..3.388 rows=1,213 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 100kB
14. 0.676 3.146 ↑ 3.5 1,213 1

Merge Join (cost=88.95..565.97 rows=4,263 width=72) (actual time=0.883..3.146 rows=1,213 loops=1)

  • Merge Cond: (pc.contract_id = (COALESCE(a.id, c.id)))
15. 1.919 1.919 ↑ 1.0 8,265 1

Index Scan using su_contracts_plots_rel_contract_id_plot_id_key on su_contracts_plots_rel pc (cost=0.29..392.69 rows=8,268 width=20) (actual time=0.007..1.919 rows=8,265 loops=1)

  • Filter: (annex_action = 'added'::annex_action_enum)
  • Rows Removed by Filter: 13
16. 0.109 0.551 ↓ 2.6 1,211 1

Sort (cost=88.66..89.81 rows=461 width=56) (actual time=0.489..0.551 rows=1,211 loops=1)

  • Sort Key: (COALESCE(a.id, c.id))
  • Sort Method: quicksort Memory: 39kB
17. 0.147 0.442 ↑ 2.5 182 1

Hash Left Join (cost=33.45..68.27 rows=461 width=56) (actual time=0.134..0.442 rows=182 loops=1)

  • Hash Cond: (c.id = a.parent_id)
  • Filter: (((c.start_date <= '2018-09-30 00:00:00'::timestamp without time zone) AND (c.due_date >= '2017-10-01 00:00:00'::timestamp without time zone)) OR ((a.start_date <= '2018-09-30 00:00:00'::timestamp without time zone) AND (a.due_date >= '2017-10-01 00:00:00'::timestamp without time zone)))
  • Rows Removed by Filter: 569
18. 0.169 0.169 ↓ 1.0 751 1

Seq Scan on su_contracts c (cost=0.00..31.02 rows=749 width=46) (actual time=0.004..0.169 rows=751 loops=1)

  • Filter: (active AND (NOT is_annex) AND (NOT is_sublease) AND (nm_usage_rights <> ALL ('{1,4}'::integer[])))
  • Rows Removed by Filter: 211
19. 0.001 0.126 ↑ 2.0 1 1

Hash (cost=33.43..33.43 rows=2 width=46) (actual time=0.126..0.126 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
20. 0.125 0.125 ↑ 2.0 1 1

Seq Scan on su_contracts a (cost=0.00..33.43 rows=2 width=46) (actual time=0.094..0.125 rows=1 loops=1)

  • Filter: (is_annex AND active AND (start_date <= '2018-09-30 00:00:00'::timestamp without time zone) AND (due_date >= '2017-10-01 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 961
21. 0.495 1.325 ↑ 1.0 2,086 1

Hash (cost=86.80..86.80 rows=2,086 width=120) (actual time=1.325..1.325 rows=2,086 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 240kB
22. 0.511 0.830 ↑ 1.0 2,086 1

Hash Left Join (cost=18.51..86.80 rows=2,086 width=120) (actual time=0.140..0.830 rows=2,086 loops=1)

  • Hash Cond: (o.id = reps.owner_id)
23. 0.184 0.184 ↑ 1.0 2,086 1

Seq Scan on su_owners o (cost=0.00..56.86 rows=2,086 width=66) (actual time=0.003..0.184 rows=2,086 loops=1)

24. 0.068 0.135 ↑ 1.3 361 1

Hash (cost=12.67..12.67 rows=467 width=58) (actual time=0.135..0.135 rows=361 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
25. 0.067 0.067 ↑ 1.0 467 1

Seq Scan on su_owners_reps reps (cost=0.00..12.67 rows=467 width=58) (actual time=0.002..0.067 rows=467 loops=1)

26. 0.000 0.000 ↓ 0.0 0 1

Hash (cost=23.75..23.75 rows=6 width=12) (actual time=0.000..0.000 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
27. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on su_personal_use pu (cost=0.00..23.75 rows=6 width=12) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (year = 9)
28. 0.001 0.014 ↑ 1.0 5 1

Hash (cost=2.13..2.13 rows=5 width=536) (actual time=0.014..0.014 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
29. 0.007 0.013 ↑ 1.0 5 1

Hash Left Join (cost=1.04..2.13 rows=5 width=536) (actual time=0.011..0.013 rows=5 loops=1)

  • Hash Cond: (ntr.renta_id = rt.id)
30. 0.003 0.003 ↑ 1.0 5 1

Seq Scan on su_contracts_rents ntr (cost=0.00..1.05 rows=5 width=20) (actual time=0.002..0.003 rows=5 loops=1)

  • Filter: (renta_id IS NOT NULL)
31. 0.001 0.003 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=520) (actual time=0.003..0.003 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
32. 0.002 0.002 ↑ 1.0 2 1

Seq Scan on su_renta_types rt (cost=0.00..1.02 rows=2 width=520) (actual time=0.001..0.002 rows=2 loops=1)

33. 0.003 0.007 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=29) (actual time=0.007..0.007 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
34. 0.004 0.004 ↑ 1.0 14 1

Seq Scan on su_charged_renta_natura crn (cost=0.00..1.14 rows=14 width=29) (actual time=0.002..0.004 rows=14 loops=1)