explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u5T

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 1,535.673 ↑ 1.0 4 1

Limit (cost=115,403.38..115,403.84 rows=4 width=237) (actual time=1,522.987..1,535.673 rows=4 loops=1)

2. 61.339 1,535.668 ↑ 17.0 4 1

Gather Merge (cost=115,403.38..115,411.31 rows=68 width=237) (actual time=1,522.986..1,535.668 rows=4 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 2.784 1,474.329 ↑ 8.5 4 3 / 3

Sort (cost=114,403.35..114,403.44 rows=34 width=237) (actual time=1,474.328..1,474.329 rows=4 loops=3)

  • Sort Key: ((o.id + 0)) DESC
  • Sort Method: top-N heapsort Memory: 27kB
  • Worker 0: Sort Method: top-N heapsort Memory: 26kB
  • Worker 1: Sort Method: top-N heapsort Memory: 26kB
4. 10.105 1,471.545 ↓ 40.6 1,379 3 / 3

Nested Loop (cost=79,913.51..114,402.84 rows=34 width=237) (actual time=1,099.458..1,471.545 rows=1,379 loops=3)

5. 2.512 1,446.275 ↓ 40.6 1,379 3 / 3

Nested Loop (cost=79,913.51..114,175.04 rows=34 width=173) (actual time=1,099.150..1,446.275 rows=1,379 loops=3)

6. 2.363 1,420.326 ↓ 40.6 1,379 3 / 3

Nested Loop (cost=79,913.08..113,915.04 rows=34 width=126) (actual time=1,098.489..1,420.326 rows=1,379 loops=3)

7. 1,001.241 1,380.567 ↓ 3.0 870 3 / 3

Parallel Bitmap Heap Scan on cache_occurrences_functional o (cost=79,912.66..111,513.33 rows=286 width=46) (actual time=1,098.129..1,380.567 rows=870 loops=3)

  • Recheck Cond: ((location_ids && '{674}'::integer[]) AND (website_id = ANY ('{101,108,43,13,97,33,29,44,87,12,24,14,17,6,8,25,11,7,30,5,34,40,32,42,51,49,65,68,73,75,9,71,88,10,23,69,116,98,115,27,112,94,95,123}'::integer[])) AND (taxon_group_id = ANY ('{73,101,102,105,111,113,118,120,123,124}'::integer[])))
  • Rows Removed by Index Recheck: 9884
  • Filter: ((NOT training) AND (NOT confidential) AND (media_count > 0) AND (release_status = 'R'::bpchar) AND ((created_by_id <> 1) OR (record_status = 'V'::bpchar)) AND ((website_id = 23) OR (created_by_id = 1) OR (blocked_sharing_tasks IS NULL) OR (NOT (blocked_sharing_tasks @> '{R}'::character(1)[]))))
  • Rows Removed by Filter: 5155
  • Heap Blocks: exact=11236
8. 2.862 379.326 ↓ 0.0 0 1 / 3

BitmapAnd (cost=79,912.66..79,912.66 rows=8,493 width=0) (actual time=1,137.977..1,137.977 rows=0 loops=1)

9. 162.518 162.518 ↓ 1.2 65,489 1 / 3

Bitmap Index Scan on ix_cache_occurrences_functional_location_ids (cost=0.00..8,016.29 rows=56,029 width=0) (actual time=487.555..487.555 rows=65,489 loops=1)

  • Index Cond: ((location_ids && '{674}'::integer[]) AND (website_id = ANY ('{101,108,43,13,97,33,29,44,87,12,24,14,17,6,8,25,11,7,30,5,34,40,32,42,51,49,65,68,73,75,9,71,88,10,23,69,116,98,115,27,112,94,95,123}'::integer[])))
10. 213.946 213.946 ↓ 1.1 1,986,479 1 / 3

Bitmap Index Scan on ix_cache_occurrences_functional_taxon_group_id (cost=0.00..71,895.78 rows=1,811,486 width=0) (actual time=641.837..641.837 rows=1,986,479 loops=1)

  • Index Cond: (taxon_group_id = ANY ('{73,101,102,105,111,113,118,120,123,124}'::integer[]))
11. 37.396 37.396 ↑ 1.0 2 2,609 / 3

Index Scan using fki_occurrence_images_occurrence on occurrence_media om (cost=0.43..8.38 rows=2 width=84) (actual time=0.042..0.043 rows=2 loops=2,609)

  • Index Cond: (occurrence_id = o.id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
12. 23.437 23.437 ↑ 1.0 1 4,136 / 3

Index Scan using pk_cache_taxa_taxon_lists on cache_taxa_taxon_lists cttl (cost=0.42..7.65 rows=1 width=55) (actual time=0.017..0.017 rows=1 loops=4,136)

  • Index Cond: (id = o.taxa_taxon_list_id)
13. 15.165 15.165 ↑ 1.0 1 4,136 / 3

Seq Scan on system sys (cost=0.00..6.66 rows=1 width=0) (actual time=0.008..0.011 rows=1 loops=4,136)

  • Filter: (id = 1)
  • Rows Removed by Filter: 45