explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FdTO

Settings
# exclusive inclusive rows x rows loops node
1. 138.136 5,754.058 ↓ 17,206.7 154,860 1

Unique (cost=31,618.89..31,619.02 rows=9 width=84) (actual time=5,548.299..5,754.058 rows=154,860 loops=1)

  • Buffers: shared hit=1714851 read=21115 dirtied=863
2.          

Initplan (for Unique)

3. 0.001 4.540 ↑ 277.0 1 1

Nested Loop (cost=0.28..77.65 rows=277 width=0) (actual time=4.54..4.54 rows=1 loops=1)

  • Buffers: shared hit=1 read=29
4. 2.486 2.486 ↑ 1.0 1 1

Seq Scan on property_groups pg (cost=0..61.06 rows=1 width=8) (actual time=2.486..2.486 rows=1 loops=1)

  • Filter: ((pg.deleted_on IS NULL) AND (pg.cid = 3395) AND (pg.id = 200006517))
  • Buffers: shared hit=1 read=26
5. 2.053 2.053 ↑ 277.0 1 1

Index Only Scan using idx_property_group_associations_master on property_group_associations pga (cost=0.28..13.82 rows=277 width=8) (actual time=2.053..2.053 rows=1 loops=1)

  • Index Cond: ((pga.cid = 3395) AND (pga.property_group_id = 200006517))
  • Buffers: shared read=3
6. 1,038.650 5,611.382 ↓ 61,017.8 549,160 1

Sort (cost=31,618.33..31,618.35 rows=9 width=84) (actual time=5,548.297..5,611.382 rows=549,160 loops=1)

  • Sort Key: (CASE WHEN (ah.lease_customer_id IS NOT NULL) THEN ah.header_memo ELSE ap.company_name END), (CASE WHEN (ah.lease_customer_id IS NULL) THEN COALESCE(apl.vendor_code, apl.location_name) ELSE NULL::character varying END), apl.id, (CASE WHEN (ah.lease_customer_id IS NOT NULL) THEN ah.lease_customer_id ELSE NULL::integer END), (count(apl.id) OVER (?))
  • Sort Method: quicksort Memory: 74998kB
  • Buffers: shared hit=1714851 read=21115 dirtied=863
7. 413.862 4,572.732 ↓ 61,017.8 549,160 1

WindowAgg (cost=31,618.03..31,618.18 rows=9 width=84) (actual time=4,125.951..4,572.732 rows=549,160 loops=1)

  • Buffers: shared hit=1714842 read=21115 dirtied=863
8. 411.124 4,158.870 ↓ 61,017.8 549,160 1

Sort (cost=31,618.03..31,618.05 rows=9 width=64) (actual time=4,057.089..4,158.87 rows=549,160 loops=1)

  • Sort Key: apl.ap_payee_id
  • Sort Method: quicksort Memory: 83194kB
  • Buffers: shared hit=1714842 read=21115 dirtied=863
9. 117.897 3,747.746 ↓ 61,017.8 549,160 1

Result (cost=9,574.5..31,617.88 rows=9 width=64) (actual time=307.028..3,747.746 rows=549,160 loops=1)

  • Buffers: shared hit=1714842 read=21115 dirtied=863
10. 643.829 3,629.849 ↓ 61,017.8 549,160 1

Nested Loop (cost=9,574.5..31,617.88 rows=9 width=64) (actual time=302.485..3,629.849 rows=549,160 loops=1)

  • Buffers: shared hit=1714841 read=21086 dirtied=863
11. 348.250 1,276.947 ↓ 7,033.2 569,691 1

Gather (cost=9,574.21..31,590.44 rows=81 width=58) (actual time=299.159..1,276.947 rows=569,691 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=6413 read=20309 dirtied=863
12. 173.752 928.697 ↓ 5,585.2 189,897 3 / 3

Hash Join (cost=8,574.21..30,582.34 rows=34 width=58) (actual time=284.368..928.697 rows=189,897 loops=3)

  • Buffers: shared hit=6413 read=20309 dirtied=863
13. 472.304 472.304 ↑ 1.2 189,898 3 / 3

Seq Scan on ap_headers ah (cost=0..20,763.65 rows=237,043 width=33) (actual time=0.184..472.304 rows=189,898 loops=3)

  • Filter: ((ah.deleted_on IS NULL) AND (ah.ap_header_type_id = ANY ('{4,5}'::integer[])) AND (ah.cid = 3395))
  • Buffers: shared hit=31 read=17177 dirtied=854
14. 120.548 282.641 ↑ 1.0 197,202 3 / 3

Hash (cost=5,615.99..5,615.99 rows=197,215 width=29) (actual time=282.641..282.641 rows=197,202 loops=3)

  • Buffers: shared hit=6312 read=3132 dirtied=9
15. 162.093 162.093 ↑ 1.0 197,202 3 / 3

Seq Scan on ap_payee_locations apl (cost=0..5,615.99 rows=197,215 width=29) (actual time=0.149..162.093 rows=197,202 loops=3)

  • Filter: ((apl.deleted_on IS NULL) AND (apl.disabled_on IS NULL) AND (apl.cid = 3395))
  • Buffers: shared hit=6312 read=3132 dirtied=9
16. 1,709.073 1,709.073 ↑ 1.0 1 569,691

Index Scan using pk_ap_payees on ap_payees ap (cost=0.29..0.33 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=569,691)

  • Index Cond: ((ap.cid = 3395) AND (ap.id = ah.ap_payee_id))
  • Filter: ((ap.company_name IS NOT NULL) AND (ap.ap_payee_status_type_id = 1) AND (ap.ap_payee_type_id = ANY ('{1,2,3,4,7,8}'::integer[])))
  • Buffers: shared hit=1708428 read=777
Planning time : 124.553 ms
Execution time : 5,781.798 ms