explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YVLS

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 1,212.122 ↓ 10.0 10 1

Limit (cost=350.82..350.82 rows=1 width=106) (actual time=1,212.119..1,212.122 rows=10 loops=1)

2.          

CTE with_categorias

3. 0.034 0.470 ↑ 8.6 73 1

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

4. 0.022 0.076 ↑ 1.0 11 1

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

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

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

6. 0.017 0.042 ↑ 1.0 62 1

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

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

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

8. 0.085 0.360 ↑ 5.2 12 5

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

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

Hash Join (cost=2.40..5.02 rows=62 width=20) (actual time=0.011..0.047 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.019 0.038 ↑ 1.0 62 1

Hash (cost=1.62..1.62 rows=62 width=8) (actual time=0.038..0.038 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.025 0.040 ↑ 7.3 15 5

Hash (cost=2.20..2.20 rows=110 width=38) (actual time=0.008..0.008 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.440 1,212.118 ↓ 10.0 10 1

Sort (cost=234.59..234.59 rows=1 width=106) (actual time=1,212.117..1,212.118 rows=10 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: top-N heapsort Memory: 32kB
16. 316.777 1,211.678 ↓ 111.0 111 1

Nested Loop Left Join (cost=14.64..234.58 rows=1 width=106) (actual time=16.476..1,211.678 rows=111 loops=1)

17. 889.486 894.346 ↓ 111.0 111 1

Nested Loop (cost=14.48..233.83 rows=1 width=579) (actual time=16.195..894.346 rows=111 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: 783
18. 0.048 0.048 ↑ 1.0 1 1

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

19. 1.246 4.812 ↓ 59.6 894 1

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

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

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

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

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

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

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

  • Filter: (id_restrito = 0)
  • Rows Removed by Filter: 43
23. 0.555 0.555 ↓ 0.0 0 111

Index Scan using item_documentacao_like_pkey on item_documentacao_like lk (cost=0.15..0.22 rows=1 width=6) (actual time=0.005..0.005 rows=0 loops=111)

  • Index Cond: ((id.cd_item_documentacao = cd_item_documentacao) AND (id_fingerprint = '2471493566'::bigint))
Planning time : 0.809 ms
Execution time : 1,212.266 ms