explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kXJF

Settings
# exclusive inclusive rows x rows loops node
1. 0.482 0.639 ↑ 1.0 1 1

Nested Loop Left Join (cost=3.26..23.55 rows=1 width=44) (actual time=0.631..0.639 rows=1 loops=1)

  • Join Filter: (sar.xml_agent_id = l.xml_agent_id)
  • Buffers: shared hit=37
2. 0.002 0.152 ↑ 1.0 1 1

Nested Loop (cost=2.98..21.48 rows=1 width=693) (actual time=0.146..0.152 rows=1 loops=1)

  • Buffers: shared hit=30
3. 0.001 0.116 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.54..18.94 rows=1 width=669) (actual time=0.111..0.116 rows=1 loops=1)

  • Buffers: shared hit=26
4. 0.001 0.069 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.12..14.54 rows=1 width=637) (actual time=0.065..0.069 rows=1 loops=1)

  • Buffers: shared hit=22
5. 0.001 0.056 ↑ 1.0 1 1

Nested Loop (cost=1.69..11.87 rows=1 width=651) (actual time=0.053..0.056 rows=1 loops=1)

  • Buffers: shared hit=18
6. 0.002 0.052 ↑ 1.0 1 1

Nested Loop (cost=1.55..10.58 rows=1 width=651) (actual time=0.049..0.052 rows=1 loops=1)

  • Buffers: shared hit=16
7. 0.000 0.045 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.26..8.55 rows=1 width=610) (actual time=0.043..0.045 rows=1 loops=1)

  • Buffers: shared hit=13
8. 0.005 0.017 ↑ 1.0 1 1

Nested Loop (cost=0.84..5.28 rows=1 width=578) (actual time=0.016..0.017 rows=1 loops=1)

  • Buffers: shared hit=8
9. 0.008 0.008 ↑ 1.0 1 1

Index Scan using pk_changed_listing_json on changed_listing_json c (cost=0.42..2.64 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: (changed_listing_json_id = 458,900)
  • Buffers: shared hit=4
10. 0.004 0.004 ↑ 1.0 1 1

Index Scan using listing_json_pkey on listing_json lj (cost=0.42..2.64 rows=1 width=574) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (listing_json_id = c.listing_json_id)
  • Buffers: shared hit=4
11. 0.016 0.028 ↑ 1.0 1 1

Nested Loop (cost=0.42..3.27 rows=1 width=40) (actual time=0.026..0.028 rows=1 loops=1)

  • Join Filter: (a.agent_status_id = s_1.agent_status_id)
  • Buffers: shared hit=5
12. 0.006 0.006 ↑ 1.0 1 1

Index Scan using agentcentre_agent_pkey on agent a (cost=0.42..2.15 rows=1 width=152) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: (agent_id = lj.agent_id)
  • Buffers: shared hit=4
13. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on agent_status s_1 (cost=0.00..1.10 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=1)

  • Filter: (agent_status_label = 'ACTIVE'::text)
  • Buffers: shared hit=1
14. 0.005 0.005 ↑ 1.0 1 1

Index Scan using listing_pkey on listing l (cost=0.29..2.02 rows=1 width=45) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (listing_id = lj.listing_id)
  • Filter: (can_publish AND (listing_type = ANY ('{residential,rental,land,rural}'::text[])))
  • Buffers: shared hit=3
15. 0.003 0.003 ↑ 1.0 1 1

Index Scan using supplier_pkey on supplier s (cost=0.14..1.26 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (supplier_id = l.supplier_id)
  • Buffers: shared hit=2
16. 0.012 0.012 ↑ 1.0 1 1

Index Scan using idx_staging_datastore_listing_raw_key on listing sdl (cost=0.43..2.65 rows=1 width=24) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: ((portalsupplierid = s.legacy_supplier_id) AND (agentid = l.xml_agent_id) AND (uniqueid = l.unique_id))
  • Buffers: shared hit=4
17. 0.035 0.046 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.42..4.40 rows=1 width=36) (actual time=0.045..0.046 rows=1 loops=1)

  • Buffers: shared hit=4
18. 0.002 0.010 ↑ 1.0 1 1

Nested Loop (cost=0.29..3.11 rows=1 width=350) (actual time=0.009..0.010 rows=1 loops=1)

  • Join Filter: (a_1.agency_status_id = s_2.agency_status_id)
  • Buffers: shared hit=4
19. 0.005 0.005 ↑ 1.0 1 1

Index Scan using agency_pkey on agency a_1 (cost=0.29..2.01 rows=1 width=346) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (agency_id = a.agency_id)
  • Buffers: shared hit=3
20. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on agency_status s_2 (cost=0.00..1.09 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=1)

  • Filter: (agency_status_label = 'ACTIVE'::text)
  • Buffers: shared hit=1
21. 0.001 0.001 ↓ 0.0 0 1

Index Scan using franchise_pkey on franchise f (cost=0.14..0.71 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (franchise_id = a_1.franchise_id)
22. 0.034 0.034 ↑ 1.0 1 1

Index Scan using pk_address on address a_2 (cost=0.43..2.54 rows=1 width=40) (actual time=0.034..0.034 rows=1 loops=1)

  • Index Cond: (oth_property_id = l.oth_property_id)
  • Filter: ((suburb IS NOT NULL) AND (state_code IS NOT NULL) AND (postcode IS NOT NULL) AND (formatted_address IS NOT NULL))
  • Buffers: shared hit=4
23. 0.005 0.005 ↑ 1.0 1 1

Index Scan using supplier_agency_ref_agency_id on supplier_agency_ref sar (cost=0.29..2.00 rows=1 width=14) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (agency_id = a_1.agency_id)
  • Buffers: shared hit=3