explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RqIW : Filter Date

Settings
# exclusive inclusive rows x rows loops node
1. 6.131 27,979.476 ↓ 2,498.2 32,477 1

Hash Left Join (cost=592.38..2,114.90 rows=13 width=50) (actual time=24,657.230..27,979.476 rows=32,477 loops=1)

  • Hash Cond: (p.id = dp.property_id)
2. 6.069 27,972.363 ↓ 2,498.2 32,477 1

Hash Left Join (cost=577.48..2,099.82 rows=13 width=46) (actual time=24,656.233..27,972.363 rows=32,477 loops=1)

  • Hash Cond: (c.id = dc.cid)
3. 16.840 27,965.797 ↓ 2,230.5 28,996 1

Nested Loop (cost=575.75..2,098.01 rows=13 width=46) (actual time=24,655.721..27,965.797 rows=28,996 loops=1)

  • Join Filter: (art.cid = c.id)
  • Rows Removed by Join Filter: 202,972
4. 50.037 50.037 ↑ 1.8 8 1

Foreign Scan on clients c (cost=100.00..165.12 rows=14 width=4) (actual time=50.034..50.037 rows=8 loops=1)

5. 16.540 27,898.920 ↓ 2,230.5 28,996 8

Materialize (cost=475.75..1,930.19 rows=13 width=42) (actual time=2,693.126..3,487.365 rows=28,996 loops=8)

6. 712.973 27,882.380 ↓ 2,230.5 28,996 1

Nested Loop (cost=475.75..1,930.12 rows=13 width=42) (actual time=21,544.994..27,882.380 rows=28,996 loops=1)

  • Join Filter: (art.property_id = p.id)
  • Rows Removed by Join Filter: 10,211,948
7. 39.759 39.759 ↓ 336.0 336 1

Foreign Scan on properties p (cost=100.00..183.14 rows=1 width=4) (actual time=35.022..39.759 rows=336 loops=1)

8. 4,214.430 27,129.648 ↓ 2,344.5 30,479 336

Hash Right Join (cost=375.75..1,746.82 rows=13 width=42) (actual time=63.709..80.743 rows=30,479 loops=336)

  • Hash Cond: (dus.unit_space_id = lus.unit_space_id)
9. 1,511.328 1,511.328 ↑ 1.0 64,941 336

Seq Scan on dim_unit_space dus (cost=0.00..1,127.41 rows=64,941 width=8) (actual time=0.006..4.498 rows=64,941 loops=336)

10. 6.278 21,403.890 ↓ 2,344.5 30,479 1

Hash (cost=375.59..375.59 rows=13 width=38) (actual time=21,403.890..21,403.890 rows=30,479 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,042kB
11. 45.530 21,397.612 ↓ 2,344.5 30,479 1

Hash Join (cost=279.37..375.59 rows=13 width=38) (actual time=18,890.357..21,397.612 rows=30,479 loops=1)

  • Hash Cond: (lus.lease_id = art.lease_id)
12. 2,485.634 2,485.634 ↓ 100.4 255,744 1

Foreign Scan on lease_unit_spaces lus (cost=100.00..186.54 rows=2,547 width=8) (actual time=23.887..2,485.634 rows=255,744 loops=1)

13. 7.216 18,866.448 ↓ 30,425.0 30,425 1

Hash (cost=179.35..179.35 rows=1 width=38) (actual time=18,866.448..18,866.448 rows=30,425 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,039kB
14. 18,859.232 18,859.232 ↓ 30,425.0 30,425 1

Foreign Scan on ar_transactions art (cost=100.00..179.35 rows=1 width=38) (actual time=66.553..18,859.232 rows=30,425 loops=1)

  • Filter: ((date_part('month'::text, (post_date)::timestamp without time zone) = '5'::double precision) AND (date_part('year'::text, (post_date)::timestamp without time zone) = '2020'::double precision))
  • Rows Removed by Filter: 1,192,395
15. 0.006 0.497 ↓ 1.8 11 1

Hash (cost=1.66..1.66 rows=6 width=8) (actual time=0.497..0.497 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.003 0.491 ↓ 1.8 11 1

Subquery Scan on dc (cost=1.30..1.66 rows=6 width=8) (actual time=0.475..0.491 rows=11 loops=1)

  • Filter: dc.is_current
17. 0.026 0.488 ↑ 1.0 11 1

WindowAgg (cost=1.30..1.55 rows=11 width=129) (actual time=0.474..0.488 rows=11 loops=1)

18. 0.041 0.462 ↑ 1.0 11 1

Sort (cost=1.30..1.33 rows=11 width=12) (actual time=0.461..0.462 rows=11 loops=1)

  • Sort Key: dim_client.cid, dim_client.version
  • Sort Method: quicksort Memory: 25kB
19. 0.421 0.421 ↑ 1.0 11 1

Seq Scan on dim_client (cost=0.00..1.11 rows=11 width=12) (actual time=0.418..0.421 rows=11 loops=1)

20. 0.051 0.982 ↑ 1.0 351 1

Hash (cost=10.51..10.51 rows=351 width=8) (actual time=0.982..0.982 rows=351 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
21. 0.931 0.931 ↑ 1.0 351 1

Seq Scan on dim_property dp (cost=0.00..10.51 rows=351 width=8) (actual time=0.513..0.931 rows=351 loops=1)

Planning time : 19.143 ms
Execution time : 27,999.034 ms