explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iiJf

Settings
# exclusive inclusive rows x rows loops node
1. 779.402 589,156.927 ↑ 1.0 100 1

Limit (cost=1,118,979.83..1,118,980.08 rows=100 width=182) (actual time=589,156.760..589,156.927 rows=100 loops=1)

  • Functions: 71
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
2. 1.163 588,377.525 ↑ 303.2 100 1

Sort (cost=1,118,979.83..1,119,055.64 rows=30,323 width=182) (actual time=588,377.514..588,377.525 rows=100 loops=1)

  • Sort Key: (percentile_disc('0.9'::double precision) WITHIN GROUP (ORDER BY t1.hotness)) DESC
  • Sort Method: top-N heapsort Memory: 65kB
3. 1.180 588,376.362 ↑ 14.4 2,104 1

Hash Join (cost=1,077,139.85..1,117,820.91 rows=30,323 width=182) (actual time=588,106.399..588,376.362 rows=2,104 loops=1)

  • Hash Cond: (product_detail_new.brand_id = pbd.brand_id)
4. 336.441 588,370.189 ↑ 14.3 2,105 1

GroupAggregate (cost=1,076,166.73..1,115,754.93 rows=30,081 width=105) (actual time=588,100.573..588,370.189 rows=2,105 loops=1)

  • Group Key: t1.model_version, t1.country_code, product_detail_new.brand_id
  • Filter: ((count(t1.item_id) > 10) AND (percentile_disc('0.9'::double precision) WITHIN GROUP (ORDER BY t1.hotness) > '0'::double precision))
  • Rows Removed by Filter: 2246
5. 1,166.881 588,033.748 ↑ 1.5 641,192 1

Sort (cost=1,076,166.73..1,078,581.43 rows=965,878 width=175) (actual time=587,759.270..588,033.748 rows=641,192 loops=1)

  • Sort Key: t1.model_version, t1.country_code, product_detail_new.brand_id
  • Sort Method: external merge Disk: 53424kB
6. 2,137.338 586,866.867 ↑ 1.5 641,192 1

Hash Right Join (cost=630,038.59..815,077.31 rows=965,878 width=175) (actual time=583,117.523..586,866.867 rows=641,192 loops=1)

  • Hash Cond: ((product_detail_new.item_id)::text = (t1.item_id)::text)
7. 1,623.772 1,623.772 ↑ 1.0 935,434 1

Seq Scan on product_detail_new (cost=0.00..148,818.29 rows=965,878 width=126) (actual time=11.527..1,623.772 rows=935,434 loops=1)

  • Filter: ((title IS NOT NULL) AND (platform_id = 1) AND ((country_code)::text = 'us'::text))
  • Rows Removed by Filter: 695652
8. 1,040.398 583,105.757 ↓ 3,206.0 641,192 1

Hash (cost=630,036.09..630,036.09 rows=200 width=167) (actual time=583,105.756..583,105.757 rows=641,192 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 32 (originally 1) Memory Usage: 3585kB
9. 576.331 582,065.359 ↓ 3,206.0 641,192 1

Nested Loop Left Join (cost=627,073.32..630,036.09 rows=200 width=167) (actual time=3,019.665..582,065.359 rows=641,192 loops=1)

10. 678.754 70,459.004 ↓ 3,206.0 641,192 1

Nested Loop (cost=627,072.88..628,816.11 rows=200 width=151) (actual time=3,019.650..70,459.004 rows=641,192 loops=1)

11. 851.146 5,661.050 ↓ 3,206.0 641,192 1

Finalize GroupAggregate (cost=627,072.44..627,123.11 rows=200 width=126) (actual time=3,019.608..5,661.050 rows=641,192 loops=1)

  • Group Key: data_hotness.item_id
12. 949.940 4,809.904 ↓ 2,570.9 1,028,369 1

Gather Merge (cost=627,072.44..627,119.11 rows=400 width=126) (actual time=3,019.576..4,809.904 rows=1,028,369 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 1,910.631 3,859.964 ↓ 1,714.0 342,790 3 / 3

Sort (cost=626,072.42..626,072.92 rows=200 width=126) (actual time=2,979.610..3,859.964 rows=342,790 loops=3)

  • Sort Key: data_hotness.item_id
  • Sort Method: external merge Disk: 11464kB
  • Worker 0: Sort Method: external merge Disk: 11440kB
  • Worker 1: Sort Method: external merge Disk: 11336kB
14. 277.615 1,949.333 ↓ 1,714.0 342,790 3 / 3

Partial HashAggregate (cost=626,062.77..626,064.77 rows=200 width=126) (actual time=1,872.668..1,949.333 rows=342,790 loops=3)

  • Group Key: data_hotness.item_id
15. 1,671.718 1,671.718 ↑ 1.2 457,930 3 / 3

Parallel Seq Scan on data_hotness (cost=0.00..623,294.17 rows=553,721 width=126) (actual time=922.492..1,671.718 rows=457,930 loops=3)

  • Filter: ((hotness IS NOT NULL) AND (date <= '2020-02-10'::date) AND (hotness > '0'::double precision) AND ((country_code)::text = 'us'::text) AND (platform_id = 1) AND ((model_version)::text = 'embeddings_nn_3.0'::text))
  • Rows Removed by Filter: 8216636
16. 64,119.200 64,119.200 ↑ 1.0 1 641,192

Index Scan using idx_5614864_primary on data_hotness t1 (cost=0.44..8.46 rows=1 width=159) (actual time=0.100..0.100 rows=1 loops=641,192)

  • Index Cond: (id = (max(data_hotness.id)))
17. 511,030.024 511,030.024 ↑ 1.0 1 641,192

Index Scan using idx_5615067_primary on product_dynamic dyn (cost=0.44..6.10 rows=1 width=32) (actual time=0.797..0.797 rows=1 loops=641,192)

  • Index Cond: (id = t1.product_dynamic_id)
18. 1.897 4.993 ↑ 1.0 9,744 1

Hash (cost=851.19..851.19 rows=9,754 width=85) (actual time=4.993..4.993 rows=9,744 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 959kB
19. 2.402 3.096 ↑ 1.0 9,744 1

Bitmap Heap Scan on product_brand_detail pbd (cost=227.88..851.19 rows=9,754 width=85) (actual time=0.764..3.096 rows=9,744 loops=1)

  • Recheck Cond: ((language)::text = 'N000920200'::text)
  • Filter: ((brand_name IS NOT NULL) AND (brand_id > 0))
  • Heap Blocks: exact=477
20. 0.694 0.694 ↑ 1.0 9,745 1

Bitmap Index Scan on idx_5615036_language_ndx (cost=0.00..225.44 rows=9,754 width=0) (actual time=0.694..0.694 rows=9,745 loops=1)

  • Index Cond: ((language)::text = 'N000920200'::text)
Planning time : 2.752 ms