explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cfU

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.044 114.032 ↓ 1.2 247 1

Unique (cost=93,827.43..93,939.70 rows=200 width=1,108) (actual time=113.978..114.032 rows=247 loops=1)

2. 0.298 113.988 ↑ 60.6 247 1

Sort (cost=93,827.43..93,864.85 rows=14,969 width=1,108) (actual time=113.977..113.988 rows=247 loops=1)

  • Sort Key: main.pid, main.sid, main.kid DESC, main.si_id DESC
  • Sort Method: quicksort Memory: 280kB
3. 0.949 113.690 ↑ 60.6 247 1

Hash Right Join (cost=84,063.88..85,523.35 rows=14,969 width=1,108) (actual time=113.469..113.690 rows=247 loops=1)

  • Hash Cond: (lowest_offer_listing_history.product_id = main.pid)
4. 7.223 17.466 ↑ 1.5 9,736 1

GroupAggregate (cost=1,388.19..2,136.64 rows=14,969 width=88) (actual time=9.483..17.466 rows=9,736 loops=1)

  • Group Key: (date_trunc('minute'::text, lowest_offer_listing_history.cdate)), lowest_offer_listing_history.product_id
5. 5.989 10.243 ↓ 1.0 14,983 1

Sort (cost=1,388.19..1,425.61 rows=14,969 width=38) (actual time=9.467..10.243 rows=14,983 loops=1)

  • Sort Key: (date_trunc('minute'::text, lowest_offer_listing_history.cdate)) DESC, lowest_offer_listing_history.product_id
  • Sort Method: quicksort Memory: 1,555kB
6. 4.254 4.254 ↓ 1.0 14,983 1

Seq Scan on lowest_offer_listing_history (cost=0.00..350.11 rows=14,969 width=38) (actual time=0.016..4.254 rows=14,983 loops=1)

7. 0.262 95.275 ↓ 1.2 247 1

Hash (cost=82,673.19..82,673.19 rows=200 width=1,020) (actual time=95.275..95.275 rows=247 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 228kB
8. 0.220 95.013 ↓ 1.2 247 1

Subquery Scan on main (cost=82,669.08..82,673.19 rows=200 width=1,020) (actual time=94.593..95.013 rows=247 loops=1)

9. 0.135 94.793 ↓ 1.2 247 1

Unique (cost=82,669.08..82,671.19 rows=200 width=1,024) (actual time=94.587..94.793 rows=247 loops=1)

10. 3.690 94.658 ↓ 4.4 1,231 1

Sort (cost=82,669.08..82,669.78 rows=281 width=1,024) (actual time=94.587..94.658 rows=1,231 loops=1)

  • Sort Key: a.product_id, a.supplier_id, j.id DESC
  • Sort Method: quicksort Memory: 1,329kB
11. 0.671 90.968 ↓ 4.4 1,231 1

Nested Loop Left Join (cost=80,207.91..82,657.65 rows=281 width=1,024) (actual time=89.346..90.968 rows=1,231 loops=1)

12. 0.139 89.556 ↑ 1.1 247 1

Unique (cost=80,207.48..80,298.28 rows=281 width=970) (actual time=89.323..89.556 rows=247 loops=1)

13. 2.445 89.417 ↑ 14.7 1,234 1

Sort (cost=80,207.48..80,252.88 rows=18,160 width=970) (actual time=89.323..89.417 rows=1,234 loops=1)

  • Sort Key: a.product_id, f.id DESC
  • Sort Method: quicksort Memory: 1,302kB
14. 1.561 86.972 ↑ 14.7 1,234 1

Nested Loop Left Join (cost=2,103.64..71,163.30 rows=18,160 width=970) (actual time=23.222..86.972 rows=1,234 loops=1)

15. 0.400 73.071 ↑ 3.1 1,234 1

Nested Loop Left Join (cost=2,095.02..65,771.51 rows=3,801 width=816) (actual time=22.596..73.071 rows=1,234 loops=1)

16. 36.270 71.683 ↓ 1.2 247 1

Nested Loop (cost=2,094.59..53,650.07 rows=202 width=762) (actual time=22.580..71.683 rows=247 loops=1)

  • Join Filter: CASE WHEN ((h.primary_id_type)::text = 'upc'::text) THEN ((h.upc)::text = (b.upc)::text) WHEN ((h.primary_id_type)::text = 'ean'::text) THEN ((h.ean)::text = (b.ean)::text) WHEN ((h.primary_id_type)::text = 'asin'::text) THEN (((h.asin)::text = (b.asin)::text) AND (b.upc IS NULL) AND (b.ean IS NULL)) ELSE NULL::boolean END
  • Rows Removed by Join Filter: 215,631
17. 0.027 24.545 ↑ 1.6 247 1

Merge Left Join (cost=2,032.18..53,572.53 rows=404 width=623) (actual time=21.620..24.545 rows=247 loops=1)

  • Merge Cond: (a.product_id = c.product_id)
  • Join Filter: (c.supplier_id = a.supplier_id)
18. 6.461 24.442 ↑ 1.6 247 1

Gather Merge (cost=2,024.18..53,563.50 rows=404 width=529) (actual time=21.539..24.442 rows=247 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
19. 0.065 17.981 ↑ 1.9 124 2 / 2

Nested Loop Left Join (cost=1,024.17..52,518.04 rows=238 width=529) (actual time=14.889..17.981 rows=124 loops=2)

20. 0.071 17.422 ↑ 1.7 124 2 / 2

Nested Loop Left Join (cost=1,023.74..41,783.39 rows=209 width=476) (actual time=14.875..17.422 rows=124 loops=2)

21. 0.105 16.857 ↑ 1.5 124 2 / 2

Nested Loop Left Join (cost=1,023.32..32,127.34 rows=188 width=423) (actual time=14.863..16.857 rows=124 loops=2)

22. 0.081 16.258 ↑ 1.4 124 2 / 2

Nested Loop Left Join (cost=1,022.89..23,138.99 rows=175 width=370) (actual time=14.851..16.258 rows=124 loops=2)

23. 0.087 15.683 ↑ 1.3 124 2 / 2

Nested Loop Left Join (cost=1,022.46..14,664.26 rows=165 width=317) (actual time=14.835..15.683 rows=124 loops=2)

24. 2.635 14.979 ↑ 1.3 124 2 / 2

Merge Join (cost=1,022.03..6,189.54 rows=165 width=264) (actual time=14.809..14.979 rows=124 loops=2)

  • Merge Cond: (b.id = a.product_id)
25. 12.048 12.048 ↑ 2.7 27,792 2 / 2

Parallel Index Scan using product_pkey on product b (cost=0.29..4,985.61 rows=73,702 width=191) (actual time=0.016..12.048 rows=27,792 loops=2)

26. 0.177 0.296 ↑ 1.1 247 2 / 2

Sort (cost=1,021.74..1,022.44 rows=281 width=73) (actual time=0.274..0.296 rows=247 loops=2)

  • Sort Key: a.product_id
  • Sort Method: quicksort Memory: 59kB
  • Worker 0: Sort Method: quicksort Memory: 59kB
27. 0.091 0.119 ↑ 1.1 247 2 / 2

Bitmap Heap Scan on product_kpi a (cost=10.60..1,010.31 rows=281 width=73) (actual time=0.036..0.119 rows=247 loops=2)

  • Recheck Cond: (supplier_id = 2,000,000,302)
  • Heap Blocks: exact=4
28. 0.028 0.028 ↑ 1.1 247 2 / 2

Bitmap Index Scan on product_kpi_supplier_id_d6e0fc70 (cost=0.00..10.53 rows=281 width=0) (actual time=0.028..0.028 rows=247 loops=2)

  • Index Cond: (supplier_id = 2,000,000,302)
29. 0.618 0.618 ↑ 1.0 1 247 / 2

Index Scan using product_attribute_product_id_eaa55dd4 on product_attribute p (cost=0.43..51.35 rows=1 width=53) (actual time=0.003..0.005 rows=1 loops=247)

  • Index Cond: (a.product_id = product_id)
  • Filter: (attribute_id = '4000000023'::bigint)
  • Rows Removed by Filter: 11
30. 0.494 0.494 ↑ 1.0 1 247 / 2

Index Scan using product_attribute_product_id_eaa55dd4 on product_attribute t (cost=0.43..51.35 rows=1 width=53) (actual time=0.002..0.004 rows=1 loops=247)

  • Index Cond: (a.product_id = product_id)
  • Filter: (attribute_id = '4000000022'::bigint)
  • Rows Removed by Filter: 11
31. 0.494 0.494 ↑ 1.0 1 247 / 2

Index Scan using product_attribute_product_id_eaa55dd4 on product_attribute i (cost=0.43..51.35 rows=1 width=53) (actual time=0.004..0.004 rows=1 loops=247)

  • Index Cond: (a.product_id = product_id)
  • Filter: (attribute_id = '4000000012'::bigint)
  • Rows Removed by Filter: 11
32. 0.494 0.494 ↑ 1.0 1 247 / 2

Index Scan using product_attribute_product_id_eaa55dd4 on product_attribute e (cost=0.43..51.35 rows=1 width=53) (actual time=0.003..0.004 rows=1 loops=247)

  • Index Cond: (a.product_id = product_id)
  • Filter: (attribute_id = '4000000017'::bigint)
  • Rows Removed by Filter: 11
33. 0.494 0.494 ↑ 1.0 1 247 / 2

Index Scan using product_attribute_product_id_eaa55dd4 on product_attribute d (cost=0.43..51.35 rows=1 width=53) (actual time=0.004..0.004 rows=1 loops=247)

  • Index Cond: (a.product_id = product_id)
  • Filter: (attribute_id = '4000000011'::bigint)
  • Rows Removed by Filter: 11
34. 0.006 0.076 ↓ 0.0 0 1

Sort (cost=8.00..8.01 rows=1 width=94) (actual time=0.076..0.076 rows=0 loops=1)

  • Sort Key: c.product_id
  • Sort Method: quicksort Memory: 25kB
35. 0.070 0.070 ↓ 0.0 0 1

Seq Scan on product_track c (cost=0.00..7.99 rows=1 width=94) (actual time=0.070..0.070 rows=0 loops=1)

  • Filter: ((seller_id = 1,000,000,010) AND (supplier_id = 2,000,000,302))
  • Rows Removed by Filter: 279
36. 10.397 10.868 ↓ 874.0 874 247

Materialize (cost=62.41..66.43 rows=1 width=139) (actual time=0.001..0.044 rows=874 loops=247)

37. 0.355 0.471 ↓ 874.0 874 1

Bitmap Heap Scan on synthesis_input h (cost=62.41..66.43 rows=1 width=139) (actual time=0.127..0.471 rows=874 loops=1)

  • Recheck Cond: ((synthesis_file_id = 308) AND (supplier_id = 2,000,000,302))
  • Heap Blocks: exact=18
38. 0.005 0.116 ↓ 0.0 0 1

BitmapAnd (cost=62.41..62.41 rows=1 width=0) (actual time=0.116..0.116 rows=0 loops=1)

39. 0.056 0.056 ↑ 1.0 874 1

Bitmap Index Scan on synthesis_input_synthesis_file_id_32204777 (cost=0.00..31.08 rows=887 width=0) (actual time=0.056..0.056 rows=874 loops=1)

  • Index Cond: (synthesis_file_id = 308)
40. 0.055 0.055 ↑ 1.0 874 1

Bitmap Index Scan on synthesis_input_supplier_id_78f55558 (cost=0.00..31.08 rows=887 width=0) (actual time=0.055..0.055 rows=874 loops=1)

  • Index Cond: (supplier_id = 2,000,000,302)
41. 0.988 0.988 ↑ 4.6 5 247

Index Scan using fees_estimate_history_product_id_3232bc26 on fees_estimate_history f (cost=0.43..59.78 rows=23 width=54) (actual time=0.002..0.004 rows=5 loops=247)

  • Index Cond: (a.product_id = product_id)
42. 7.412 12.340 ↑ 9.0 1 1,234

Hash Right Join (cost=8.62..9.95 rows=9 width=126) (actual time=0.004..0.010 rows=1 loops=1,234)

  • Hash Cond: (k.amazon_category_id = l.amazon_category_id)
43. 2.460 2.460 ↓ 1.3 29 1,230

Seq Scan on product_category_mapping k (cost=0.00..1.23 rows=23 width=74) (actual time=0.001..0.002 rows=29 loops=1,230)

44. 1.234 2.468 ↑ 9.0 1 1,234

Hash (cost=8.51..8.51 rows=9 width=52) (actual time=0.002..0.002 rows=1 loops=1,234)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
45. 1.234 1.234 ↑ 9.0 1 1,234

Index Scan using product_category_product_id_aa5da095 on product_category l (cost=0.42..8.51 rows=9 width=52) (actual time=0.001..0.001 rows=1 loops=1,234)

  • Index Cond: (a.product_id = product_id)
46. 0.741 0.741 ↓ 5.0 5 247

Index Scan using sales_ranking_history_product_cat_id_idx on sales_ranking_history j (cost=0.43..8.38 rows=1 width=54) (actual time=0.002..0.003 rows=5 loops=247)

  • Index Cond: ((a.product_id = product_id) AND ((product_category_id)::text = (k.amazon_category_tag_id)::text))
Planning time : 21.303 ms
Execution time : 114.620 ms