explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qQhv

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 898.895 ↓ 0.0 0 1

Limit (cost=350.82..350.82 rows=1 width=106) (actual time=898.895..898.895 rows=0 loops=1)

2.          

CTE with_categorias

3. 0.032 0.480 ↑ 8.6 73 1

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

4. 0.023 0.073 ↑ 1.0 11 1

Hash Anti Join (cost=2.40..4.50 rows=11 width=16) (actual time=0.047..0.073 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.017 0.039 ↑ 1.0 62 1

Hash (cost=1.62..1.62 rows=62 width=4) (actual time=0.039..0.039 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.375 ↑ 5.2 12 5

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

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

Hash Join (cost=2.40..5.02 rows=62 width=20) (actual time=0.014..0.050 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.033 0.052 ↑ 1.0 62 1

Hash (cost=1.62..1.62 rows=62 width=8) (actual time=0.052..0.052 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.011 898.894 ↓ 0.0 0 1

Sort (cost=234.59..234.59 rows=1 width=106) (actual time=898.894..898.894 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
16. 0.001 898.883 ↓ 0.0 0 1

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

17. 894.295 898.882 ↓ 0.0 0 1

Nested Loop (cost=14.48..233.83 rows=1 width=579) (actual time=898.882..898.882 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
18. 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)

19. 1.205 4.558 ↓ 59.6 894 1

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 0.517 0.517 ↓ 10.0 30 1

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

  • Filter: (id_restrito = 0)
  • Rows Removed by Filter: 43
23. 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.792 ms
Execution time : 899.026 ms