explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oNJu

Settings
# exclusive inclusive rows x rows loops node
1. 293.411 2,706.078 ↑ 1.0 20 1

Limit (cost=650,436.58..650,436.63 rows=20 width=594) (actual time=2,706.074..2,706.078 rows=20 loops=1)

2. 26.278 2,412.667 ↑ 781.6 20 1

Sort (cost=650,436.58..650,475.66 rows=15,633 width=594) (actual time=2,412.664..2,412.667 rows=20 loops=1)

  • Sort Key: search_results.published_timestamp DESC
  • Sort Method: top-N heapsort Memory: 126kB
3. 108.976 2,386.389 ↓ 3.2 49,577 1

GroupAggregate (cost=646,583.92..650,020.59 rows=15,633 width=594) (actual time=2,163.074..2,386.389 rows=49,577 loops=1)

  • Group Key: search_results.id
  • Filter: (count(images.id) > 0)
4. 753.503 2,277.413 ↓ 1.1 290,342 1

Sort (cost=646,583.92..647,267.22 rows=273,319 width=745) (actual time=2,162.997..2,277.413 rows=290,342 loops=1)

  • Sort Key: search_results.id
  • Sort Method: external merge Disk: 226,408kB
5. 0.000 1,523.910 ↓ 1.1 290,342 1

Gather (cost=343,930.85..531,284.40 rows=273,319 width=745) (actual time=583.551..1,523.910 rows=290,342 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 459.722 1,539.299 ↑ 1.2 96,781 3 / 3

Parallel Hash Join (cost=342,930.85..502,952.50 rows=113,883 width=745) (actual time=571.040..1,539.299 rows=96,781 loops=3)

  • Hash Cond: (images.id = image_reference.image_id)
7. 510.630 510.630 ↑ 1.3 1,343,351 3 / 3

Parallel Seq Scan on images (cost=0.00..107,070.77 rows=1,679,244 width=183) (actual time=0.041..510.630 rows=1,343,351 loops=3)

  • Filter: status
  • Rows Removed by Filter: 1,031
8. 105.739 568.947 ↑ 1.0 109,318 3 / 3

Parallel Hash (cost=341,506.26..341,506.26 rows=113,967 width=566) (actual time=568.947..568.947 rows=109,318 loops=3)

  • Buckets: 524,288 Batches: 1 Memory Usage: 212,512kB
9. 8.599 463.208 ↑ 1.0 109,318 3 / 3

Nested Loop (cost=506.73..341,506.26 rows=113,967 width=566) (actual time=247.762..463.208 rows=109,318 loops=3)

10. 261.543 293.584 ↑ 1.1 17,892 3 / 3

Parallel Bitmap Heap Scan on search_results (cost=506.16..164,647.54 rows=19,541 width=566) (actual time=247.671..293.584 rows=17,892 loops=3)

  • Recheck Cond: (tag_ids @> '{13,493,496}'::integer[])
  • Filter: is_indexed
  • Rows Removed by Filter: 502
  • Heap Blocks: exact=12,503
11. 32.041 32.041 ↓ 1.1 55,181 1 / 3

Bitmap Index Scan on tags_index (cost=0.00..494.44 rows=48,858 width=0) (actual time=96.123..96.123 rows=55,181 loops=1)

  • Index Cond: (tag_ids @> '{13,493,496}'::integer[])
12. 161.025 161.025 ↑ 4.0 6 53,675 / 3

Index Scan using image_reference_id_ad_idx on image_reference (cost=0.56..8.81 rows=24 width=8) (actual time=0.004..0.009 rows=6 loops=53,675)

  • Index Cond: (result_id = search_results.id)