explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yPj5

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 338.463 ↑ 1.0 13 1

Limit (cost=39,260.25..39,260.29 rows=13 width=56) (actual time=338.453..338.463 rows=13 loops=1)

2. 0.011 338.454 ↑ 18.5 13 1

Sort (cost=39,260.25..39,260.86 rows=241 width=56) (actual time=338.453..338.454 rows=13 loops=1)

  • Sort Key: (count(DISTINCT e0.id)) DESC
  • Sort Method: quicksort Memory: 26kB
3. 2.941 338.443 ↑ 18.5 13 1

GroupAggregate (cost=39,249.77..39,254.59 rows=241 width=56) (actual time=335.086..338.443 rows=13 loops=1)

  • Group Key: ((regexp_matches(c3.unique_key, '^[[:alnum:]]+::[[:alnum:]]+::[[:alnum:]]+'::text))[1])
4. 5.154 335.502 ↓ 45.2 10,889 1

Sort (cost=39,249.77..39,250.37 rows=241 width=48) (actual time=334.811..335.502 rows=10,889 loops=1)

  • Sort Key: ((regexp_matches(c3.unique_key, '^[[:alnum:]]+::[[:alnum:]]+::[[:alnum:]]+'::text))[1])
  • Sort Method: quicksort Memory: 1235kB
5. 32.721 330.348 ↓ 45.2 10,889 1

Nested Loop Left Join (cost=33,442.56..39,240.23 rows=241 width=48) (actual time=191.389..330.348 rows=10,889 loops=1)

6. 3.137 286.483 ↓ 46.2 11,144 1

Hash Left Join (cost=33,442.28..39,159.94 rows=241 width=32) (actual time=191.363..286.483 rows=11,144 loops=1)

  • Hash Cond: (s1.id = o0.office_instance_id)
7. 4.153 274.297 ↓ 46.2 11,144 1

Merge Join (cost=30,881.03..36,597.15 rows=241 width=32) (actual time=182.300..274.297 rows=11,144 loops=1)

  • Merge Cond: (e0.id = s1.entity_instance_id)
8. 11.151 209.578 ↑ 1.7 19,217 1

Unique (cost=28,133.44..33,431.33 rows=33,170 width=32) (actual time=123.568..209.578 rows=19,217 loops=1)

9. 27.364 198.427 ↑ 1.0 167,638 1

Merge Join (cost=28,133.44..33,005.65 rows=170,271 width=32) (actual time=123.567..198.427 rows=167,638 loops=1)

  • Merge Cond: (e0.id = e1.entity_instance_id)
10. 10.686 10.686 ↑ 1.0 32,599 1

Index Only Scan using entity_instances_pkey on entity_instances e0 (cost=0.29..1,809.83 rows=33,170 width=16) (actual time=0.008..10.686 rows=32,599 loops=1)

  • Heap Fetches: 32267
11. 15.276 160.377 ↑ 1.0 167,638 1

Materialize (cost=28,133.15..28,984.51 rows=170,271 width=16) (actual time=123.555..160.377 rows=167,638 loops=1)

12. 90.422 145.101 ↑ 1.0 167,638 1

Sort (cost=28,133.15..28,558.83 rows=170,271 width=16) (actual time=123.553..145.101 rows=167,638 loops=1)

  • Sort Key: e1.entity_instance_id
  • Sort Method: external merge Disk: 4256kB
13. 54.679 54.679 ↑ 1.0 167,638 1

Seq Scan on entity_facts e1 (cost=0.00..10,426.75 rows=170,271 width=16) (actual time=0.006..54.679 rows=167,638 loops=1)

  • Filter: ((dataset)::text = ANY ('{public}'::text[]))
  • Rows Removed by Filter: 184166
14. 5.653 60.566 ↓ 46.2 11,144 1

Sort (cost=2,747.59..2,748.19 rows=241 width=32) (actual time=58.729..60.566 rows=11,144 loops=1)

  • Sort Key: s1.entity_instance_id
  • Sort Method: quicksort Memory: 1255kB
15. 0.962 54.913 ↓ 46.2 11,144 1

Subquery Scan on s1 (cost=2,730.52..2,738.05 rows=241 width=32) (actual time=47.071..54.913 rows=11,144 loops=1)

16. 5.428 53.951 ↓ 46.2 11,144 1

GroupAggregate (cost=2,730.52..2,735.64 rows=241 width=48) (actual time=47.070..53.951 rows=11,144 loops=1)

  • Group Key: o0_1.id
  • Filter: bool_or((((c2.unique_key)::text = ANY ('{Office::Address::Place}'::text[])) AND ((c3_1.unique_key = ANY ('{Places::CAN}'::text[])) OR (c3_1.unique_key ~~ ANY ('{Places::CAN::%}'::text[])))))
  • Rows Removed by Filter: 1479
17. 6.068 48.523 ↓ 52.4 12,623 1

Sort (cost=2,730.52..2,731.12 rows=241 width=134) (actual time=47.060..48.523 rows=12,623 loops=1)

  • Sort Key: o0_1.id
  • Sort Method: quicksort Memory: 2160kB
18. 3.878 42.455 ↓ 52.4 12,623 1

Nested Loop Left Join (cost=476.03..2,720.99 rows=241 width=134) (actual time=3.322..42.455 rows=12,623 loops=1)

19. 5.468 25.954 ↓ 52.4 12,623 1

Nested Loop (cost=475.75..2,641.30 rows=241 width=72) (actual time=3.315..25.954 rows=12,623 loops=1)

20. 1.478 7.863 ↓ 52.4 12,623 1

Nested Loop (cost=475.46..2,558.24 rows=241 width=56) (actual time=3.306..7.863 rows=12,623 loops=1)

21. 0.036 0.036 ↑ 1.0 1 1

Seq Scan on concepts c2 (cost=0.00..7.28 rows=1 width=40) (actual time=0.013..0.036 rows=1 loops=1)

  • Filter: ((unique_key)::text = ANY ('{Office::Address::Place}'::text[]))
  • Rows Removed by Filter: 211
22. 4.768 6.349 ↓ 1.5 12,623 1

Bitmap Heap Scan on office_facts o1 (cost=475.46..2,469.31 rows=8,165 width=48) (actual time=3.290..6.349 rows=12,623 loops=1)

  • Recheck Cond: (concept_id = c2.id)
  • Filter: ((dataset)::text = ANY ('{public}'::text[]))
  • Rows Removed by Filter: 17447
  • Heap Blocks: exact=504
23. 1.581 1.581 ↓ 1.7 30,085 1

Bitmap Index Scan on office_facts_concept_id_index (cost=0.00..473.42 rows=17,734 width=0) (actual time=1.581..1.581 rows=30,085 loops=1)

  • Index Cond: (concept_id = c2.id)
24. 12.623 12.623 ↑ 1.0 1 12,623

Index Scan using office_instances_pkey on office_instances o0_1 (cost=0.29..0.33 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=12,623)

  • Index Cond: (id = o1.office_instance_id)
25. 12.623 12.623 ↑ 1.0 1 12,623

Index Scan using entity_concept_options_pkey on concept_options c3_1 (cost=0.28..0.32 rows=1 width=94) (actual time=0.001..0.001 rows=1 loops=12,623)

  • Index Cond: (id = o1.value_option_id)
26. 1.896 9.049 ↓ 52.4 12,623 1

Hash (cost=2,558.24..2,558.24 rows=241 width=32) (actual time=9.049..9.049 rows=12,623 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 917kB
27. 1.449 7.153 ↓ 52.4 12,623 1

Nested Loop (cost=475.46..2,558.24 rows=241 width=32) (actual time=3.238..7.153 rows=12,623 loops=1)

28. 0.033 0.033 ↑ 1.0 1 1

Seq Scan on concepts c1 (cost=0.00..7.28 rows=1 width=16) (actual time=0.011..0.033 rows=1 loops=1)

  • Filter: ((unique_key)::text = ANY ('{Office::Address::Place}'::text[]))
  • Rows Removed by Filter: 211
29. 4.128 5.671 ↓ 1.5 12,623 1

Bitmap Heap Scan on office_facts o0 (cost=475.46..2,469.31 rows=8,165 width=48) (actual time=3.225..5.671 rows=12,623 loops=1)

  • Recheck Cond: (concept_id = c1.id)
  • Filter: ((dataset)::text = ANY ('{public}'::text[]))
  • Rows Removed by Filter: 17447
  • Heap Blocks: exact=504
30. 1.543 1.543 ↓ 1.7 30,085 1

Bitmap Index Scan on office_facts_concept_id_index (cost=0.00..473.42 rows=17,734 width=0) (actual time=1.543..1.543 rows=30,085 loops=1)

  • Index Cond: (concept_id = c1.id)
31. 11.144 11.144 ↑ 1.0 1 11,144

Index Scan using entity_concept_options_pkey on concept_options c3 (cost=0.28..0.32 rows=1 width=94) (actual time=0.001..0.001 rows=1 loops=11,144)

  • Index Cond: (id = o0.value_option_id)
Planning time : 1.042 ms