explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bMiQf

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 902.432 ↓ 0.0 0 1

Sort (cost=350.82..350.82 rows=1 width=106) (actual time=902.432..902.432 rows=0 loops=1)

  • Sort Key: lk.id_like DESC NULLS LAST, (ts_rank_cd(to_tsvector(id.ds_titulo), query.query)) DESC, (ts_rank_cd(to_tsvector(regexp_replace(id.ds_item_documentacao, '<[^>]+>'::text, ''::text, 'gi'::text)), query.query)) DESC, id.qt_likes DESC, id.qt_acessos DESC, id.nr_ordem, id.ds_titulo
  • Sort Method: quicksort Memory: 25kB
2.          

CTE with_categorias

3. 0.032 0.461 ↑ 8.6 73 1

Recursive Union (cost=2.40..116.23 rows=631 width=38) (actual time=0.049..0.461 rows=73 loops=1)

4. 0.023 0.074 ↑ 1.0 11 1

Hash Anti Join (cost=2.40..4.50 rows=11 width=16) (actual time=0.048..0.074 rows=11 loops=1)

  • Hash Cond: (c.cd_categoria = ch.cd_categoria_filha)
5. 0.011 0.011 ↑ 1.0 73 1

Seq Scan on categoria c (cost=0.00..1.73 rows=73 width=16) (actual time=0.003..0.011 rows=73 loops=1)

6. 0.018 0.040 ↑ 1.0 62 1

Hash (cost=1.62..1.62 rows=62 width=4) (actual time=0.040..0.040 rows=62 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
7. 0.022 0.022 ↑ 1.0 62 1

Seq Scan on categoria_hierarquia ch (cost=0.00..1.62 rows=62 width=4) (actual time=0.007..0.022 rows=62 loops=1)

8. 0.090 0.355 ↑ 5.2 12 5

Hash Join (cost=5.97..9.91 rows=62 width=38) (actual time=0.036..0.071 rows=12 loops=5)

  • Hash Cond: (ch_1.cd_categoria_mae = cf.cd_categoria)
9. 0.139 0.230 ↑ 1.0 62 5

Hash Join (cost=2.40..5.02 rows=62 width=20) (actual time=0.011..0.046 rows=62 loops=5)

  • Hash Cond: (c_1.cd_categoria = ch_1.cd_categoria_filha)
10. 0.055 0.055 ↑ 1.0 73 5

Seq Scan on categoria c_1 (cost=0.00..1.73 rows=73 width=16) (actual time=0.002..0.011 rows=73 loops=5)

11. 0.017 0.036 ↑ 1.0 62 1

Hash (cost=1.62..1.62 rows=62 width=8) (actual time=0.036..0.036 rows=62 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
12. 0.019 0.019 ↑ 1.0 62 1

Seq Scan on categoria_hierarquia ch_1 (cost=0.00..1.62 rows=62 width=8) (actual time=0.002..0.019 rows=62 loops=1)

13. 0.020 0.035 ↑ 7.3 15 5

Hash (cost=2.20..2.20 rows=110 width=38) (actual time=0.007..0.007 rows=15 loops=5)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.015 0.015 ↑ 7.3 15 5

WorkTable Scan on with_categorias cf (cost=0.00..2.20 rows=110 width=38) (actual time=0.001..0.003 rows=15 loops=5)

15. 0.001 902.421 ↓ 0.0 0 1

Nested Loop Left Join (cost=14.64..234.58 rows=1 width=106) (actual time=902.421..902.421 rows=0 loops=1)

16. 897.803 902.420 ↓ 0.0 0 1

Nested Loop (cost=14.48..233.83 rows=1 width=579) (actual time=902.420..902.420 rows=0 loops=1)

  • Join Filter: (query.query @@ to_tsvector(((id.ds_titulo || ' '::text) || regexp_replace(id.ds_item_documentacao, '<[^>]+>'::text, ''::text, 'gi'::text))))
  • Rows Removed by Join Filter: 894
17. 0.029 0.029 ↑ 1.0 1 1

Function Scan on to_tsquery query (cost=0.25..0.26 rows=1 width=32) (actual time=0.028..0.029 rows=1 loops=1)

18. 1.241 4.588 ↓ 59.6 894 1

Hash Join (cost=14.23..229.52 rows=15 width=547) (actual time=0.527..4.588 rows=894 loops=1)

  • Hash Cond: (id.cd_categoria = wc.cd_categoria)
19. 2.842 2.842 ↑ 1.0 1,003 1

Seq Scan on item_documentacao id (cost=0.00..211.38 rows=1,003 width=551) (actual time=0.015..2.842 rows=1,003 loops=1)

  • Filter: (id_publicado = 1)
  • Rows Removed by Filter: 1187
20. 0.008 0.505 ↓ 10.0 30 1

Hash (cost=14.20..14.20 rows=3 width=4) (actual time=0.505..0.505 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
21. 0.497 0.497 ↓ 10.0 30 1

CTE Scan on with_categorias wc (cost=0.00..14.20 rows=3 width=4) (actual time=0.051..0.497 rows=30 loops=1)

  • Filter: (id_restrito = 0)
  • Rows Removed by Filter: 43
22. 0.000 0.000 ↓ 0.0 0

Index Scan using item_documentacao_like_pkey on item_documentacao_like lk (cost=0.15..0.22 rows=1 width=6) (never executed)

  • Index Cond: ((id.cd_item_documentacao = cd_item_documentacao) AND (id_fingerprint = '2471493566'::bigint))
Planning time : 0.773 ms
Execution time : 902.560 ms