explain.depesz.com

PostgreSQL's explain analyze made readable

Result: z54M

Settings
# exclusive inclusive rows x rows loops node
1. 1,415.868 1,415.868 ↓ 787.0 2,361 1

CTE Scan on products_rsri (cost=2,480.53..2,480.59 rows=3 width=311) (actual time=170.154..1,415.868 rows=2,361 loops=1)

  • Output: products_rsri.id, products_rsri.site_id, products_rsri.search_items_brands_relation_id, products_rsri.ranking, products_rsri.date_of_upload, products_rsri.best_seller_ranking, products_rsri.search_term_in_title, products_rsri.is_out_of_stock, products_rsri.is_in_store_only, products_rsri.currency, products_rsri.results_per_page, products_rsri.price, products_rsri.url_id, products_rsri.ranking_by_price, products_rsri.prime, products_rsri.seller_category_id, products_rsri.pickup_only, products_rsri.shipping, products_rsri.department, products_rsri.ranking_product_category_id, products_rsri.deliver_in, products_rsri.price_details_in_cart, products_rsri.no_longer_available, products_rsri.price_subscribe_save, products_rsri.zip_code_id, products_rsri.is_on_first_page, products_rsri.additional, products_rsri.search_term_id, products_rsri.brand_id, products_rsri.is_sponsored, products_rsri.is_low_stock, products_rsri._old_search_term_id, products_rsri._old_search_term_id2, products_rsri.crawled_at, products_rsri.imported_at
  • Buffers: shared hit=45766 read=1936
2.          

CTE products

3. 2.940 171.244 ↓ 5,327.0 5,327 1

Unique (cost=2,476.05..2,476.06 rows=1 width=217) (actual time=167.296..171.244 rows=5,327 loops=1)

  • Output: p.id, p.site_id, p.id, pu.id, pu.url, s.name
  • Buffers: shared hit=24030
4. 122.431 168.304 ↓ 5,327.0 5,327 1

Sort (cost=2,476.05..2,476.06 rows=1 width=217) (actual time=167.293..168.304 rows=5,327 loops=1)

  • Output: p.id, p.site_id, p.id, pu.id, pu.url, s.name
  • Sort Key: p.id, pu.url
  • Sort Method: quicksort Memory: 3175kB
  • Buffers: shared hit=24030
5. 1.366 45.873 ↓ 5,327.0 5,327 1

Nested Loop (cost=1.97..2,476.04 rows=1 width=217) (actual time=0.090..45.873 rows=5,327 loops=1)

  • Output: p.id, p.site_id, p.id, pu.id, pu.url, s.name
  • Join Filter: (p.id = pu.product_id)
  • Buffers: shared hit=24030
6. 0.147 6.907 ↓ 235.0 235 1

Nested Loop (cost=1.54..2,473.33 rows=1 width=26) (actual time=0.072..6.907 rows=235 loops=1)

  • Output: p.id, p.site_id, s.name, tcv_power_sku.product_id
  • Inner Unique: true
  • Buffers: shared hit=3795
7. 0.288 6.290 ↓ 235.0 235 1

Nested Loop (cost=1.27..2,472.90 rows=1 width=12) (actual time=0.065..6.290 rows=235 loops=1)

  • Output: p.id, p.site_id, tcv_power_sku.product_id
  • Inner Unique: true
  • Buffers: shared hit=3090
8. 1.506 5.297 ↓ 58.8 235 1

Nested Loop (cost=0.85..2,471.09 rows=4 width=16) (actual time=0.051..5.297 rows=235 loops=1)

  • Output: p.id, p.site_id, tcv_power_sku.product_id, tcv_power_sku.taxonomy_value_id
  • Inner Unique: true
  • Join Filter: (tcv_power_sku.taxonomy_column_id = tc_power_sku.id)
  • Rows Removed by Join Filter: 2667
  • Buffers: shared hit=2150
9. 0.881 3.791 ↓ 50.0 2,902 1

Nested Loop (cost=0.85..2,469.07 rows=58 width=20) (actual time=0.035..3.791 rows=2,902 loops=1)

  • Output: p.id, p.site_id, tcv_power_sku.product_id, tcv_power_sku.taxonomy_column_id, tcv_power_sku.taxonomy_value_id
  • Buffers: shared hit=2149
10. 1.030 1.030 ↑ 1.0 235 1

Index Scan using product_pkey on public.product p (cost=0.43..381.53 rows=235 width=8) (actual time=0.023..1.030 rows=235 loops=1)

  • Output: p.id, p.site_id, p.reseller_id, p.secondary_id, p.upc, p.vsn, p.map, p.msrp, p.name, p.cxh_product_guid
  • Index Cond: (p.id = ANY ('{24369,24370,24381,24390,24430,24437,24469,24475,24478,24480,24486,26078,27467,29222,29984,30364,30850,30854,31240,31370,31577,31585,31591,31599,33100,33158,33280,33300,33313,36831,36837,36845,36855,36900,36907,36910,39076,39973,43003,43008,43931,44211,44212,44237,44238,44240,44241,44244,44245,44246,44247,44248,44249,44251,44253,44256,44259,44264,44266,44267,44268,44270,44271,44273,44274,44275,44282,44564,51545,60218,60219,60220,60223,60224,67697,67914,67957,67990,68085,68188,68209,68212,68316,68386,68510,68603,68630,68770,70089,70414,70578,75784,76717,78005,82595,82633,82635,82871,83033,83060,83113,83121,83126,83292,83508,83587,83667,83697,83735,83830,83977,84247,84264,84532,84549,84572,101775,105500,117624,118046,118393,118471,118992,120074,120083,120367,120414,120627,121459,121588,122504,122506,122508,122525,122537,122539,122563,123484,126037,130332,130938,136673,137650,137685,141566,141602,151425,183781,184467,187019,187379,187409,187410,187411,195540,199316,200630,200643,200654,200665,203072,203122,203155,203197,203209,203210,204107,204108,204415,204442,204557,204715,206307,206749,206750,206778,210401,210402,210412,210420,210422,210424,211643,222378,222445,222446,222452,222490,224076,224129,231897,239917,241653,250412,254393,256311,257259,262412,266033,269264,269890,269964,270250,278331,285830,291855,300118,301051,301146,313259,325616,326034,326097,332874,335796,335881,336251,336272,336430,336562,337399,337432,337566,337593,337815,338185,338188,338189,379244,384923,391095,391169,403707,409404,414162}'::integer[]))
  • Buffers: shared hit=908
11. 1.880 1.880 ↓ 1.3 12 235

Index Scan using idx_taxonomy_column_values_product_id on public.taxonomy_column_values tcv_power_sku (cost=0.42..8.79 rows=9 width=12) (actual time=0.004..0.008 rows=12 loops=235)

  • Output: tcv_power_sku.id, tcv_power_sku.product_id, tcv_power_sku.taxonomy_column_id, tcv_power_sku.taxonomy_value_id, tcv_power_sku.originating_from_taxonomy_column_id
  • Index Cond: (tcv_power_sku.product_id = p.id)
  • Buffers: shared hit=1241
12. 0.000 0.000 ↑ 1.0 1 2,902

Materialize (cost=0.00..1.15 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=2,902)

  • Output: tc_power_sku.id
  • Buffers: shared hit=1
13. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on public.taxonomy_columns tc_power_sku (cost=0.00..1.15 rows=1 width=4) (actual time=0.008..0.010 rows=1 loops=1)

  • Output: tc_power_sku.id
  • Filter: tc_power_sku.is_for_power_sku
  • Rows Removed by Filter: 14
  • Buffers: shared hit=1
14. 0.705 0.705 ↑ 1.0 1 235

Index Scan using taxonomy_values_pkey on public.taxonomy_values tv_power_sku (cost=0.42..0.45 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=235)

  • Output: tv_power_sku.id, tv_power_sku.name
  • Index Cond: (tv_power_sku.id = tcv_power_sku.taxonomy_value_id)
  • Filter: (lower(tv_power_sku.name) = 'yes'::text)
  • Buffers: shared hit=940
15. 0.470 0.470 ↑ 1.0 1 235

Index Scan using sites_id_pkey on public.sites s (cost=0.27..0.44 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=235)

  • Output: s.name, s.id
  • Index Cond: (s.id = p.site_id)
  • Buffers: shared hit=705
16. 37.600 37.600 ↓ 2.6 23 235

Index Scan using product_url_product_id on public.product_url pu (cost=0.43..2.60 rows=9 width=195) (actual time=0.018..0.160 rows=23 loops=235)

  • Output: pu.id, pu.url, pu.product_id, pu.image_url, pu.title, pu.description, pu.model, pu.upc, pu.sku, pu.updated_at, pu.customer_uploaded_upc
  • Index Cond: (pu.product_id = tcv_power_sku.product_id)
  • Filter: (pu.updated_at >= '2020-02-10 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 172
  • Buffers: shared hit=20235
17.          

CTE products_rsri

18. 5.766 1,409.935 ↓ 787.0 2,361 1

Nested Loop (cost=0.00..4.47 rows=3 width=546) (actual time=170.149..1,409.935 rows=2,361 loops=1)

  • Output: rsri.id, rsri.site_id, rsri.search_items_brands_relation_id, rsri.ranking, rsri.date_of_upload, rsri.best_seller_ranking, rsri.search_term_in_title, rsri.is_out_of_stock, rsri.is_in_store_only, rsri.currency, rsri.results_per_page, rsri.price, rsri.url_id, rsri.ranking_by_price, rsri.prime, rsri.seller_category_id, rsri.pickup_only, rsri.shipping, rsri.department, rsri.ranking_product_category_id, rsri.deliver_in, rsri.price_details_in_cart, rsri.no_longer_available, rsri.price_subscribe_save, rsri.zip_code_id, rsri.is_on_first_page, rsri.additional, rsri.search_term_id, rsri.brand_id, rsri.is_sponsored, rsri.is_low_stock, rsri._old_search_term_id, rsri._old_search_term_id2, rsri.crawled_at, rsri.imported_at
  • Buffers: shared hit=45766 read=1936
19. 173.632 173.632 ↓ 5,327.0 5,327 1

CTE Scan on products p_1 (cost=0.00..0.02 rows=1 width=4) (actual time=167.297..173.632 rows=5,327 loops=1)

  • Output: p_1.id, p_1.site_id, p_1.product_id, p_1.url_id, p_1.url, p_1.site_name
  • Buffers: shared hit=24030
20. 5.327 1,230.537 ↓ 0.0 0 5,327

Append (cost=0.00..4.40 rows=5 width=546) (actual time=0.065..0.231 rows=0 loops=5,327)

  • Buffers: shared hit=21736 read=1936
21. 0.000 0.000 ↓ 0.0 0 5,327

Seq Scan on public.ranking_search_results_items rsri (cost=0.00..0.00 rows=1 width=311) (actual time=0.000..0.000 rows=0 loops=5,327)

  • Output: rsri.id, rsri.site_id, rsri.search_items_brands_relation_id, rsri.ranking, rsri.date_of_upload, rsri.best_seller_ranking, rsri.search_term_in_title, rsri.is_out_of_stock, rsri.is_in_store_only, rsri.currency, rsri.results_per_page, rsri.price, rsri.url_id, rsri.ranking_by_price, rsri.prime, rsri.seller_category_id, rsri.pickup_only, rsri.shipping, rsri.department, rsri.ranking_product_category_id, rsri.deliver_in, rsri.price_details_in_cart, rsri.no_longer_available, rsri.price_subscribe_save, rsri.zip_code_id, rsri.is_on_first_page, rsri.additional, rsri.search_term_id, rsri.brand_id, rsri.is_sponsored, rsri.is_low_stock, rsri._old_search_term_id, rsri._old_search_term_id2, rsri.crawled_at, rsri.imported_at
  • Filter: ((rsri.date_of_upload = '2020-01-10'::date) AND (p_1.url_id = rsri.url_id))
22. 1,225.210 1,225.210 ↓ 0.0 0 5,327

Index Scan using ranking_search_results_items_2020_1_date_of_upload_url_id_idx on public.ranking_search_results_items_2020_1 rsri_1 (cost=0.56..4.40 rows=4 width=546) (actual time=0.064..0.230 rows=0 loops=5,327)

  • Output: rsri_1.id, rsri_1.site_id, rsri_1.search_items_brands_relation_id, rsri_1.ranking, rsri_1.date_of_upload, rsri_1.best_seller_ranking, rsri_1.search_term_in_title, rsri_1.is_out_of_stock, rsri_1.is_in_store_only, rsri_1.currency, rsri_1.results_per_page, rsri_1.price, rsri_1.url_id, rsri_1.ranking_by_price, rsri_1.prime, rsri_1.seller_category_id, rsri_1.pickup_only, rsri_1.shipping, rsri_1.department, rsri_1.ranking_product_category_id, rsri_1.deliver_in, rsri_1.price_details_in_cart, rsri_1.no_longer_available, rsri_1.price_subscribe_save, rsri_1.zip_code_id, rsri_1.is_on_first_page, rsri_1.additional, rsri_1.search_term_id, rsri_1.brand_id, rsri_1.is_sponsored, rsri_1.is_low_stock, rsri_1._old_search_term_id, rsri_1._old_search_term_id2, rsri_1.crawled_at, rsri_1.imported_at
  • Index Cond: ((rsri_1.date_of_upload = '2020-01-10'::date) AND (rsri_1.url_id = p_1.url_id))
  • Buffers: shared hit=21736 read=1936
Planning time : 4.610 ms
Execution time : 1,416.696 ms