explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 74K2

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 2,798.888 ↑ 1.0 10 1

Limit (cost=515,546.66..515,546.69 rows=10 width=1,140) (actual time=2,798.887..2,798.888 rows=10 loops=1)

2.          

CTE stats

3. 245.752 297.340 ↓ 1.7 259,225 1

Bitmap Heap Scan on app_playerdailystat (cost=12,472.56..248,514.13 rows=151,421 width=91) (actual time=52.646..297.340 rows=259,225 loops=1)

  • Recheck Cond: ((project_id = ANY ('{306,526,545,55,390,585,563,583,29,116,196,354,569,101,622,104,200,458,649,318,367,31,213,313,112,338,230,13,9,15,283,222,449,474,432,525,292,309,385,231,477,492,235,375,296,629,448,388,371,370,232,377,415,465,476,346,475,403,520,584,595,466,645,393,258,625,639,522,198,195,457,647,456,651,382,343,392,643,39,335,661,582,98,683,331,531,353,536,567,121,214,552,463,295,105,159,223,473,376,586,558,452,627,544,455,59,239,304,197,451,30,182,553,360,149,673,273,111,484,236,614,512,478,115,314,345,43,255,633,429,290,418,436,359,496,417,600,601,628,83,636,397,297,560,172,224,537,528,489,621,615,161,554,504,194,327,384,391,387,399,262,264,617,266,289,253,285,206,408,268,249,250,252,619,320,592,407,401,204,251,238,366,461,40,336,662,613,302,373,379,325,79,588,256,564,680,487,635,568,344,667,150,539,655,657,561,490,77,459,604,605,606,607,608,609,610,611,35,282,308,499,637,372,310,381,524,510,679,394,305,438,624,378,470,351,587,227,541,589,623,488,177,447,498,147,422,538,181,109,157,383,508,506,491,533,326,226,416,590,509,542,681,280,233,107,380,210,44,631,596,543,300,523,599,559,259,48,462,74,240,299,669,145,414,301,602,234,411,281,62,269,386,518,511,515,603,597,322,495,352,676,100,556,221,562,500,479,69,323,431,53,464,514,497,272,340,342,298,632,123,555,81,71,57,28,136,594,659,630,425,97,521,598,163,106,505,389,374,626,334,519,428,612,316,641,540,17,486,653,368,566}'::integer[])) AND (day >= '2019-02-03'::date) AND ((currency_type)::text = 'real'::text) AND ((currency_code)::text = 'EUR'::text))
  • Heap Blocks: exact=6176
4. 51.588 51.588 ↓ 1.7 259,227 1

Bitmap Index Scan on app_playerdailystat_project_id_day_currency__9b0effbe_uniq (cost=0.00..12,434.70 rows=151,421 width=0) (actual time=51.588..51.588 rows=259,227 loops=1)

  • Index Cond: ((project_id = ANY ('{306,526,545,55,390,585,563,583,29,116,196,354,569,101,622,104,200,458,649,318,367,31,213,313,112,338,230,13,9,15,283,222,449,474,432,525,292,309,385,231,477,492,235,375,296,629,448,388,371,370,232,377,415,465,476,346,475,403,520,584,595,466,645,393,258,625,639,522,198,195,457,647,456,651,382,343,392,643,39,335,661,582,98,683,331,531,353,536,567,121,214,552,463,295,105,159,223,473,376,586,558,452,627,544,455,59,239,304,197,451,30,182,553,360,149,673,273,111,484,236,614,512,478,115,314,345,43,255,633,429,290,418,436,359,496,417,600,601,628,83,636,397,297,560,172,224,537,528,489,621,615,161,554,504,194,327,384,391,387,399,262,264,617,266,289,253,285,206,408,268,249,250,252,619,320,592,407,401,204,251,238,366,461,40,336,662,613,302,373,379,325,79,588,256,564,680,487,635,568,344,667,150,539,655,657,561,490,77,459,604,605,606,607,608,609,610,611,35,282,308,499,637,372,310,381,524,510,679,394,305,438,624,378,470,351,587,227,541,589,623,488,177,447,498,147,422,538,181,109,157,383,508,506,491,533,326,226,416,590,509,542,681,280,233,107,380,210,44,631,596,543,300,523,599,559,259,48,462,74,240,299,669,145,414,301,602,234,411,281,62,269,386,518,511,515,603,597,322,495,352,676,100,556,221,562,500,479,69,323,431,53,464,514,497,272,340,342,298,632,123,555,81,71,57,28,136,594,659,630,425,97,521,598,163,106,505,389,374,626,334,519,428,612,316,641,540,17,486,653,368,566}'::integer[])) AND (day >= '2019-02-03'::date) AND ((currency_type)::text = 'real'::text) AND ((currency_code)::text = 'EUR'::text))
5. 14.487 2,798.886 ↑ 1,514.3 10 1

Sort (cost=267,032.53..267,070.39 rows=15,143 width=1,140) (actual time=2,798.886..2,798.886 rows=10 loops=1)

  • Sort Key: (COALESCE(((sum(stats.bets)) - (sum(stats.wins))), '0'::numeric))
  • Sort Method: top-N heapsort Memory: 26kB
6. 10.641 2,784.399 ↓ 2.6 39,357 1

Hash Join (cost=265,550.64..266,705.30 rows=15,143 width=1,140) (actual time=2,684.338..2,784.399 rows=39,357 loops=1)

  • Hash Cond: (stats.project_id = t2.id)
7. 74.994 2,772.465 ↓ 2.6 39,357 1

WindowAgg (cost=264,267.54..265,062.55 rows=15,143 width=1,132) (actual time=2,683.034..2,772.465 rows=39,357 loops=1)

8. 61.688 2,697.471 ↓ 2.6 39,357 1

Sort (cost=264,267.54..264,305.40 rows=15,143 width=1,132) (actual time=2,683.006..2,697.471 rows=39,357 loops=1)

  • Sort Key: (((('100'::numeric * sum(stats.wins)))::double precision / NULLIF((sum(stats.bets))::double precision, '0'::double precision))) DESC
  • Sort Method: external merge Disk: 2688kB
9. 389.137 2,635.783 ↓ 2.6 39,357 1

GroupAggregate (cost=241,197.84..248,201.14 rows=15,143 width=1,132) (actual time=1,881.642..2,635.783 rows=39,357 loops=1)

  • Group Key: stats.player_id, stats.game_name, stats.project_id
  • Filter: (((('100'::numeric * sum(stats.wins)))::double precision / NULLIF((sum(stats.bets))::double precision, '0'::double precision)) >= '100'::double precision)
  • Rows Removed by Filter: 157128
10. 1,729.616 2,246.646 ↓ 1.7 259,225 1

Sort (cost=241,197.84..241,576.39 rows=151,421 width=1,132) (actual time=1,881.566..2,246.646 rows=259,225 loops=1)

  • Sort Key: stats.player_id, stats.game_name, stats.project_id
  • Sort Method: external merge Disk: 14960kB
11. 517.030 517.030 ↓ 1.7 259,225 1

CTE Scan on stats (cost=0.00..3,028.42 rows=151,421 width=1,132) (actual time=52.651..517.030 rows=259,225 loops=1)

12. 0.114 1.293 ↑ 1.0 671 1

Hash (cost=1,274.71..1,274.71 rows=671 width=20) (actual time=1.293..1.293 rows=671 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
13. 1.179 1.179 ↑ 1.0 671 1

Seq Scan on app_project t2 (cost=0.00..1,274.71 rows=671 width=20) (actual time=0.004..1.179 rows=671 loops=1)