My Custom report is Slow since 4.05.9 Beta

[Dutchraise72]

I have a custom report to get the same view as i had in pt3. When i try to run this report it takes 1108075.386 ms for results. Thats way to long. In previous version it did run smoothly.

Here is the querie:

Code: Select all

SELECT id_player, id_site, str_player_name, tourney_date_start, tourney_no, id_gametype, id_table_type, sum(amt_buyin_curr_conv), sum(amt_fee_curr_conv), amt_bounty_per, tourney_currency, sum(amt_bounty_per_curr_conv), cnt_bounties, cnt_rebuys, amt_rebuy_per, amt_rebuy_per_curr_conv, amt_rebuy, amt_rebuy_curr_conv, cnt_addons, amt_addon, sum(amt_addon_curr_conv), sum(amt_addon_rebuy_curr_conv), sum(amt_buyin_ttl_curr_conv), sum(cnt_tourney_players), val_finish, id_player_results, tourney_date_end, id_tourney_type, flg_sat_seat, amt_addon_per, sum(amt_addon_per_curr_conv), sum(amt_won_curr_conv), date_end_summary, date_start_summary FROM ( SELECT (tourney_hand_player_statistics.id_player) as "id_player", (player_real.id_site) as "id_site", (player.player_name) as "str_player_name", (timezone('UTC',  tourney_summary.date_start  + INTERVAL '0 HOURS')) as "tourney_date_start", (tourney_summary.tourney_no) as "tourney_no", (tourney_hand_player_statistics.id_gametype) as "id_gametype", (tourney_summary.id_table_type) as "id_table_type", (((case when(tourney_summary.val_curr_conv!=0) then  tourney_summary.val_curr_conv * tourney_summary.amt_buyin else  0.0 end))) as "amt_buyin_curr_conv", (( (case when(tourney_summary.val_curr_conv!=0) then  tourney_summary.val_curr_conv * tourney_summary.amt_fee else  0.0 end) )) as "amt_fee_curr_conv", (tourney_summary.amt_bounty) as "amt_bounty_per", (tourney_summary.currency) as "tourney_currency", (((case when(tourney_summary.val_curr_conv!=0) then  tourney_summary.val_curr_conv * tourney_summary.amt_bounty else  0.0 end))) as "amt_bounty_per_curr_conv", (tourney_results.cnt_bounty) as "cnt_bounties", (tourney_results.cnt_rebuy) as "cnt_rebuys", (tourney_summary.amt_rebuy) as "amt_rebuy_per", ((case when(tourney_summary.val_curr_conv != 0) then  tourney_summary.val_curr_conv * tourney_summary.amt_rebuy else  0.0 end)) as "amt_rebuy_per_curr_conv", (tourney_summary.amt_rebuy * tourney_results.cnt_rebuy) as "amt_rebuy", ((case when(tourney_summary.val_curr_conv!=0) then  tourney_summary.val_curr_conv * (tourney_summary.amt_rebuy * tourney_results.cnt_rebuy) else  0.0 end)) as "amt_rebuy_curr_conv", (tourney_results.cnt_addon) as "cnt_addons", (tourney_summary.amt_addon * tourney_results.cnt_addon) as "amt_addon", (((case when(tourney_summary.val_curr_conv!=0) then  tourney_summary.val_curr_conv * (tourney_summary.amt_addon * tourney_results.cnt_addon) else  0.0 end))) as "amt_addon_curr_conv", (((case when(tourney_summary.val_curr_conv != 0) then  tourney_summary.val_curr_conv * (tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy) else  0.0 end))) as "amt_addon_rebuy_curr_conv", (((case when(tourney_summary.val_curr_conv != 0) then  tourney_summary.val_curr_conv * (tourney_summary.amt_buyin + tourney_summary.amt_fee + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy + tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_bounty) else  0.0 end))) as "amt_buyin_ttl_curr_conv", ((tourney_summary.cnt_players)) as "cnt_tourney_players", (tourney_results.val_finish) as "val_finish", (tourney_results.id_player) as "id_player_results", (timezone('UTC',  tourney_summary.date_end  + INTERVAL '0 HOURS')) as "tourney_date_end", (tourney_summary.id_tourney_type) as "id_tourney_type", (tourney_results.flg_sat_seat) as "flg_sat_seat", (tourney_summary.amt_addon) as "amt_addon_per", (((case when(tourney_summary.val_curr_conv!=0) then  tourney_summary.val_curr_conv * tourney_summary.amt_addon else  0.0 end))) as "amt_addon_per_curr_conv", (((case when(tourney_summary.val_curr_conv != 0) then  tourney_summary.val_curr_conv * (tourney_results.amt_won + tourney_results.cnt_bounty * tourney_summary.amt_bounty) else  0.0 end))) as "amt_won_curr_conv", (timezone('UTC',  tourney_summary.date_end  + INTERVAL '-9 HOURS')) as "date_end_summary", (timezone('UTC',  tourney_summary.date_start  + INTERVAL '-9 HOURS')) as "date_start_summary" FROM              tourney_hand_player_statistics, player, tourney_summary, tourney_results , player player_real WHERE  (player.id_player = tourney_hand_player_statistics.id_player)  AND (tourney_summary.id_tourney = tourney_hand_player_statistics.id_tourney)  AND (tourney_results.id_tourney = tourney_hand_player_statistics.id_tourney  AND tourney_results.id_player = tourney_hand_player_statistics.id_player)  AND (player_real.id_player = tourney_hand_player_statistics.id_player_real)  AND (player.id_player = tourney_results.id_player)  AND (tourney_results.id_tourney = tourney_summary.id_tourney)  AND (player_real.id_player = tourney_results.id_player_real)   AND (tourney_results.id_player = (SELECT id_player FROM player WHERE player_name_search='dutchraise72'  AND id_site='200'))        AND (((tourney_results.id_gametype in(1))))  GROUP BY tourney_summary.id_tourney, tourney_results.amt_won, tourney_results.flg_sat_seat, tourney_results.id_player, tourney_results.val_finish, tourney_results.cnt_addon, tourney_results.cnt_rebuy, tourney_results.cnt_bounty, tourney_summary.id_tourney_type, tourney_summary.date_end, tourney_summary.cnt_players, tourney_summary.amt_addon, tourney_summary.amt_rebuy, tourney_summary.amt_bounty, tourney_summary.amt_fee, tourney_summary.currency, tourney_summary.amt_buyin, tourney_summary.val_curr_conv, tourney_summary.id_table_type, tourney_summary.tourney_no, tourney_summary.date_start, (tourney_hand_player_statistics.id_player), (player_real.id_site), (player.player_name), (timezone('UTC',  tourney_summary.date_start  + INTERVAL '0 HOURS')), (tourney_summary.tourney_no), (tourney_hand_player_statistics.id_gametype), (tourney_summary.id_table_type), (tourney_summary.amt_bounty), (tourney_summary.currency), (tourney_results.cnt_bounty), (tourney_results.cnt_rebuy), (tourney_summary.amt_rebuy), ((case when(tourney_summary.val_curr_conv != 0) then  tourney_summary.val_curr_conv * tourney_summary.amt_rebuy else  0.0 end)), (tourney_summary.amt_rebuy * tourney_results.cnt_rebuy), ((case when(tourney_summary.val_curr_conv!=0) then  tourney_summary.val_curr_conv * (tourney_summary.amt_rebuy * tourney_results.cnt_rebuy) else  0.0 end)), (tourney_results.cnt_addon), (tourney_summary.amt_addon * tourney_results.cnt_addon), (tourney_results.val_finish), (tourney_results.id_player), (timezone('UTC',  tourney_summary.date_end  + INTERVAL '0 HOURS')), (tourney_summary.id_tourney_type), (tourney_results.flg_sat_seat), (tourney_summary.amt_addon), (timezone('UTC',  tourney_summary.date_end  + INTERVAL '-9 HOURS')), (timezone('UTC',  tourney_summary.date_start  + INTERVAL '-9 HOURS')) ) AS tnySumGroup GROUP BY id_player, id_site, str_player_name, tourney_date_start, tourney_no, id_gametype, id_table_type, amt_bounty_per, tourney_currency, cnt_bounties, cnt_rebuys, amt_rebuy_per, amt_rebuy_per_curr_conv, amt_rebuy, amt_rebuy_curr_conv, cnt_addons, amt_addon, val_finish, id_player_results, tourney_date_end, id_tourney_type, flg_sat_seat, amt_addon_per, date_end_summary, date_start_summary

And here is the result of the explain analyze.

Code: Select all

"HashAggregate  (cost=78.51..78.54 rows=1 width=613) (actual time=1108053.826..1108067.439 rows=4757 loops=1)"
"  ->  HashAggregate  (cost=78.28..78.42 rows=1 width=114) (actual time=1107981.581..1108026.341 rows=4757 loops=1)"
"        InitPlan 1 (returns $0)"
"          ->  Index Scan using "idx1:player_name_search" on player  (cost=0.00..6.28 rows=1 width=4) (actual time=0.030..0.035 rows=1 loops=1)"
"                Index Cond: (player_name_search = 'dutchraise72'::text)"
"                Filter: (id_site = 200::smallint)"
"        ->  Nested Loop  (cost=19.61..71.91 rows=1 width=114) (actual time=169.469..1104964.144 rows=357035 loops=1)"
"              ->  Nested Loop  (cost=19.61..65.58 rows=1 width=120) (actual time=169.331..1097820.575 rows=357035 loops=1)"
"                    ->  Nested Loop  (cost=19.61..59.30 rows=1 width=51) (actual time=169.307..1090745.345 rows=357035 loops=1)"
"                          ->  Nested Loop  (cost=19.61..53.02 rows=1 width=42) (actual time=169.277..1084922.194 rows=357035 loops=1)"
"                                Join Filter: (tourney_hand_player_statistics.id_player_real = tourney_results.id_player_real)"
"                                ->  Index Scan using "tor:idx2-id_player" on tourney_results  (cost=0.00..7.75 rows=2 width=28) (actual time=0.066..2733.642 rows=4759 loops=1)"
"                                      Index Cond: (id_player = $0)"
"                                      Filter: (id_gametype = 1)"
"                                ->  Bitmap Heap Scan on tourney_hand_player_statistics  (cost=19.61..22.62 rows=1 width=14) (actual time=202.852..225.710 rows=75 loops=4759)"
"                                      Recheck Cond: ((tourney_hand_player_statistics.id_player = $0) AND (tourney_hand_player_statistics.id_tourney = tourney_results.id_tourney))"
"                                      ->  BitmapAnd  (cost=19.61..19.61 rows=1 width=0) (actual time=197.103..197.103 rows=0 loops=4759)"
"                                            ->  Bitmap Index Scan on "thps:idx2-id_player"  (cost=0.00..4.27 rows=102 width=0) (actual time=129.650..129.650 rows=357165 loops=4759)"
"                                                  Index Cond: (tourney_hand_player_statistics.id_player = $0)"
"                                            ->  Bitmap Index Scan on "thps:idx4-id_tourney"  (cost=0.00..15.07 rows=742 width=0) (actual time=3.900..3.900 rows=640 loops=4759)"
"                                                  Index Cond: (tourney_hand_player_statistics.id_tourney = tourney_results.id_tourney)"
"                          ->  Index Scan using player_primary_key on player  (cost=0.00..6.28 rows=1 width=13) (actual time=0.006..0.007 rows=1 loops=357035)"
"                                Index Cond: (public.player.id_player = $0)"
"                    ->  Index Scan using tourney_summary_primary_key on tourney_summary  (cost=0.00..6.27 rows=1 width=77) (actual time=0.009..0.010 rows=1 loops=357035)"
"                          Index Cond: (tourney_summary.id_tourney = tourney_hand_player_statistics.id_tourney)"
"              ->  Index Scan using player_primary_key on player player_real  (cost=0.00..6.28 rows=1 width=6) (actual time=0.004..0.006 rows=1 loops=357035)"
"                    Index Cond: (player_real.id_player = tourney_hand_player_statistics.id_player_real)"
"Total runtime: 1108075.386 ms"

There are a few line with more then 4700 loops, That can't be good. What is wrong and why does it not work anymore?

I did re index and vacuum the database.

[Klinschor]

Same here, since 4.05.9 its a little bit slow. 4.05.07 was faster.

[kraada]

I'll make sure this gets looked into.

[Dutchraise72]

It's getting Stranger.

I re-imported my PT3 database in to PT4 for the second time in a new database. The first time i did that was in early july and this database still exists. In the first database from July this query runs smoothly but in the new created database it does not. Just to be sure that this new created database was not corrupted i imported my PT3 database for the third time. With both new databases same problem. These are in all three 4700+ tourneys.

Here is the explain analyze from the database that runs this report smoothly

Code: Select all

"HashAggregate  (cost=58.12..58.15 rows=1 width=613) (actual time=21351.622..21365.021 rows=4756 loops=1)"
"  ->  HashAggregate  (cost=57.88..58.02 rows=1 width=114) (actual time=21277.151..21323.421 rows=4756 loops=1)"
"        InitPlan 1 (returns $0)"
"          ->  Index Scan using "idx1:player_name_search" on player  (cost=0.00..6.28 rows=1 width=4) (actual time=0.019..0.023 rows=1 loops=1)"
"                Index Cond: (player_name_search = 'dutchraise72'::text)"
"                Filter: (id_site = 200::smallint)"
"        ->  Nested Loop  (cost=6.49..51.52 rows=1 width=114) (actual time=16.907..18627.149 rows=356902 loops=1)"
"              ->  Nested Loop  (cost=6.49..45.19 rows=1 width=120) (actual time=16.880..12111.475 rows=356902 loops=1)"
"                    ->  Nested Loop  (cost=6.49..38.91 rows=1 width=51) (actual time=16.866..7103.804 rows=356902 loops=1)"
"                          ->  Hash Join  (cost=6.49..32.62 rows=1 width=42) (actual time=16.850..2054.181 rows=356902 loops=1)"
"                                Hash Cond: ((tourney_hand_player_statistics.id_tourney = tourney_results.id_tourney) AND (tourney_hand_player_statistics.id_player_real = tourney_results.id_player_real))"
"                                ->  Index Scan using "thps:idx2-id_player" on tourney_hand_player_statistics  (cost=0.00..24.44 rows=168 width=14) (actual time=0.048..671.555 rows=357032 loops=1)"
"                                      Index Cond: (id_player = $0)"
"                                ->  Hash  (cost=6.46..6.46 rows=2 width=28) (actual time=16.784..16.784 rows=4758 loops=1)"
"                                      Buckets: 1024  Batches: 1  Memory Usage: 262kB"
"                                      ->  Index Scan using "tor:idx2-id_player" on tourney_results  (cost=0.00..6.46 rows=2 width=28) (actual time=0.024..8.718 rows=4758 loops=1)"
"                                            Index Cond: (id_player = $0)"
"                                            Filter: (id_gametype = 1)"
"                          ->  Index Scan using player_primary_key on player  (cost=0.00..6.28 rows=1 width=13) (actual time=0.004..0.006 rows=1 loops=356902)"
"                                Index Cond: (public.player.id_player = $0)"
"                    ->  Index Scan using tourney_summary_primary_key on tourney_summary  (cost=0.00..6.27 rows=1 width=77) (actual time=0.004..0.005 rows=1 loops=356902)"
"                          Index Cond: (tourney_summary.id_tourney = tourney_hand_player_statistics.id_tourney)"
"              ->  Index Scan using player_primary_key on player player_real  (cost=0.00..6.28 rows=1 width=6) (actual time=0.004..0.005 rows=1 loops=356902)"
"                    Index Cond: (player_real.id_player = tourney_hand_player_statistics.id_player_real)"
"Total runtime: 21371.706 ms"

[kraada]

I've been told by the developer who looks into these issues that he'd prefer you add this information into a support ticket. Could you please do that and post your ticket number here? I'll make sure the ticket goes straight to him.

[Dutchraise72]

ticket_id=131724

[kraada]

Thanks, I've made him aware of your ticket.