explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jeGN

Settings
# exclusive inclusive rows x rows loops node
1. 0.386 15.533 ↑ 1.0 1 1

Nested Loop (cost=17.95..1,653.79 rows=1 width=44) (actual time=12.223..15.533 rows=1 loops=1)

  • Buffers: shared hit=1,397
2. 0.006 15.141 ↑ 1.0 1 1

Nested Loop (cost=17.81..1,652.94 rows=1 width=1,194) (actual time=11.832..15.141 rows=1 loops=1)

  • Join Filter: (l.xml_agent_id = sar.xml_agent_id)
  • Buffers: shared hit=1,391
3. 0.006 15.129 ↑ 1.0 1 1

Nested Loop (cost=17.52..1,650.94 rows=1 width=1,195) (actual time=11.821..15.129 rows=1 loops=1)

  • Buffers: shared hit=1,388
4. 1.531 15.107 ↑ 1.0 1 1

Hash Join (cost=17.09..1,648.43 rows=1 width=1,115) (actual time=11.799..15.107 rows=1 loops=1)

  • Hash Cond: (a_1.agency_id = a.agency_id)
  • Buffers: shared hit=1,384
5. 2.778 13.490 ↓ 5.3 13,597 1

Hash Left Join (cost=2.55..1,624.27 rows=2,563 width=358) (actual time=0.049..13.490 rows=13,597 loops=1)

  • Hash Cond: (a_1.franchise_id = f.franchise_id)
  • Buffers: shared hit=1,362
6. 8.467 10.699 ↓ 5.3 13,597 1

Hash Join (cost=1.10..1,616.09 rows=2,563 width=350) (actual time=0.031..10.699 rows=13,597 loops=1)

  • Hash Cond: (a_1.agency_status_id = s_2.agency_status_id)
  • Buffers: shared hit=1,361
7. 2.221 2.221 ↓ 1.0 17,941 1

Seq Scan on agency a_1 (cost=0.00..1,539.39 rows=17,939 width=346) (actual time=0.010..2.221 rows=17,941 loops=1)

  • Buffers: shared hit=1,360
8. 0.003 0.011 ↑ 1.0 1 1

Hash (cost=1.09..1.09 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
9. 0.008 0.008 ↑ 1.0 1 1

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

  • Filter: (agency_status_label = 'ACTIVE'::text)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1
10. 0.006 0.013 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=16) (actual time=0.013..0.013 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
11. 0.007 0.007 ↑ 1.0 20 1

Seq Scan on franchise f (cost=0.00..1.20 rows=20 width=16) (actual time=0.004..0.007 rows=20 loops=1)

  • Buffers: shared hit=1
12. 0.003 0.086 ↑ 1.0 1 1

Hash (cost=14.52..14.52 rows=1 width=757) (actual time=0.086..0.086 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=22
13. 0.004 0.083 ↑ 1.0 1 1

Nested Loop (cost=2.12..14.52 rows=1 width=757) (actual time=0.080..0.083 rows=1 loops=1)

  • Join Filter: (a.agent_status_id = s_1.agent_status_id)
  • Buffers: shared hit=22
14. 0.003 0.076 ↑ 1.0 1 1

Nested Loop (cost=2.12..13.41 rows=1 width=753) (actual time=0.074..0.076 rows=1 loops=1)

  • Buffers: shared hit=21
15. 0.004 0.066 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.70..11.26 rows=1 width=609) (actual time=0.063..0.066 rows=1 loops=1)

  • Buffers: shared hit=17
16. 0.002 0.042 ↑ 1.0 1 1

Nested Loop (cost=1.27..8.60 rows=1 width=623) (actual time=0.041..0.042 rows=1 loops=1)

  • Buffers: shared hit=13
17. 0.003 0.035 ↑ 1.0 1 1

Nested Loop (cost=1.13..7.30 rows=1 width=623) (actual time=0.034..0.035 rows=1 loops=1)

  • Buffers: shared hit=11
18. 0.004 0.023 ↑ 1.0 1 1

Nested Loop (cost=0.84..5.28 rows=1 width=574) (actual time=0.022..0.023 rows=1 loops=1)

  • Buffers: shared hit=8
19. 0.012 0.012 ↑ 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.011..0.012 rows=1 loops=1)

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

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

  • Index Cond: (listing_json_id = c.listing_json_id)
  • Buffers: shared hit=4
21. 0.009 0.009 ↑ 1.0 1 1

Index Scan using listing_pkey on listing l (cost=0.29..2.02 rows=1 width=53) (actual time=0.009..0.009 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
22. 0.005 0.005 ↑ 1.0 1 1

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

  • Index Cond: (supplier_id = l.supplier_id)
  • Buffers: shared hit=2
23. 0.020 0.020 ↑ 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.019..0.020 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
24. 0.007 0.007 ↑ 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.007..0.007 rows=1 loops=1)

  • Index Cond: (agent_id = l.agent_id)
  • Buffers: shared hit=4
25. 0.003 0.003 ↑ 1.0 1 1

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

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

Index Scan using pk_address on address a_2 (cost=0.43..2.51 rows=1 width=96) (actual time=0.016..0.016 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
27. 0.006 0.006 ↑ 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.006..0.006 rows=1 loops=1)

  • Index Cond: (agency_id = a_1.agency_id)
  • Filter: publish_listings
  • Buffers: shared hit=3
28. 0.006 0.006 ↑ 1.0 1 1

Index Scan using supplier_creds_pkey on supplier_creds sc (cost=0.14..0.71 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (supplier_creds_id = sar.supplier_creds_id)
  • Filter: accept_feed
  • Buffers: shared hit=2