explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T2lQL

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 12,954.382 ↓ 0.0 20 1

Sort (cost=0..0 rows=0 width=0) (actual time=12,954.376..12,954.382 rows=20 loops=1)

  • Sort Key: ((n.count * 1))
  • Sort Method: quicksort Memory: 29kB
2. 0.013 12,954.337 ↓ 0.0 20 1

Subquery Scan on n (cost=0..0 rows=0 width=0) (actual time=12,954.299..12,954.337 rows=20 loops=1)

3. 0.024 12,954.324 ↓ 0.0 20 1

Unique (cost=0..0 rows=0 width=0) (actual time=12,954.296..12,954.324 rows=20 loops=1)

4. 0.051 12,954.300 ↓ 0.0 20 1

Sort (cost=0..0 rows=0 width=0) (actual time=12,954.294..12,954.300 rows=20 loops=1)

  • Sort Key: ((min(((first_value(n1.text) OVER (?)))::text))::d_longname), ((sum((n1.count)::integer))::integer), (max((n1.doc_count)::integer)), (jsonb_agg(jsonb_build_object('text', n1.text, 'count', n1.count, 'doc_count', n1.doc_count) ORDER BY n1.count DESC, n1.text))
  • Sort Method: quicksort Memory: 29kB
5. 0.287 12,954.249 ↓ 0.0 20 1

GroupAggregate (cost=0..0 rows=0 width=0) (actual time=12,953.987..12,954.249 rows=20 loops=1)

  • Group Key: n_1.ner_id
6. 0.046 12,953.962 ↓ 0.0 21 1

WindowAgg (cost=0..0 rows=0 width=0) (actual time=12,953.917..12,953.962 rows=21 loops=1)

7. 0.067 12,953.916 ↓ 0.0 21 1

Sort (cost=0..0 rows=0 width=0) (actual time=12,953.909..12,953.916 rows=21 loops=1)

  • Sort Key: n_1.ner_id, n1.count DESC
  • Sort Method: quicksort Memory: 26kB
8. 1.365 12,953.849 ↓ 0.0 21 1

Nested Loop (cost=0..0 rows=0 width=0) (actual time=8,346.885..12,953.849 rows=21 loops=1)

9. 2.903 8,343.170 ↓ 0.0 489 1

Nested Loop Left Join (cost=0..0 rows=0 width=0) (actual time=8,339.241..8,343.170 rows=489 loops=1)

  • Join Filter: ((cardinality(n_1.norm_words) > 1) AND (n_1.norm_words[cardinality(n_1.norm_words)] !~ '^(?:[0123456789].*)$'::text))
  • Rows Removed by Join Filter: 884
10. 0.019 8,339.247 ↓ 0.0 20 1

Limit (cost=0..0 rows=0 width=0) (actual time=8,339.204..8,339.247 rows=20 loops=1)

11. 484.578 8,339.228 ↓ 0.0 20 1

Sort (cost=0..0 rows=0 width=0) (actual time=8,339.203..8,339.228 rows=20 loops=1)

  • Sort Key: (((n_1.count)::integer * 1))
  • Sort Method: top-N heapsort Memory: 27kB
12. 1,171.530 7,854.650 ↓ 0.0 626,132 1

Nested Loop Anti Join (cost=0..0 rows=0 width=0) (actual time=0.044..7,854.650 rows=626,132 loops=1)

13. 421.800 421.800 ↓ 0.0 626,132 1

Seq Scan on ner n_1 (cost=0..0 rows=0 width=0) (actual time=0.009..421.800 rows=626,132 loops=1)

  • Filter: ((entity_id IS NULL) AND ((count)::integer > 0) AND ((entity_type_id)::integer = 6))
  • Rows Removed by Filter: 51825
14. 1,836.470 6,261.320 ↓ 0.0 0 626,132

Nested Loop (cost=0..0 rows=0 width=0) (actual time=0.010..0.010 rows=0 loops=626,132)

15. 3,756.792 3,756.792 ↓ 0.0 0 626,132

Index Scan using idx_pattern_text_entity_id on pattern p (cost=0..0 rows=0 width=0) (actual time=0.006..0.006 rows=0 loops=626,132)

  • Index Cond: (lower((text)::text) = ANY (ARRAY[lower((n_1.text)::text), (n_1.norm_text)::text]))
  • Filter: enabled
  • Rows Removed by Filter: 0
16. 668.058 668.058 ↓ 0.0 0 222,686

Index Scan using pk_m_entity on entity e (cost=0..0 rows=0 width=0) (actual time=0.003..0.003 rows=0 loops=222,686)

  • Index Cond: ((entity_id)::integer = (p.entity_id)::integer)
  • Filter: (enabled AND ((entity_type)::integer = ANY ('{5498,6,653,5511,608,587,335}'::integer[])))
  • Rows Removed by Filter: 1
17. 0.500 1.020 ↓ 0.0 68 20

Materialize (cost=0..0 rows=0 width=0) (actual time=0.001..0.051 rows=68 loops=20)

18. 0.044 0.520 ↓ 0.0 68 1

Append (cost=0..0 rows=0 width=0) (actual time=0.002..0.520 rows=68 loops=1)

19. 0.001 0.001 ↓ 0.0 1 1

Result (cost=0..0 rows=0 width=0) (actual time=0.001..0.001 rows=1 loops=1)

20. 0.475 0.475 ↓ 0.0 67 1

Index Scan using idx_pattern_text_entity_id on pattern p_1 (cost=0..0 rows=0 width=0) (actual time=0.028..0.475 rows=67 loops=1)

  • Index Cond: ((lower((text)::text) ~>=~ 'synonym'::text) AND (lower((text)::text) ~<~ 'synonyn'::text))
  • Filter: (enabled AND is_macro AND (lower((text)::text) ~~ 'synonym%'::text))
21. 2.934 4,609.314 ↓ 0.0 0 489

Bitmap Heap Scan on ner n1 (cost=0..0 rows=0 width=0) (actual time=9.426..9.426 rows=0 loops=489)

  • Recheck Cond: ((norm_words = CASE WHEN ((NULL::text) IS NULL) THEN n_1.norm_words ELSE (n_1.norm_words || (NULL::text)) END) AND (entity_id IS NULL) AND ((count)::integer > 0))
  • Rows Removed by Index Recheck: 0
  • Filter: ((entity_type_id)::integer = 6)
  • Heap Blocks: exact=31
22. 4,606.380 4,606.380 ↓ 0.0 0 489

Bitmap Index Scan on idx_ner_norm_words (cost=0..0 rows=0 width=0) (actual time=9.420..9.420 rows=0 loops=489)

  • Index Cond: (norm_words = CASE WHEN ((NULL::text) IS NULL) THEN n_1.norm_words ELSE (n_1.norm_words || (NULL::text)) END)
Planning time : 1.488 ms
Execution time : 12,954.553 ms