explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dgeJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 16,191.562 ↓ 0.0 0 1

Merge Full Join (cost=2,582,014.38..2,582,052.80 rows=1,028 width=288) (actual time=16,191.562..16,191.562 rows=0 loops=1)

  • Merge Cond: ((details.entity_name = reports.entity_name) AND (details.seller_golden_record = reports.seller_golden_record) AND (details.breed_name = reports.breed_name) AND (details.class_name = reports.class_name))
2.          

CTE p

3. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=1)

4.          

CTE details_filtered

5. 0.000 2,046.241 ↓ 0.0 0 1

Unique (cost=369,137.06..369,143.39 rows=1,266 width=543) (actual time=2,046.241..2,046.241 rows=0 loops=1)

6. 0.026 2,046.241 ↓ 0.0 0 1

Sort (cost=369,137.06..369,140.23 rows=1,266 width=543) (actual time=2,046.240..2,046.241 rows=0 loops=1)

  • Sort Key: details_1.deal_details_id
  • Sort Method: quicksort Memory: 25kB
7. 169.453 2,046.215 ↓ 0.0 0 1

Hash Join (cost=1,041.51..369,071.83 rows=1,266 width=543) (actual time=2,046.215..2,046.215 rows=0 loops=1)

  • Hash Cond: ((details_1.seller_federal_district_name = p.district_) AND (seller_special_categories_bars.special_category_fkey = p.spec_))
  • Join Filter: ((date_part('year'::text, (details_1.deal_start_date)::timestamp without time zone) <= (p.year_)::double precision) AND (date_part('year'::text, (details_1.deal_end_date)::timestamp without time zone) >= (p.year_)::double precision))
8. 81.364 1,876.754 ↓ 1.8 496,211 1

Gather (cost=1,041.47..366,653.07 rows=276,920 width=547) (actual time=2.609..1,876.754 rows=496,211 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 288.403 1,795.390 ↓ 1.4 165,404 3

Hash Join (cost=41.48..337,961.07 rows=115,383 width=547) (actual time=1.069..1,795.390 rows=165,404 loops=3)

  • Hash Cond: (details_1.seller_party_inn = (seller_special_categories_bars.inn)::text)
  • Join Filter: (COALESCE((details_1.seller_party_special_category_fkey)::integer, seller_special_categories_bars.special_category_fkey) IS NOT NULL)
10. 1,506.500 1,506.500 ↑ 1.2 984,778 3

Parallel Seq Scan on pl_deal_details details_1 (cost=0.00..327,256.46 rows=1,228,480 width=543) (actual time=0.035..1,506.500 rows=984,778 loops=3)

  • Filter: ((NOT deal_details_is_buyer) AND (buyer_party_type_fkey <> 2))
  • Rows Removed by Filter: 446739
11. 0.208 0.487 ↑ 1.0 910 3

Hash (cost=30.10..30.10 rows=910 width=14) (actual time=0.486..0.487 rows=910 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 51kB
12. 0.279 0.279 ↑ 1.0 910 3

Seq Scan on party_special_categories_bars seller_special_categories_bars (cost=0.00..30.10 rows=910 width=14) (actual time=0.029..0.279 rows=910 loops=3)

13. 0.006 0.008 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=40) (actual time=0.008..0.008 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on p (cost=0.00..0.02 rows=1 width=40) (actual time=0.001..0.002 rows=1 loops=1)

15.          

CTE details

16. 0.007 2,046.261 ↓ 0.0 0 1

HashAggregate (cost=101,991.75..101,995.75 rows=400 width=217) (actual time=2,046.261..2,046.261 rows=0 loops=1)

  • Group Key: true, details_2.seller_federal_district_name, details_2.deal_details_wood_breed_name, details_2.deal_details_wood_class_name, details_2.seller_party_name, details_2.seller_party_inn, details_2.seller_bo_party_golden_record_fkey
17. 0.002 2,046.254 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.43..101,111.10 rows=27,097 width=255) (actual time=2,046.253..2,046.254 rows=0 loops=1)

18. 0.003 2,046.252 ↓ 0.0 0 1

Nested Loop (cost=0.00..38.00 rows=1,266 width=232) (actual time=2,046.252..2,046.252 rows=0 loops=1)

19. 0.006 0.006 ↑ 1.0 1 1

CTE Scan on p p_1 (cost=0.00..0.02 rows=1 width=0) (actual time=0.004..0.006 rows=1 loops=1)

20. 2,046.243 2,046.243 ↓ 0.0 0 1

CTE Scan on details_filtered details_2 (cost=0.00..25.32 rows=1,266 width=232) (actual time=2,046.243..2,046.243 rows=0 loops=1)

21. 0.000 0.000 ↓ 0.0 0

Index Scan using tmp_rgusev_20190802_pl_deal_underlying_deal_id on pl_deal_underlying underlying (cost=0.43..79.63 rows=21 width=63) (never executed)

  • Index Cond: (details_2.deal_id = deal_id)
22.          

CTE reports_filterd

23. 0.001 14,145.225 ↓ 0.0 0 1

Unique (cost=2,110,510.76..2,110,562.14 rows=5,138 width=833) (actual time=14,145.225..14,145.225 rows=0 loops=1)

24. 0.021 14,145.224 ↓ 0.0 0 1

Sort (cost=2,110,510.76..2,110,523.61 rows=5,138 width=833) (actual time=14,145.224..14,145.224 rows=0 loops=1)

  • Sort Key: reports_1.report_id, reports_1.report_details_wood_breed_name, reports_1.report_details_sort_name
  • Sort Method: quicksort Memory: 25kB
25. 0.012 14,145.203 ↓ 0.0 0 1

Hash Join (cost=44.20..2,108,297.08 rows=5,138 width=833) (actual time=14,145.203..14,145.203 rows=0 loops=1)

  • Hash Cond: ((reports_1.seller_party_inn = (seller_special_categories_bars_1.inn)::text) AND (p_2.spec_ = seller_special_categories_bars_1.special_category_fkey))
  • Join Filter: (COALESCE((reports_1.seller_party_special_category_fkey)::integer, seller_special_categories_bars_1.special_category_fkey) IS NOT NULL)
26. 0.017 14,145.191 ↓ 0.0 0 1

Nested Loop (cost=0.45..2,106,081.97 rows=140,454 width=837) (actual time=14,145.191..14,145.191 rows=0 loops=1)

27. 0.006 0.006 ↑ 1.0 1 1

CTE Scan on p p_2 (cost=0.00..0.02 rows=1 width=40) (actual time=0.002..0.006 rows=1 loops=1)

28. 14,145.168 14,145.168 ↓ 0.0 0 1

Index Scan using tmp_rostov_pl_deal_reports_report18_idx6 on pl_deal_reports reports_1 (cost=0.45..2,104,677.41 rows=140,454 width=833) (actual time=14,145.168..14,145.168 rows=0 loops=1)

  • Index Cond: ((report_create_date_yyyy_mm >= ((p_2.year_)::text || '-01'::text)) AND (report_create_date_yyyy_mm <= ((p_2.year_)::text || '-12'::text)))
  • Filter: (p_2.district_ = seller_federal_district_name)
  • Rows Removed by Filter: 2509237
29. 0.000 0.000 ↓ 0.0 0

Hash (cost=30.10..30.10 rows=910 width=14) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Seq Scan on party_special_categories_bars seller_special_categories_bars_1 (cost=0.00..30.10 rows=910 width=14) (never executed)

31.          

CTE reports

32. 0.016 14,145.243 ↓ 0.0 0 1

HashAggregate (cost=205.52..215.80 rows=1,028 width=201) (actual time=14,145.243..14,145.243 rows=0 loops=1)

  • Group Key: true, reports_2.seller_federal_district_name, reports_2.report_details_wood_breed_name, reports_2.report_details_sort_name, reports_2.seller_party_name, reports_2.seller_party_inn, reports_2.seller_bo_party_golden_record_fkey
33. 14,145.227 14,145.227 ↓ 0.0 0 1

CTE Scan on reports_filterd reports_2 (cost=0.00..102.76 rows=5,138 width=201) (actual time=14,145.227..14,145.227 rows=0 loops=1)

34. 0.016 2,046.279 ↓ 0.0 0 1

Sort (cost=25.29..26.29 rows=400 width=216) (actual time=2,046.279..2,046.279 rows=0 loops=1)

  • Sort Key: details.entity_name, details.seller_golden_record, details.breed_name, details.class_name
  • Sort Method: quicksort Memory: 25kB
35. 2,046.263 2,046.263 ↓ 0.0 0 1

CTE Scan on details (cost=0.00..8.00 rows=400 width=216) (actual time=2,046.263..2,046.263 rows=0 loops=1)

36. 0.033 14,145.279 ↓ 0.0 0 1

Sort (cost=71.99..74.56 rows=1,028 width=200) (actual time=14,145.279..14,145.279 rows=0 loops=1)

  • Sort Key: reports.entity_name, reports.seller_golden_record, reports.breed_name, reports.class_name
  • Sort Method: quicksort Memory: 25kB
37. 14,145.246 14,145.246 ↓ 0.0 0 1

CTE Scan on reports (cost=0.00..20.56 rows=1,028 width=200) (actual time=14,145.246..14,145.246 rows=0 loops=1)

Planning time : 3.853 ms
Execution time : 16,192.839 ms