explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gnBC

Settings
# exclusive inclusive rows x rows loops node
1. 0.101 5,418.301 ↑ 1.0 1,000 1

Limit (cost=97,962.43..97,964.93 rows=1,000 width=41) (actual time=5,418.184..5,418.301 rows=1,000 loops=1)

2. 14.371 5,418.200 ↑ 10.5 2,000 1

Sort (cost=97,959.93..98,012.56 rows=21,054 width=41) (actual time=5,418.098..5,418.200 rows=2,000 loops=1)

  • Sort Key: (sum(COALESCE(((prod_stats_daily_20200420.stats ->> 'allSiteView'::text))::numeric, '0'::numeric))) DESC
  • Sort Method: top-N heapsort Memory: 250kB
3. 960.031 5,403.829 ↓ 1.4 28,794 1

HashAggregate (cost=96,437.12..96,700.29 rows=21,054 width=41) (actual time=5,388.072..5,403.829 rows=28,794 loops=1)

  • Group Key: prod_stats_daily_20200420.product_id
4. 129.846 4,443.798 ↓ 14.1 296,418 1

Hash Join (cost=52,362.30..96,173.94 rows=21,054 width=263) (actual time=4,139.333..4,443.798 rows=296,418 loops=1)

  • Hash Cond: (prod_stats_daily_20200420.product_id = prod_stats_daily_20200420_1.product_id)
5. 20.302 174.647 ↑ 1.0 296,418 1

Append (cost=0.00..42,799.25 rows=296,446 width=263) (actual time=0.013..174.647 rows=296,418 loops=1)

6. 33.908 33.908 ↑ 1.0 63,562 1

Seq Scan on prod_stats_daily_20200420 (cost=0.00..8,588.49 rows=63,678 width=240) (actual time=0.013..33.908 rows=63,562 loops=1)

  • Filter: ((day >= '2020-04-21'::date) AND (day <= '2020-05-21'::date))
  • Rows Removed by Filter: 10471
7. 35.626 35.626 ↑ 1.0 69,348 1

Seq Scan on prod_stats_daily_20200427 (cost=0.00..10,059.22 rows=69,348 width=276) (actual time=0.018..35.626 rows=69,348 loops=1)

  • Filter: ((day >= '2020-04-21'::date) AND (day <= '2020-05-21'::date))
8. 33.402 33.402 ↓ 1.0 65,376 1

Seq Scan on prod_stats_daily_20200504 (cost=0.00..9,047.32 rows=65,288 width=277) (actual time=0.018..33.402 rows=65,376 loops=1)

  • Filter: ((day >= '2020-04-21'::date) AND (day <= '2020-05-21'::date))
9. 33.826 33.826 ↑ 1.0 64,907 1

Seq Scan on prod_stats_daily_20200511 (cost=0.00..9,027.60 rows=64,907 width=262) (actual time=0.014..33.826 rows=64,907 loops=1)

  • Filter: ((day >= '2020-04-21'::date) AND (day <= '2020-05-21'::date))
10. 17.583 17.583 ↑ 1.0 33,225 1

Seq Scan on prod_stats_daily_20200518 (cost=0.00..4,594.38 rows=33,225 width=250) (actual time=0.015..17.583 rows=33,225 loops=1)

  • Filter: ((day >= '2020-04-21'::date) AND (day <= '2020-05-21'::date))
11. 4.676 4,139.305 ↓ 19.4 28,794 1

Hash (cost=52,343.77..52,343.77 rows=1,482 width=9) (actual time=4,139.304..4,139.305 rows=28,794 loops=1)

  • Buckets: 32768 (originally 2048) Batches: 1 (originally 1) Memory Usage: 1395kB
12. 156.216 4,134.629 ↓ 19.4 28,794 1

HashAggregate (cost=52,328.95..52,343.77 rows=1,482 width=9) (actual time=4,128.491..4,134.629 rows=28,794 loops=1)

  • Group Key: prod_stats_daily_20200420_1.product_id
13. 234.212 3,978.413 ↓ 2.0 296,418 1

Nested Loop (cost=0.01..51,958.45 rows=148,200 width=9) (actual time=0.038..3,978.413 rows=296,418 loops=1)

14. 22.388 2,558.529 ↓ 200.0 296,418 1

Append (cost=0.00..48,994.45 rows=1,482 width=529) (actual time=0.023..2,558.529 rows=296,418 loops=1)

15. 533.713 533.713 ↓ 199.9 63,562 1

Seq Scan on prod_stats_daily_20200420 prod_stats_daily_20200420_1 (cost=0.00..10,439.32 rows=318 width=529) (actual time=0.022..533.713 rows=63,562 loops=1)

  • Filter: ((day >= '2020-04-21'::date) AND (day <= '2020-05-21'::date) AND ((COALESCE((record ->> 'price'::text), '0'::text))::numeric(24,2) >= '0'::numeric) AND ((COALESCE((record ->> 'price'::text), '0'::text))::numeric(24,2) <= '1000000000000'::numeric))
  • Rows Removed by Filter: 10471
16. 588.752 588.752 ↓ 199.9 69,348 1

Seq Scan on prod_stats_daily_20200427 prod_stats_daily_20200427_1 (cost=0.00..11,792.92 rows=347 width=529) (actual time=0.026..588.752 rows=69,348 loops=1)

  • Filter: ((day >= '2020-04-21'::date) AND (day <= '2020-05-21'::date) AND ((COALESCE((record ->> 'price'::text), '0'::text))::numeric(24,2) >= '0'::numeric) AND ((COALESCE((record ->> 'price'::text), '0'::text))::numeric(24,2) <= '1000000000000'::numeric))
17. 561.716 561.716 ↓ 200.5 65,376 1

Seq Scan on prod_stats_daily_20200504 prod_stats_daily_20200504_1 (cost=0.00..10,679.52 rows=326 width=530) (actual time=0.046..561.716 rows=65,376 loops=1)

  • Filter: ((day >= '2020-04-21'::date) AND (day <= '2020-05-21'::date) AND ((COALESCE((record ->> 'price'::text), '0'::text))::numeric(24,2) >= '0'::numeric) AND ((COALESCE((record ->> 'price'::text), '0'::text))::numeric(24,2) <= '1000000000000'::numeric))
18. 573.057 573.057 ↓ 199.7 64,907 1

Seq Scan on prod_stats_daily_20200511 prod_stats_daily_20200511_1 (cost=0.00..10,650.28 rows=325 width=529) (actual time=0.027..573.057 rows=64,907 loops=1)

  • Filter: ((day >= '2020-04-21'::date) AND (day <= '2020-05-21'::date) AND ((COALESCE((record ->> 'price'::text), '0'::text))::numeric(24,2) >= '0'::numeric) AND ((COALESCE((record ->> 'price'::text), '0'::text))::numeric(24,2) <= '1000000000000'::numeric))
19. 278.903 278.903 ↓ 200.2 33,225 1

Seq Scan on prod_stats_daily_20200518 prod_stats_daily_20200518_1 (cost=0.00..5,425.00 rows=166 width=529) (actual time=0.027..278.903 rows=33,225 loops=1)

  • Filter: ((day >= '2020-04-21'::date) AND (day <= '2020-05-21'::date) AND ((COALESCE((record ->> 'price'::text), '0'::text))::numeric(24,2) >= '0'::numeric) AND ((COALESCE((record ->> 'price'::text), '0'::text))::numeric(24,2) <= '1000000000000'::numeric))
20. 1,185.672 1,185.672 ↑ 100.0 1 296,418

Function Scan on json_array_elements p (cost=0.01..1.00 rows=100 width=0) (actual time=0.004..0.004 rows=1 loops=296,418)

Planning time : 2.460 ms
Execution time : 5,418.733 ms