explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cvKE

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.004 763.485 ↑ 2.0 1 1

Subquery Scan on sub (cost=1,483,270.85..1,483,280.21 rows=2 width=36) (actual time=763.482..763.485 rows=1 loops=1)

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

CTE result

3. 0.536 763.409 ↑ 39.0 8 1

GroupAggregate (cost=1,480,136.30..1,483,251.69 rows=312 width=12) (actual time=762.835..763.409 rows=8 loops=1)

  • Group Key: ((date_part('year'::text, p.created_on))::integer)
4. 0.547 762.873 ↑ 411.9 1,007 1

Sort (cost=1,480,136.30..1,481,173.20 rows=414,761 width=8) (actual time=762.730..762.873 rows=1,007 loops=1)

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

Nested Loop (cost=1.01..1,441,435.12 rows=414,761 width=8) (actual time=28.371..762.326 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: 578174
6. 615.350 615.350 ↑ 272.6 12,323 1

Index Scan using idx_property_products_ps_product_id on property_products pp (cost=0.43..203,430.92 rows=3,359,668 width=8) (actual time=0.038..615.350 rows=12,323 loops=1)

  • Index Cond: (ps_product_id = 1)
7. 44.944 61.615 ↓ 2.2 47 12,323

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

8. 0.125 16.671 ↓ 2.2 47 1

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

9. 15.490 15.490 ↑ 2.4 132 1

Index Scan using pk_properties on properties p (cost=0.29..867.88 rows=313 width=16) (actual time=0.094..15.490 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.039 763.481 ↑ 39.0 8 1

WindowAgg (cost=19.17..24.63 rows=312 width=36) (actual time=763.469..763.481 rows=8 loops=1)

12. 0.016 763.442 ↑ 39.0 8 1

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

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

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

Planning time : 3.612 ms