explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GyMK

Settings
# exclusive inclusive rows x rows loops node
1. 0.041 936.760 ↓ 0.0 0 1

Sort (cost=353.16..353.16 rows=1 width=106) (actual time=936.760..936.760 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.031 0.518 ↑ 8.6 73 1

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

4. 0.029 0.092 ↑ 1.0 11 1

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

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

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

6. 0.021 0.050 ↑ 1.0 62 1

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

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

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

8. 0.105 0.395 ↑ 5.2 12 5

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
12. 0.020 0.020 ↑ 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.020 rows=62 loops=1)

13. 0.025 0.045 ↑ 7.3 15 5

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

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

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

15. 0.001 936.719 ↓ 0.0 0 1

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

16. 909.377 936.718 ↓ 0.0 0 1

Nested Loop (cost=14.48..236.17 rows=1 width=579) (actual time=936.718..936.718 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. 22.504 22.504 ↑ 1.0 1 1

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

18. 1.205 4.837 ↓ 59.6 894 1

Hash Join (cost=14.23..231.86 rows=15 width=547) (actual time=0.627..4.837 rows=894 loops=1)

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

Seq Scan on item_documentacao id (cost=0.00..213.68 rows=1,014 width=551) (actual time=0.023..3.063 rows=1,003 loops=1)

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

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

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

CTE Scan on with_categorias wc (cost=0.00..14.20 rows=3 width=4) (actual time=0.070..0.559 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 : 1.926 ms
Execution time : 936.961 ms