explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6zte : Optimization for: Optimization for: plan #cvKE; plan #cmSP

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.504 1,492.960 ↑ 39.0 8 1

GroupAggregate (cost=1,017,399.58..1,019,436.40 rows=312 width=12) (actual time=1,492.456..1,492.960 rows=8 loops=1)

  • Group Key: ((date_part('year'::text, p.created_on))::integer)
2. 0.540 1,492.456 ↑ 269.1 1,007 1

Sort (cost=1,017,399.58..1,018,076.96 rows=270,953 width=8) (actual time=1,492.302..1,492.456 rows=1,007 loops=1)

  • Sort Key: ((date_part('year'::text, p.created_on))::integer)
  • Sort Method: quicksort Memory: 72kB
3. 86.975 1,491.916 ↑ 269.1 1,007 1

Nested Loop (cost=1.01..992,949.21 rows=270,953 width=8) (actual time=16.283..1,491.916 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: 579584
4. 1,355.529 1,355.529 ↑ 177.7 12,353 1

Index Scan using idx_property_products_ps_product_id on property_products pp (cost=0.43..183,757.19 rows=2,194,786 width=8) (actual time=0.056..1,355.529 rows=12,353 loops=1)

  • Index Cond: (ps_product_id = 1)
5. 36.300 49.412 ↓ 2.2 47 12,353

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

6. 0.078 13.112 ↓ 2.2 47 1

Nested Loop (cost=0.57..1,253.34 rows=21 width=16) (actual time=0.272..13.112 rows=47 loops=1)

7. 11.846 11.846 ↑ 2.4 132 1

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

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

  • Index Cond: ((cid = 2138) AND (property_id = p.id))
  • Filter: activate_standard_posting
  • Rows Removed by Filter: 1
Planning time : 5.600 ms