explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xq1c

Settings
# exclusive inclusive rows x rows loops node
1. 0.030 60,351.812 ↑ 11,942.5 11 1

Sort (cost=495,824.05..496,152.47 rows=131,368 width=28) (actual time=60,351.811..60,351.812 rows=11 loops=1)

  • Sort Key: opportunities_opportunity.time_stamp DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=8,373,044 read=226,830, temp read=27,615 written=27,674
2. 28.413 60,351.782 ↑ 11,942.5 11 1

GroupAggregate (cost=435,371.43..481,509.13 rows=131,368 width=28) (actual time=60,269.364..60,351.782 rows=11 loops=1)

  • Group Key: opportunities_opportunity.id, (((opportunities_opportunity.data ->> 'PHOTOCOUNT'::text))::integer)
  • Filter: ((((opportunities_opportunity.data ->> 'PHOTOCOUNT'::text))::integer) > count(zillow_houseimage.id))
  • Buffers: shared hit=8,373,041 read=226,830, temp read=27,615 written=27,674
3. 142.109 60,323.369 ↑ 2.0 132,409 1

Gather Merge (cost=435,371.43..477,239.67 rows=262,736 width=53) (actual time=59,678.485..60,323.369 rows=132,409 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=25,485,707 read=727,799, temp read=84,418 written=84,599
4. 308.364 60,181.260 ↑ 3.0 44,136 3 / 3

GroupAggregate (cost=434,371.41..445,913.4 rows=131,368 width=53) (actual time=59,593.685..60,181.26 rows=44,136 loops=3)

  • Group Key: opportunities_opportunity.id, (((opportunities_opportunity.data ->> 'PHOTOCOUNT'::text))::integer)
  • Buffers: shared hit=25,485,707 read=727,799, temp read=84,418 written=84,599
5. 2,272.694 59,872.896 ↓ 1.8 1,626,434 3 / 3

Sort (cost=434,371.41..436,682.17 rows=924,305 width=49) (actual time=59,593.656..59,872.896 rows=1,626,434 loops=3)

  • Sort Key: opportunities_opportunity.id, (((opportunities_opportunity.data ->> 'PHOTOCOUNT'::text))::integer)
  • Sort Method: external merge Disk: 103,112kB
  • Buffers: shared hit=25,485,707 read=727,799, temp read=84,418 written=84,599
6. 41,581.417 57,600.202 ↓ 1.8 1,626,434 3 / 3

Nested Loop (cost=16,432.35..279,592.98 rows=924,305 width=49) (actual time=12,727.108..57,600.202 rows=1,626,434 loops=3)

  • Buffers: shared hit=25,485,693 read=727,799, temp read=3,541 written=3,523
7. 243.625 13,194.060 ↑ 1.2 44,136 3 / 3

Hash Join (cost=16,431.92..82,963.78 rows=54,737 width=45) (actual time=12,726.289..13,194.06 rows=44,136 loops=3)

  • Buffers: shared hit=3,659,957 read=261,082, temp read=3,541 written=3,523
8. 224.701 224.701 ↑ 1.2 169,294 3 / 3

Seq Scan on zillow_house zillow_house (cost=0..63,172.57 rows=211,158 width=4) (actual time=0.018..224.701 rows=169,294 loops=3)

  • Buffers: shared read=61,061
9. 165.515 12,725.734 ↓ 1.0 132,409 3 / 3

Hash (cost=13,634.81..13,634.81 rows=131,368 width=41) (actual time=12,725.734..12,725.734 rows=132,409 loops=3)

  • Buffers: shared hit=3,659,887 read=200,021, temp written=2,394
10. 12,560.219 12,560.219 ↓ 1.0 132,409 3 / 3

Seq Scan on opportunities_opportunity opportunities_opportunity (cost=0..13,634.81 rows=131,368 width=41) (actual time=0.339..12,560.219 rows=132,409 loops=3)

  • Filter: ((NOT (opportunities_opportunity.data @> '{"PHOTOCOUNT": ""}'::jsonb)) AND ((opportunities_opportunity.source)::text = 'minneapolis_mls'::text))
  • Buffers: shared hit=3,659,887 read=200,021
11. 2,824.725 2,824.725 ↑ 2.2 37 132,409 / 3

Index Scan using zillow_houseimage_house_id_24d9f8fb on zillow_houseimage zillow_houseimage (cost=0.43..2.64 rows=83 width=8) (actual time=0.04..0.064 rows=37 loops=132,409)

  • Index Cond: (zillow_house.id = zillow_houseimage.house_id)
  • Buffers: shared hit=355,505 read=280,999
Planning time : 2.097 ms
Execution time : 60,405.98 ms