explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CUUy : Optimization for: plan #cvKE

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.003 1,666.573 ↑ 2.0 1 1

Subquery Scan on sub (cost=1,485,071.45..1,485,080.81 rows=2 width=36) (actual time=1,666.571..1,666.573 rows=1 loops=1)

  • Filter: (sub.established_year = 2018)
  • Rows Removed by Filter: 7
2.          

CTE result

3. 0.315 1,666.485 ↑ 39.0 8 1

GroupAggregate (cost=1,481,933.12..1,485,052.28 rows=312 width=12) (actual time=1,666.176..1,666.485 rows=8 loops=1)

  • Group Key: ((date_part('year'::text, p.created_on))::integer)
4. 0.361 1,666.170 ↑ 412.4 1,007 1

Sort (cost=1,481,933.12..1,482,971.28 rows=415,264 width=8) (actual time=1,666.087..1,666.170 rows=1,007 loops=1)

  • Sort Key: ((date_part('year'::text, p.created_on))::integer)
  • Sort Method: quicksort Memory: 72kB
5. 88.853 1,665.809 ↑ 412.4 1,007 1

Nested Loop (cost=1.01..1,443,181.37 rows=415,264 width=8) (actual time=15.406..1,665.809 rows=1,007 loops=1)

  • Join Filter: (((pp.cid = p.cid) AND (pp.property_id = p.id)) OR (pp.property_id IS NULL))
  • Rows Removed by Join Filter: 578080
6. 1,527.672 1,527.672 ↑ 273.0 12,321 1

Index Scan using idx_property_products_ps_product_id on property_products pp (cost=0.43..203,676.37 rows=3,363,745 width=8) (actual time=0.076..1,527.672 rows=12,321 loops=1)

  • Index Cond: (ps_product_id = 1)
7. 36.747 49.284 ↓ 2.2 47 12,321

Materialize (cost=0.57..1,252.45 rows=21 width=16) (actual time=0.000..0.004 rows=47 loops=12,321)

8. 0.145 12.537 ↓ 2.2 47 1

Nested Loop (cost=0.57..1,252.34 rows=21 width=16) (actual time=0.278..12.537 rows=47 loops=1)

9. 11.336 11.336 ↑ 2.4 132 1

Index Scan using pk_properties on properties p (cost=0.29..867.88 rows=313 width=16) (actual time=0.239..11.336 rows=132 loops=1)

  • Index Cond: (cid = 2138)
  • Filter: ((occupancy_type_ids && '{2}'::integer[]) AND (is_disabled = 0))
  • Rows Removed by Filter: 3155
10. 1.056 1.056 ↓ 0.0 0 132

Index Scan using uk_property_gl_settings_cid_property_id on property_gl_settings pgs (cost=0.29..1.23 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=132)

  • Index Cond: ((cid = 2138) AND (property_id = p.id))
  • Filter: activate_standard_posting
  • Rows Removed by Filter: 1
11. 0.041 1,666.570 ↑ 39.0 8 1

WindowAgg (cost=19.17..24.63 rows=312 width=36) (actual time=1,666.563..1,666.570 rows=8 loops=1)

12. 0.032 1,666.529 ↑ 39.0 8 1

Sort (cost=19.17..19.95 rows=312 width=12) (actual time=1,666.529..1,666.529 rows=8 loops=1)

  • Sort Key: r.established_year
  • Sort Method: quicksort Memory: 25kB
13. 1,666.497 1,666.497 ↑ 39.0 8 1

CTE Scan on result r (cost=0.00..6.24 rows=312 width=12) (actual time=1,666.183..1,666.497 rows=8 loops=1)

Planning time : 5.848 ms