explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cmSP : Optimization for: plan #cvKE

Settings

Optimization path:

Optimization(s) for this plan:

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

Subquery Scan on sub (cost=1,486,864.13..1,486,873.49 rows=2 width=36) (actual time=1,431.462..1,431.464 rows=1 loops=1)

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

CTE result

3. 0.555 1,431.373 ↑ 39.0 8 1

GroupAggregate (cost=1,483,722.04..1,486,844.97 rows=312 width=12) (actual time=1,430.795..1,431.373 rows=8 loops=1)

  • Group Key: ((date_part('year'::text, p.created_on))::integer)
4. 0.507 1,430.818 ↑ 412.9 1,007 1

Sort (cost=1,483,722.04..1,484,761.46 rows=415,766 width=8) (actual time=1,430.660..1,430.818 rows=1,007 loops=1)

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

Nested Loop (cost=1.01..1,444,919.82 rows=415,766 width=8) (actual time=12.649..1,430.311 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: 578033
6. 1,254.643 1,254.643 ↑ 273.4 12,320 1

Index Scan using idx_property_products_ps_product_id on property_products pp (cost=0.44..203,919.53 rows=3,367,807 width=8) (actual time=0.051..1,254.643 rows=12,320 loops=1)

  • Index Cond: (ps_product_id = 1)
7. 51.852 61.600 ↓ 2.2 47 12,320

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

8. 0.121 9.748 ↓ 2.2 47 1

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

9. 8.967 8.967 ↑ 2.4 132 1

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

  • Index Cond: (cid = 2138)
  • Filter: ((occupancy_type_ids && '{2}'::integer[]) AND (is_disabled = 0))
  • Rows Removed by Filter: 3155
10. 0.660 0.660 ↓ 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.005..0.005 rows=0 loops=132)

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

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

12. 0.038 1,431.431 ↑ 39.0 8 1

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

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

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

Planning time : 5.994 ms