explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7sIk

Settings
# exclusive inclusive rows x rows loops node
1. 2.500 16,685.714 ↓ 46.0 46 1

GroupAggregate (cost=2,180.24..2,180.24 rows=1 width=247) (actual time=16,683.290..16,685.714 rows=46 loops=1)

  • Group Key: tb_pivot_news.id_news, tb_pivot_news_agency.id_agency, tb_pivot_news_agency.ds_agency, tb_pivot_news_agency.ds_agency_nickname, tb_pivot_news_agency.hr_relevancy
2. 5.515 16,683.214 ↓ 2,663.0 2,663 1

Sort (cost=2,180.24..2,180.24 rows=1 width=161) (actual time=16,683.104..16,683.214 rows=2,663 loops=1)

  • Sort Key: tb_pivot_news.id_news, tb_pivot_news_agency.id_agency, tb_pivot_news_agency.ds_agency, tb_pivot_news_agency.ds_agency_nickname, tb_pivot_news_agency.hr_relevancy
  • Sort Method: quicksort Memory: 804kB
3. 2,102.062 16,677.699 ↓ 2,663.0 2,663 1

Nested Loop (cost=2.15..2,180.23 rows=1 width=161) (actual time=100.866..16,677.699 rows=2,663 loops=1)

  • Join Filter: (tb_pivot_news.id_news = tb_agency_tag_pivot_news_1.id_news)
  • Rows Removed by Join Filter: 31,521,453
4. 10.072 239.285 ↓ 11,062.0 11,062 1

Nested Loop Left Join (cost=2.12..2,037.97 rows=1 width=161) (actual time=74.601..239.285 rows=11,062 loops=1)

5. 8.255 196.027 ↓ 11,062.0 11,062 1

Nested Loop Left Join (cost=2.10..2,037.95 rows=1 width=150) (actual time=74.593..196.027 rows=11,062 loops=1)

6. 11.262 163.037 ↓ 4,947.0 4,947 1

Nested Loop Left Join (cost=2.07..2,035.92 rows=1 width=146) (actual time=74.561..163.037 rows=4,947 loops=1)

7. 15.288 117.650 ↓ 4,875.0 4,875 1

Nested Loop (cost=2.05..2,035.89 rows=1 width=124) (actual time=74.522..117.650 rows=4,875 loops=1)

  • Join Filter: (tb_pivot_news.id_agency = tb_pivot_news_agency.id_agency)
  • Rows Removed by Join Filter: 4,289
8. 35.561 87.737 ↓ 4,875.0 4,875 1

Hash Right Join (cost=2.05..2,034.88 rows=1 width=102) (actual time=74.499..87.737 rows=4,875 loops=1)

  • Hash Cond: (tb_pivot_news_tag.id_news = tb_pivot_news.id_news)
9. 48.420 48.420 ↓ 1.0 276,586 1

Seq Scan on tb_pivot_news_tag (cost=0.00..1,960.30 rows=276,301 width=9) (actual time=0.018..48.420 rows=276,586 loops=1)

10. 0.115 3.756 ↓ 369.0 369 1

Hash (cost=2.04..2.04 rows=1 width=97) (actual time=3.756..3.756 rows=369 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 56kB
11. 3.641 3.641 ↓ 369.0 369 1

Index Scan using tb_pivot_news_id_contract_dh_news_idx on tb_pivot_news (cost=0.04..2.04 rows=1 width=97) (actual time=0.061..3.641 rows=369 loops=1)

  • Index Cond: ((id_contract = 2,020) AND (dh_news >= '2020-09-08 00:00:00'::timestamp without time zone) AND (dh_news <= '2020-09-09 23:00:00'::timestamp without time zone))
12. 14.625 14.625 ↑ 1.0 2 4,875

Seq Scan on tb_pivot_news_agency (cost=0.00..1.01 rows=2 width=34) (actual time=0.002..0.003 rows=2 loops=4,875)

  • Filter: (id_contract = 2,020)
  • Rows Removed by Filter: 3
13. 34.125 34.125 ↓ 0.0 0 4,875

Index Only Scan using tb_reuters_news_topics_catalog_cd_topic_ds_topic_pt_full_idx on tb_reuters_news_topics_catalog (cost=0.03..0.03 rows=1 width=26) (actual time=0.007..0.007 rows=0 loops=4,875)

  • Index Cond: (cd_topic = (tb_pivot_news_tag.tag)::text)
  • Heap Fetches: 0
14. 24.735 24.735 ↑ 1.0 2 4,947

Index Only Scan using tb_agency_tag_pivot_news_pk on tb_agency_tag_pivot_news (cost=0.03..2.03 rows=2 width=8) (actual time=0.004..0.005 rows=2 loops=4,947)

  • Index Cond: (id_news = tb_pivot_news.id_news)
  • Heap Fetches: 10,311
15. 33.186 33.186 ↑ 1.0 1 11,062

Index Scan using tb_agency_tag_pk on tb_agency_tag (cost=0.01..0.02 rows=1 width=15) (actual time=0.003..0.003 rows=1 loops=11,062)

  • Index Cond: (id_agency_tag = tb_agency_tag_pivot_news.id_agency_tag)
16. 4,048.692 14,336.352 ↓ 1.1 2,850 11,062

Unique (cost=0.03..136.36 rows=2,625 width=4) (actual time=0.068..1.296 rows=2,850 loops=11,062)

17. 10,287.660 10,287.660 ↓ 1.0 2,850 11,062

Index Only Scan using tb_agency_tag_pivot_news_pk on tb_agency_tag_pivot_news tb_agency_tag_pivot_news_1 (cost=0.03..135.67 rows=2,766 width=4) (actual time=0.068..0.930 rows=2,850 loops=11,062)

  • Index Cond: (id_agency_tag = 20)
  • Heap Fetches: 2,364,684
Planning time : 6.254 ms
Execution time : 16,686.122 ms