explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2wQK : Optimization for: plan #3HOx

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 38.309 133.997 ↑ 6.8 2,991 1

GroupAggregate (cost=4,940.93..8,914.84 rows=20,379 width=797) (actual time=94.216..133.997 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. 44.693 95.688 ↑ 1.2 17,363 1

Sort (cost=4,940.93..4,991.88 rows=20,379 width=797) (actual time=94.153..95.688 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.542 50.995 ↑ 1.2 17,363 1

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

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

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

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

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

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

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

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

Hash Right Join (cost=2,403.11..2,649.13 rows=20,379 width=128) (actual time=19.108..34.291 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.226 0.320 ↑ 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.104..0.320 rows=1,464 loops=1)

  • Recheck Cond: (year = 9)
  • Heap Blocks: exact=29
9. 0.094 0.094 ↑ 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.094..0.094 rows=1,464 loops=1)

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

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

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

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

  • Hash Cond: (po.pc_rel_id = pc.id)
12. 3.909 3.909 ↑ 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.006..3.909 rows=39,586 loops=1)

13. 0.247 3.635 ↑ 3.5 1,213 1

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

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

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

  • Merge Cond: (pc.contract_id = (COALESCE(a.id, c.id)))
15. 2.125 2.125 ↑ 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.014..2.125 rows=8,265 loops=1)

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

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

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

Hash Left Join (cost=33.45..68.27 rows=461 width=56) (actual time=0.144..0.451 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.185 0.185 ↓ 1.0 751 1

Seq Scan on su_contracts c (cost=0.00..31.02 rows=749 width=46) (actual time=0.004..0.185 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.000 0.135 ↑ 2.0 1 1

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

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

Seq Scan on su_contracts a (cost=0.00..33.43 rows=2 width=46) (actual time=0.100..0.135 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.514 1.420 ↑ 1.0 2,086 1

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

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

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

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

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

24. 0.076 0.157 ↑ 1.3 361 1

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

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

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

26. 0.001 0.001 ↓ 0.0 0 1

Hash (cost=23.75..23.75 rows=6 width=12) (actual time=0.001..0.001 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.002 0.022 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
29. 0.006 0.020 ↑ 1.0 5 1

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

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

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

  • Filter: (renta_id IS NOT NULL)
31. 0.002 0.007 ↑ 1.0 2 1

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

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

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

33. 0.003 0.009 ↑ 1.0 14 1

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

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

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