explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qNlf

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 5,943.743 ↓ 0.0 0 1

Unique (cost=27,856.59..29,490.70 rows=2,660 width=32) (actual time=5,943.738..5,943.743 rows=0 loops=1)

2. 12.906 5,943.729 ↓ 0.0 0 1

Merge Anti Join (cost=27,856.59..29,484.05 rows=2,660 width=32) (actual time=5,943.723..5,943.729 rows=0 loops=1)

  • Merge Cond: (e0.id = b4.entity_instance_id)
3. 24.877 5,911.406 ↑ 2.8 969 1

Nested Loop (cost=27,784.38..29,400.06 rows=2,689 width=32) (actual time=5,526.731..5,911.406 rows=969 loops=1)

4. 95.330 5,870.056 ↑ 2.8 969 1

Merge Join (cost=27,783.96..28,182.55 rows=2,689 width=32) (actual time=5,526.696..5,870.056 rows=969 loops=1)

  • Merge Cond: (o0.entity_instance_id = e1.entity_instance_id)
5. 150.018 3,672.262 ↑ 1.8 11,048 1

Unique (cost=10,416.09..10,514.76 rows=19,733 width=16) (actual time=3,442.015..3,672.262 rows=11,048 loops=1)

6. 166.996 3,522.244 ↑ 1.6 12,415 1

Sort (cost=10,416.09..10,465.43 rows=19,733 width=16) (actual time=3,441.995..3,522.244 rows=12,415 loops=1)

  • Sort Key: o0.entity_instance_id
  • Sort Method: quicksort Memory: 967kB
7. 598.608 3,355.248 ↑ 1.6 12,419 1

Hash Join (cost=6,553.82..9,008.31 rows=19,733 width=16) (actual time=2,254.617..3,355.248 rows=12,419 loops=1)

  • Hash Cond: (o0.id = o1.office_instance_id)
8. 502.289 502.289 ↑ 1.0 76,908 1

Seq Scan on office_instances o0 (cost=0.00..1,488.08 rows=76,908 width=32) (actual time=0.021..502.289 rows=76,908 loops=1)

9. 86.453 2,254.351 ↑ 1.6 12,419 1

Hash (cost=6,307.16..6,307.16 rows=19,733 width=16) (actual time=2,254.345..2,254.351 rows=12,419 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 839kB
10. 1,076.840 2,167.898 ↑ 1.6 12,419 1

Hash Join (cost=248.99..6,307.16 rows=19,733 width=16) (actual time=28.802..2,167.898 rows=12,419 loops=1)

  • Hash Cond: (o1.value_option_id = c0_1.id)
11. 1,062.504 1,062.504 ↓ 1.0 150,720 1

Seq Scan on office_facts o1 (cost=0.00..5,389.96 rows=150,314 width=32) (actual time=0.024..1,062.504 rows=150,720 loops=1)

  • Filter: ((dataset)::text = ANY ('{cvca_member,aca,naco,enhanced,public}'::text[]))
  • Rows Removed by Filter: 13893
12. 7.733 28.554 ↓ 1.0 823 1

Hash (cost=239.12..239.12 rows=789 width=16) (actual time=28.548..28.554 rows=823 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
13. 20.821 20.821 ↓ 1.0 823 1

Seq Scan on concept_options c0_1 (cost=0.00..239.12 rows=789 width=16) (actual time=0.082..20.821 rows=823 loops=1)

  • Filter: (unique_key ~~* 'Places::CAN%'::text)
  • Rows Removed by Filter: 5187
14. 37.708 2,102.464 ↑ 1.9 2,787 1

Sort (cost=17,367.87..17,381.05 rows=5,273 width=16) (actual time=2,084.316..2,102.464 rows=2,787 loops=1)

  • Sort Key: e1.entity_instance_id
  • Sort Method: quicksort Memory: 227kB
15. 932.966 2,064.756 ↑ 1.9 2,787 1

Merge Join (cost=241.36..17,041.88 rows=5,273 width=16) (actual time=140.943..2,064.756 rows=2,787 loops=1)

  • Merge Cond: (e1.value_option_id = c0.id)
16. 1,104.036 1,104.036 ↑ 3.8 135,200 1

Index Scan using entity_facts_value_entity_concept_option_id_index on entity_facts e1 (cost=0.42..57,420.52 rows=519,556 width=32) (actual time=0.070..1,104.036 rows=135,200 loops=1)

  • Filter: ((dataset)::text = ANY ('{cvca_member,aca,naco,enhanced,public}'::text[]))
  • Rows Removed by Filter: 22342
17. 18.003 27.754 ↓ 37.7 2,297 1

Sort (cost=240.93..241.09 rows=61 width=16) (actual time=9.798..27.754 rows=2,297 loops=1)

  • Sort Key: c0.id
  • Sort Method: quicksort Memory: 25kB
18. 9.751 9.751 ↑ 10.2 6 1

Seq Scan on concept_options c0 (cost=0.00..239.12 rows=61 width=16) (actual time=0.740..9.751 rows=6 loops=1)

  • Filter: (unique_key ~~* 'Business::Company%'::text)
  • Rows Removed by Filter: 6004
19. 16.473 16.473 ↑ 1.0 1 969

Index Scan using entity_instances_pkey on entity_instances e0 (cost=0.42..0.44 rows=1 width=32) (actual time=0.011..0.017 rows=1 loops=969)

  • Index Cond: (id = e1.entity_instance_id)
20. 12.893 19.417 ↑ 1.0 955 1

Sort (cost=72.21..74.59 rows=955 width=16) (actual time=13.005..19.417 rows=955 loops=1)

  • Sort Key: b4.entity_instance_id
  • Sort Method: quicksort Memory: 69kB
21. 6.524 6.524 ↑ 1.0 955 1

Seq Scan on benefits b4 (cost=0.00..24.94 rows=955 width=16) (actual time=0.016..6.524 rows=955 loops=1)

  • Filter: (reward_id = '1f474f4a-883b-438b-bab0-8064edd8e849'::uuid)