explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rpQp

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 8,771.444 ↓ 25.0 25 1

Limit (cost=70,836.35..70,836.35 rows=1 width=588) (actual time=8,771.427..8,771.444 rows=25 loops=1)

2.          

CTE cte_table

3. 20.591 8,761.240 ↓ 4,529.0 4,529 1

GroupAggregate (cost=70,836.10..70,836.32 rows=1 width=611) (actual time=8,739.287..8,761.240 rows=4,529 loops=1)

  • Group Key: reports_products_dynamic_data_sources.variant_id, reports_products_dynamic_data_sources.variant_title, reports_products_dynamic_data_sources.sku
4. 5.198 8,740.649 ↓ 4,529.0 4,529 1

Sort (cost=70,836.10..70,836.11 rows=1 width=288) (actual time=8,739.259..8,740.649 rows=4,529 loops=1)

  • Sort Key: reports_products_dynamic_data_sources.variant_id, reports_products_dynamic_data_sources.variant_title, reports_products_dynamic_data_sources.sku
  • Sort Method: quicksort Memory: 1646kB
5. 19.986 8,735.451 ↓ 4,529.0 4,529 1

Nested Loop Left Join (cost=70,193.09..70,836.09 rows=1 width=288) (actual time=8,517.134..8,735.451 rows=4,529 loops=1)

  • Filter: ((sellables.deleted_at IS NULL) OR (sellables.deleted_at >= '2020-01-29 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 14299
6. 12.664 8,658.981 ↓ 18,828.0 18,828 1

Merge Left Join (cost=70,192.66..70,828.44 rows=1 width=282) (actual time=8,327.800..8,658.981 rows=18,828 loops=1)

  • Merge Cond: (reports_products_dynamic_data_sources.variant_id = reports_products_static_kits_data_sources.variant_id)
7. 13.506 7,540.094 ↓ 18,828.0 18,828 1

Merge Left Join (cost=55,501.10..56,136.57 rows=1 width=274) (actual time=7,222.243..7,540.094 rows=18,828 loops=1)

  • Merge Cond: (reports_products_dynamic_data_sources.variant_id = reports_products_static_data_sources.variant_id)
8. 29.268 347.663 ↓ 18,828.0 18,828 1

Nested Loop (cost=27,335.19..27,963.85 rows=1 width=138) (actual time=49.589..347.663 rows=18,828 loops=1)

9. 20.858 92.459 ↓ 257.9 18,828 1

Finalize GroupAggregate (cost=27,334.76..27,345.18 rows=73 width=36) (actual time=49.568..92.459 rows=18,828 loops=1)

  • Group Key: stock_entries.sellable_id
10. 16.049 71.601 ↓ 313.8 18,828 1

Gather Merge (cost=27,334.76..27,342.73 rows=60 width=48) (actual time=49.555..71.601 rows=18,828 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 6.877 55.552 ↓ 209.2 6,276 3 / 3

Partial GroupAggregate (cost=26,334.73..26,335.78 rows=30 width=48) (actual time=46.486..55.552 rows=6,276 loops=3)

  • Group Key: stock_entries.sellable_id
12. 5.521 48.675 ↓ 209.2 6,276 3 / 3

Sort (cost=26,334.73..26,334.81 rows=30 width=17) (actual time=46.472..48.675 rows=6,276 loops=3)

  • Sort Key: stock_entries.sellable_id
  • Sort Method: quicksort Memory: 497kB
  • Worker 0: Sort Method: quicksort Memory: 487kB
  • Worker 1: Sort Method: quicksort Memory: 492kB
13. 4.195 43.154 ↓ 209.2 6,276 3 / 3

Hash Join (cost=505.34..26,334.00 rows=30 width=17) (actual time=3.073..43.154 rows=6,276 loops=3)

  • Hash Cond: (sellables_1.created_by_id = users.id)
14. 7.615 38.778 ↓ 4.1 6,276 3 / 3

Nested Loop (cost=157.72..25,982.33 rows=1,542 width=21) (actual time=2.862..38.778 rows=6,276 loops=3)

15. 11.186 12.322 ↓ 4.1 6,280 3 / 3

Parallel Bitmap Heap Scan on stock_entries (cost=157.28..13,124.98 rows=1,544 width=17) (actual time=2.841..12.322 rows=6,280 loops=3)

  • Recheck Cond: (warehouse_id = 23912)
  • Heap Blocks: exact=3562
16. 1.136 1.136 ↓ 5.5 20,202 1 / 3

Bitmap Index Scan on index_stock_entries_on_warehouse_id (cost=0.00..156.35 rows=3,706 width=0) (actual time=3.409..3.409 rows=20,202 loops=1)

  • Index Cond: (warehouse_id = 23912)
17. 18.841 18.841 ↑ 1.0 1 18,841 / 3

Index Scan using product_variants_pkey on sellables sellables_1 (cost=0.43..8.33 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=18,841)

  • Index Cond: (id = stock_entries.sellable_id)
18. 0.049 0.181 ↑ 1.3 130 3 / 3

Hash (cost=345.50..345.50 rows=170 width=4) (actual time=0.180..0.181 rows=130 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
19. 0.113 0.132 ↑ 1.3 130 3 / 3

Bitmap Heap Scan on users (cost=9.60..345.50 rows=170 width=4) (actual time=0.027..0.132 rows=130 loops=3)

  • Recheck Cond: (company_id = 22292)
  • Heap Blocks: exact=55
20. 0.019 0.019 ↑ 1.3 130 3 / 3

Bitmap Index Scan on users_company_id_idx (cost=0.00..9.56 rows=170 width=0) (actual time=0.019..0.019 rows=130 loops=3)

  • Index Cond: (company_id = 22292)
21. 225.936 225.936 ↑ 1.0 1 18,828

Index Scan using index_reports_products_dynamic_data_sources_on_variant_id on reports_products_dynamic_data_sources (cost=0.43..8.46 rows=1 width=106) (actual time=0.009..0.012 rows=1 loops=18,828)

  • Index Cond: (variant_id = stock_entries.sellable_id)
  • Filter: (company_id = 22292)
22. 4.911 7,178.925 ↓ 5.9 797 1

GroupAggregate (cost=28,165.91..28,171.01 rows=136 width=140) (actual time=7,172.648..7,178.925 rows=797 loops=1)

  • Group Key: reports_products_static_data_sources.variant_id
23. 3.625 7,174.014 ↓ 31.8 4,325 1

Sort (cost=28,165.91..28,166.25 rows=136 width=39) (actual time=7,172.632..7,174.014 rows=4,325 loops=1)

  • Sort Key: reports_products_static_data_sources.variant_id
  • Sort Method: quicksort Memory: 530kB
24. 5.987 7,170.389 ↓ 31.8 4,325 1

Bitmap Heap Scan on reports_products_static_data_sources (cost=27,620.00..28,161.09 rows=136 width=39) (actual time=7,164.452..7,170.389 rows=4,325 loops=1)

  • Recheck Cond: ((company_id = 22292) AND (created_at >= '2020-01-29'::date) AND (created_at <= '2020-02-04'::date) AND (warehouse_id = 23912))
  • Heap Blocks: exact=413
25. 0.569 7,164.402 ↓ 0.0 0 1

BitmapAnd (cost=27,620.00..27,620.00 rows=136 width=0) (actual time=7,164.401..7,164.402 rows=0 loops=1)

26. 0.897 0.897 ↓ 1.2 14,787 1

Bitmap Index Scan on index_reports_products_static_data_sources_on_company_id_and_cr (cost=0.00..464.31 rows=12,140 width=0) (actual time=0.897..0.897 rows=14,787 loops=1)

  • Index Cond: ((company_id = 22292) AND (created_at >= '2020-01-29'::date) AND (created_at <= '2020-02-04'::date))
27. 7,162.936 7,162.936 ↓ 1.0 349,538 1

Bitmap Index Scan on index_reports_products_static_data_sources_on_warehouse_id (cost=0.00..27,155.37 rows=340,108 width=0) (actual time=7,162.936..7,162.936 rows=349,538 loops=1)

  • Index Cond: (warehouse_id = 23912)
28. 0.416 1,106.223 ↓ 15.6 156 1

GroupAggregate (cost=14,691.56..14,691.73 rows=10 width=12) (actual time=1,105.550..1,106.223 rows=156 loops=1)

  • Group Key: reports_products_static_kits_data_sources.variant_id
29. 0.781 1,105.807 ↓ 86.4 864 1

Sort (cost=14,691.56..14,691.58 rows=10 width=8) (actual time=1,105.529..1,105.807 rows=864 loops=1)

  • Sort Key: reports_products_static_kits_data_sources.variant_id
  • Sort Method: quicksort Memory: 65kB
30. 11.678 1,105.026 ↓ 86.4 864 1

Bitmap Heap Scan on reports_products_static_kits_data_sources (cost=13,797.78..14,691.39 rows=10 width=8) (actual time=1,093.748..1,105.026 rows=864 loops=1)

  • Recheck Cond: ((created_at >= '2020-01-29'::date) AND (created_at <= '2020-02-04'::date) AND (warehouse_id = 23912))
  • Filter: (company_id = 22292)
  • Heap Blocks: exact=229
31. 0.375 1,093.348 ↓ 0.0 0 1

BitmapAnd (cost=13,797.78..13,797.78 rows=232 width=0) (actual time=1,093.347..1,093.348 rows=0 loops=1)

32. 38.369 38.369 ↓ 4.5 41,563 1

Bitmap Index Scan on index_reports_products_static_kits_data_sources_on_created_at (cost=0.00..289.01 rows=9,258 width=0) (actual time=38.369..38.369 rows=41,563 loops=1)

  • Index Cond: ((created_at >= '2020-01-29'::date) AND (created_at <= '2020-02-04'::date))
33. 1,054.604 1,054.604 ↑ 1.5 74,697 1

Bitmap Index Scan on index_reports_products_static_kits_data_sources_on_warehouse_id (cost=0.00..13,508.52 rows=112,528 width=0) (actual time=1,054.603..1,054.604 rows=74,697 loops=1)

  • Index Cond: (warehouse_id = 23912)
34. 56.484 56.484 ↑ 1.0 1 18,828

Index Scan using product_variants_pkey on sellables (cost=0.43..7.64 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=18,828)

  • Index Cond: (reports_products_dynamic_data_sources.variant_id = id)
35. 2.893 8,771.431 ↓ 25.0 25 1

Sort (cost=0.03..0.04 rows=1 width=588) (actual time=8,771.425..8,771.431 rows=25 loops=1)

  • Sort Key: cte_table.name
  • Sort Method: top-N heapsort Memory: 34kB
36. 8,768.538 8,768.538 ↓ 4,529.0 4,529 1

CTE Scan on cte_table (cost=0.00..0.02 rows=1 width=588) (actual time=8,739.292..8,768.538 rows=4,529 loops=1)

Planning time : 3.432 ms
Execution time : 8,772.785 ms