explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gSc

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 4,942.182 ↑ 1.0 10 1

Limit (cost=456,510.16..469,804.21 rows=10 width=557) (actual time=4,513.222..4,942.182 rows=10 loops=1)

2.          

CTE _rsri_distinct

3. 68.316 1,565.542 ↓ 2.3 112,551 1

Unique (cost=84,748.21..85,110.76 rows=48,341 width=525) (actual time=1,288.729..1,565.542 rows=112,551 loops=1)

4. 1,194.965 1,497.226 ↓ 2.3 112,643 1

Sort (cost=84,748.21..84,869.06 rows=48,341 width=525) (actual time=1,288.708..1,497.226 rows=112,643 loops=1)

  • Sort Key: rsri_1.date_of_upload, (COALESCE((pu_1.product_id)::text, ('unique_'::text || (pu_1.id)::text))), rsri_1.id DESC
  • Sort Method: external merge Disk: 41,032kB
5. 121.724 302.261 ↓ 2.3 112,643 1

Hash Join (cost=14,112.88..74,308.15 rows=48,341 width=525) (actual time=121.035..302.261 rows=112,643 loops=1)

  • Hash Cond: (rsri_1.url_id = pu_1.id)
6. 13.815 59.829 ↓ 1.3 114,113 1

Append (cost=0.00..58,785.69 rows=85,842 width=493) (actual time=0.021..59.829 rows=114,113 loops=1)

7. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on ranking_search_results_items rsri_1 (cost=0.00..0.00 rows=1 width=291) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((date_of_upload = ANY ('{2019-09-19,2019-09-20}'::date[])) AND (site_id = 77))
8. 46.012 46.012 ↓ 1.3 114,113 1

Index Scan using ranking_search_results_items_2019_3_site_id_date_of_upload_idx on ranking_search_results_items_2019_3 rsri_2 (cost=0.43..58,785.69 rows=85,841 width=493) (actual time=0.018..46.012 rows=114,113 loops=1)

  • Index Cond: ((site_id = 77) AND (date_of_upload = ANY ('{2019-09-19,2019-09-20}'::date[])))
9. 14.155 120.708 ↑ 1.1 56,409 1

Hash (cost=13,357.56..13,357.56 rows=60,425 width=8) (actual time=120.708..120.708 rows=56,409 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,716kB
10. 46.915 106.553 ↑ 1.1 56,409 1

Hash Join (cost=2,280.93..13,357.56 rows=60,425 width=8) (actual time=27.901..106.553 rows=56,409 loops=1)

  • Hash Cond: (pu_1.product_id = cdt.product_id)
11. 31.808 31.808 ↑ 1.1 100,694 1

Seq Scan on product_url pu_1 (cost=0.00..10,070.01 rows=107,301 width=8) (actual time=0.005..31.808 rows=100,694 loops=1)

12. 11.389 27.830 ↑ 1.0 56,350 1

Hash (cost=1,571.71..1,571.71 rows=56,737 width=4) (actual time=27.830..27.830 rows=56,350 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,494kB
13. 16.441 16.441 ↑ 1.0 56,350 1

Seq Scan on cars_dashboard_taxonomy cdt (cost=0.00..1,571.71 rows=56,737 width=4) (actual time=0.008..16.441 rows=56,350 loops=1)

  • Filter: (site_id = 77)
  • Rows Removed by Filter: 26,907
14. 0.035 4,942.167 ↑ 4,834.1 10 1

Result (cost=371,399.39..64,636,166.50 rows=48,341 width=557) (actual time=4,513.221..4,942.167 rows=10 loops=1)

15. 51.100 4,463.072 ↑ 4,834.1 10 1

Sort (cost=371,399.39..371,520.25 rows=48,341 width=525) (actual time=4,463.068..4,463.072 rows=10 loops=1)

  • Sort Key: pu.title
  • Sort Method: top-N heapsort Memory: 30kB
16. 22.960 4,411.972 ↓ 1.2 56,350 1

Hash Join (cost=369,495.05..370,354.76 rows=48,341 width=525) (actual time=4,208.136..4,411.972 rows=56,350 loops=1)

  • Hash Cond: (s_1.id = s.id)
17. 35.803 4,388.868 ↓ 1.2 56,350 1

Hash Join (cost=369,479.66..370,211.03 rows=48,341 width=525) (actual time=4,207.986..4,388.868 rows=56,350 loops=1)

  • Hash Cond: (rsri.brand_id = rb.id)
18. 515.809 4,352.039 ↓ 1.2 56,350 1

Sort (cost=369,405.61..369,526.46 rows=48,341 width=509) (actual time=4,206.947..4,352.039 rows=56,350 loops=1)

  • Sort Key: pu.title
  • Sort Method: external merge Disk: 21,872kB
19. 46.826 3,836.230 ↓ 1.2 56,350 1

Unique (cost=358,435.18..358,676.89 rows=48,341 width=509) (actual time=3,680.287..3,836.230 rows=56,350 loops=1)

20. 581.553 3,789.404 ↓ 2.3 112,551 1

Sort (cost=358,435.18..358,556.04 rows=48,341 width=509) (actual time=3,680.285..3,789.404 rows=112,551 loops=1)

  • Sort Key: (COALESCE((pu.product_id)::text, ('unique_'::text || (pu.id)::text))), rsri.id DESC
  • Sort Method: external merge Disk: 43,704kB
21. 864.543 3,207.851 ↓ 2.3 112,551 1

Hash Join (cost=10,196.62..348,189.87 rows=48,341 width=509) (actual time=1,899.087..3,207.851 rows=112,551 loops=1)

  • Hash Cond: (rsri.site_id = s_1.id)
22. 187.410 2,343.167 ↓ 2.3 112,551 1

Hash Left Join (cost=10,181.23..29,240.30 rows=48,341 width=460) (actual time=1,898.920..2,343.167 rows=112,551 loops=1)

  • Hash Cond: (pu.product_id = p.id)
23. 100.395 2,114.476 ↓ 2.3 112,551 1

Merge Join (cost=6,954.57..19,758.75 rows=48,341 width=451) (actual time=1,857.429..2,114.476 rows=112,551 loops=1)

  • Merge Cond: (pu.id = rsri.url_id)
24. 117.522 117.522 ↑ 1.1 96,764 1

Index Scan using product_url_pkey on product_url pu (cost=0.29..11,690.25 rows=107,301 width=298) (actual time=0.012..117.522 rows=96,764 loops=1)

25. 24.354 1,896.559 ↓ 2.3 112,551 1

Materialize (cost=6,954.28..7,195.99 rows=48,341 width=153) (actual time=1,822.232..1,896.559 rows=112,551 loops=1)

26. 195.021 1,872.205 ↓ 2.3 112,551 1

Sort (cost=6,954.28..7,075.13 rows=48,341 width=153) (actual time=1,822.230..1,872.205 rows=112,551 loops=1)

  • Sort Key: rsri.url_id
  • Sort Method: external merge Disk: 39,248kB
27. 1,677.184 1,677.184 ↓ 2.3 112,551 1

CTE Scan on _rsri_distinct rsri (cost=0.00..966.82 rows=48,341 width=153) (actual time=1,288.735..1,677.184 rows=112,551 loops=1)

28. 22.455 41.281 ↓ 1.0 94,618 1

Hash (cost=1,582.07..1,582.07 rows=94,607 width=13) (actual time=41.281..41.281 rows=94,618 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 3,248kB
29. 18.826 18.826 ↓ 1.0 94,618 1

Seq Scan on product p (cost=0.00..1,582.07 rows=94,607 width=13) (actual time=0.012..18.826 rows=94,618 loops=1)

30. 0.071 0.141 ↑ 1.0 373 1

Hash (cost=10.73..10.73 rows=373 width=18) (actual time=0.141..0.141 rows=373 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
31. 0.070 0.070 ↑ 1.0 373 1

Seq Scan on sites s_1 (cost=0.00..10.73 rows=373 width=18) (actual time=0.003..0.070 rows=373 loops=1)

32.          

SubPlan (for Hash Join)

33. 0.000 0.000 ↓ 0.0 0

Limit (cost=5.44..6.55 rows=1 width=13) (never executed)

34.          

Initplan (for Limit)

35. 0.000 0.000 ↓ 0.0 0

Index Scan using product_pkey on product p2 (cost=0.29..2.51 rows=1 width=9) (never executed)

  • Index Cond: (id = pu.product_id)
36. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on ch_master_catalog_products cmcp (cost=2.93..5.15 rows=2 width=13) (never executed)

  • Recheck Cond: ((product_url_id = pu.id) OR ((web_id)::text = ($5)::text))
  • Filter: (upc IS NOT NULL)
37. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=2.93..2.93 rows=2 width=0) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ch_master_catalog_products_product_url_id (cost=0.00..1.40 rows=1 width=0) (never executed)

  • Index Cond: (product_url_id = pu.id)
39. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ch_master_catalog_products_web_id_index (cost=0.00..1.53 rows=1 width=0) (never executed)

  • Index Cond: ((web_id)::text = ($5)::text)
40. 0.515 1.026 ↓ 1.0 2,586 1

Hash (cost=41.80..41.80 rows=2,580 width=16) (actual time=1.026..1.026 rows=2,586 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 158kB
41. 0.511 0.511 ↓ 1.0 2,586 1

Seq Scan on ranking_brands rb (cost=0.00..41.80 rows=2,580 width=16) (actual time=0.006..0.511 rows=2,586 loops=1)

42. 0.066 0.144 ↑ 1.0 373 1

Hash (cost=10.73..10.73 rows=373 width=4) (actual time=0.143..0.144 rows=373 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
43. 0.078 0.078 ↑ 1.0 373 1

Seq Scan on sites s (cost=0.00..10.73 rows=373 width=4) (actual time=0.007..0.078 rows=373 loops=1)

44.          

SubPlan (for Result)

45. 0.230 479.060 ↑ 1.0 1 10

Aggregate (cost=1,329.38..1,329.39 rows=1 width=32) (actual time=47.905..47.906 rows=1 loops=10)

46. 478.830 478.830 ↓ 2.0 2 10

CTE Scan on _rsri_distinct rsril (cost=0.00..1,329.38 rows=1 width=5) (actual time=10.645..47.883 rows=2 loops=10)

  • Filter: ((NOT (zip_code_id IS DISTINCT FROM rsri.zip_code_id)) AND (date_of_upload = ANY ('{2019-09-19,2019-09-20}'::date[])) AND (url_id = rsri.url_id))
  • Rows Removed by Filter: 112,549
Planning time : 2.331 ms
Execution time : 4,973.445 ms