explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vSHb

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 5,448.610 ↓ 21.0 21 1

Limit (cost=122.26..122.91 rows=1 width=683) (actual time=5,448.542..5,448.610 rows=21 loops=1)

2. 2.582 5,448.604 ↓ 21.0 21 1

Result (cost=122.26..122.91 rows=1 width=683) (actual time=5,448.541..5,448.604 rows=21 loops=1)

3. 18.854 5,446.022 ↓ 21.0 21 1

Sort (cost=122.26..122.26 rows=1 width=623) (actual time=5,446.021..5,446.022 rows=21 loops=1)

  • Sort Key: o.id DESC
  • Sort Method: top-N heapsort Memory: 46kB
4. 108.461 5,427.168 ↓ 6,947.0 6,947 1

Nested Loop (cost=2.72..122.25 rows=1 width=623) (actual time=1.269..5,427.168 rows=6,947 loops=1)

5. 39.461 5,256.184 ↓ 6,947.0 6,947 1

Nested Loop Left Join (cost=2.72..118.57 rows=1 width=367) (actual time=0.958..5,256.184 rows=6,947 loops=1)

  • Join Filter: (canedit.from_website_id = o.website_id)
  • Rows Removed by Join Filter: 20841
6. 14.514 1,201.357 ↓ 6,947.0 6,947 1

Nested Loop (cost=2.72..55.21 rows=1 width=363) (actual time=0.926..1,201.357 rows=6,947 loops=1)

7. 13.304 1,159.055 ↓ 6,947.0 6,947 1

Nested Loop (cost=2.30..46.77 rows=1 width=296) (actual time=0.856..1,159.055 rows=6,947 loops=1)

8. 13.746 972.076 ↓ 6,947.0 6,947 1

Nested Loop (cost=1.86..45.85 rows=1 width=289) (actual time=0.797..972.076 rows=6,947 loops=1)

9. 15.497 777.708 ↓ 6,947.0 6,947 1

Nested Loop (cost=1.43..37.40 rows=1 width=245) (actual time=0.720..777.708 rows=6,947 loops=1)

10. 19.634 567.695 ↓ 6,947.0 6,947 1

Nested Loop (cost=1.00..36.42 rows=1 width=193) (actual time=0.636..567.695 rows=6,947 loops=1)

11. 4.258 332.704 ↓ 6,947.0 6,947 1

Nested Loop (cost=0.56..27.97 rows=1 width=90) (actual time=0.553..332.704 rows=6,947 loops=1)

12. 0.121 0.121 ↓ 5.0 5 1

Seq Scan on workflow_metadata wm (cost=0.00..1.07 rows=1 width=36) (actual time=0.113..0.121 rows=5 loops=1)

  • Filter: (((key)::text = 'taxa_taxon_list_external_key'::text) AND ((group_code)::text = 'NNSIP'::text))
13. 328.325 328.325 ↓ 1,389.0 1,389 5

Index Scan using ix_cache_occurrences_functional_taxa_taxon_list_external_key on cache_occurrences_functional o (cost=0.56..26.89 rows=1 width=102) (actual time=0.333..65.665 rows=1,389 loops=5)

  • Index Cond: (((taxa_taxon_list_external_key)::text = (wm.key_value)::text) AND ((taxa_taxon_list_external_key)::text = ANY ('{NHMSYS0020936646,NHMSYS0000605574,NHMSYS0001697250,NHMSYS0000605569,NHMSYS0000876769}'::text[])))
  • Filter: ((NOT training) AND (record_status <> 'R'::bpchar) AND (taxon_path && '{134903,87889}'::integer[]) AND (taxon_group_id = 110) AND (website_id = ANY ('{109,23,3,69}'::integer[])) AND ((record_status = 'V'::bpchar) OR (record_substatus <= 3)) AND ((website_id = 109) OR (created_by_id = 1) OR (blocked_sharing_tasks IS NULL) OR (NOT (blocked_sharing_tasks @> '{R}'::character(1)[]))))
  • Rows Removed by Filter: 1549
14. 215.357 215.357 ↑ 1.0 1 6,947

Index Scan using pk_cache_occurrences_nonfunctional on cache_occurrences_nonfunctional onf (cost=0.43..8.45 rows=1 width=103) (actual time=0.031..0.031 rows=1 loops=6,947)

  • Index Cond: (id = o.id)
15. 194.516 194.516 ↑ 1.0 1 6,947

Index Scan using pk_occurrences on occurrences occ (cost=0.43..0.98 rows=1 width=60) (actual time=0.028..0.028 rows=1 loops=6,947)

  • Index Cond: (id = onf.id)
16. 180.622 180.622 ↑ 1.0 1 6,947

Index Scan using pk_cache_samples_nonfunctional on cache_samples_nonfunctional snf (cost=0.43..8.45 rows=1 width=44) (actual time=0.026..0.026 rows=1 loops=6,947)

  • Index Cond: (id = o.sample_id)
17. 173.675 173.675 ↑ 1.0 1 6,947

Index Scan using pk_samples on samples s (cost=0.43..0.92 rows=1 width=15) (actual time=0.025..0.025 rows=1 loops=6,947)

  • Index Cond: (id = snf.id)
  • Filter: (NOT deleted)
18. 27.788 27.788 ↑ 1.0 1 6,947

Index Scan using pk_cache_taxa_taxon_lists on cache_taxa_taxon_lists cttl (cost=0.42..8.44 rows=1 width=71) (actual time=0.004..0.004 rows=1 loops=6,947)

  • Index Cond: (id = o.taxa_taxon_list_id)
19. 4,015.366 4,015.366 ↑ 1.0 4 6,947

Seq Scan on index_websites_website_agreements canedit (cost=0.00..63.31 rows=4 width=8) (actual time=0.018..0.578 rows=4 loops=6,947)

  • Filter: (provide_for_editing AND (to_website_id = 109))
  • Rows Removed by Filter: 3418
20. 62.523 62.523 ↑ 1.0 1 6,947

Seq Scan on system sys (cost=0.00..3.58 rows=1 width=0) (actual time=0.008..0.009 rows=1 loops=6,947)

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