ristorothe
09-03-2012, 03:42 AM
When I run a report (without any filters) for example "By stakes" then summary data comes quickly. But those last 100 hands that appear in the bottom of holdem manager are coming with ~1-2 minute delay sometimes the hands are not even coming.
It all started after I purged all my Fulltilt hands (2M) and run under database maintaince->vacuum with options analyze and full were selected. I am playing on Stars right now.
When I turn on logging database command then I see from dbtrace.log file that:
09/02/12 14:02:48 HM COMMAND: select * from gametypes
09/02/12 14:02:48 HM COMMAND FINISHED: select * from gametypes
09/02/12 14:02:48 HM COMMAND: select gt.gametypedescription as "gametypedescription",(case when ....
09/02/12 14:02:48 HM COMMAND FINISHED: select gt.gametypedescription as "gametypedescription",.....
09/02/12 14:02:48 HM COMMAND: select PH.PokerHand_ID,PH.PreFlopPlayerActionType_ID,PH.S treetWentAllin...
As you see the HM COMMAND FINISHED for the last query that displays hands did not appear. Also in Windows task manager I see that postgres is consuming 25% of CPU and if I take that las query and try to run it through PGAdminIII then it wont return output (doesn't hang but query is running endless). Also this action adds another process of postgres.exe that consumes 25% of CPU.
Full query:
select PH.PokerHand_ID,PH.PreFlopPlayerActionType_ID,PH.S treetWentAllin,PH.HandTimeStamp,GT.GameTypeDescrip tion, GT.PokerGame,PH.holecard1int,PH.holecard2int,PH.ho lecard3int,PH.holecard4int,PKH.FlopCard1int,PKH.fl opcard2int,PKH.flopcard3int,PKH.TurnCardint,PKH.Ri verCardint,PH.flopplayerActionType_ID,PH.turnplaye rActionType_ID,PH.riverplayerActionType_ID,PH.NetA mountWon,GT.BigBlind,PH.PositionType_ID,PH.preflop action_id,PH.didpfr,PH.didvpip,(case when PH.StreetWentAllIn > 0 then (Select SklanskyBucks from AllInSituations where ph.playerhand_id = allinsituations.playerhand_id) else 0 end) as SklanskyBucks,(case when PH.StreetWentAllIn > 0 then (Select EquityPct from AllInSituations where ph.playerhand_id = allinsituations.playerhand_id) else 0 end) as EquityPct,getwinningplayerjoincashver2(ph.pokerhan d_id) as winningplayerjoin from playerhandscashkeycolumns_hero ph join players pl on (pl.player_id = ph.player_id) join pokerhands_hero pkh on pkh.pokerhand_id = ph.pokerhand_id join playerhandscashmisc_hero phmisc on phmisc.playerhand_id = ph.playerhand_id join gametypes gt on gt.gametype_id = ph.gametype_id left join playerhandsflop_hero flop on ph.playerhand_id = flop.playerhand_id left join playerhandsturn_hero turn on ph.playerhand_id = turn.playerhand_id left join playerhandsriver_hero river on ph.playerhand_id = river.playerhand_id left join allinsituations_hero ev on ph.playerhand_id = ev.playerhand_id where (ph.player_id = 158041) and (ph.gametype_id = 1 or ph.gametype_id = 2 or ph.gametype_id = 9 or ph.gametype_id = 10 or ph.gametype_id = 11 or ph.gametype_id = 12 or ph.gametype_id = 23 or ph.gametype_id = 24 or ph.gametype_id = 32 or ph.gametype_id = 38 or ph.gametype_id = 39 or ph.gametype_id = 40 or ph.gametype_id = 41 or ph.gametype_id = 42 or ph.gametype_id = 43 or ph.gametype_id = 44 or ph.gametype_id = 52 or ph.gametype_id = 53 or ph.gametype_id = 64 or ph.gametype_id = 65 or ph.gametype_id = 66 or ph.gametype_id = 67 or ph.gametype_id = 68 or ph.gametype_id = 69 or ph.gametype_id = 70 or ph.gametype_id = 75 or ph.gametype_id = 77 or ph.gametype_id = 78 or ph.gametype_id = 82 or ph.gametype_id = 85 or ph.gametype_id = 86 or ph.gametype_id = 87 or ph.gametype_id = 92 or ph.gametype_id = 94 or ph.gametype_id = 104 or ph.gametype_id = 123 or ph.gametype_id = 126) and ph.handtimestamp > to_timestamp('09/01/2012 02:14:35','mm/dd/yyyy hh24:mi:ss') and gt.pokergame = 1 and GT.GameTypeDescription = '$0.25/0.5 ZOOM NL' and GT.PokerGame In (1) order by PH.HandTimeStamp desc limit 100
It all started after I purged all my Fulltilt hands (2M) and run under database maintaince->vacuum with options analyze and full were selected. I am playing on Stars right now.
When I turn on logging database command then I see from dbtrace.log file that:
09/02/12 14:02:48 HM COMMAND: select * from gametypes
09/02/12 14:02:48 HM COMMAND FINISHED: select * from gametypes
09/02/12 14:02:48 HM COMMAND: select gt.gametypedescription as "gametypedescription",(case when ....
09/02/12 14:02:48 HM COMMAND FINISHED: select gt.gametypedescription as "gametypedescription",.....
09/02/12 14:02:48 HM COMMAND: select PH.PokerHand_ID,PH.PreFlopPlayerActionType_ID,PH.S treetWentAllin...
As you see the HM COMMAND FINISHED for the last query that displays hands did not appear. Also in Windows task manager I see that postgres is consuming 25% of CPU and if I take that las query and try to run it through PGAdminIII then it wont return output (doesn't hang but query is running endless). Also this action adds another process of postgres.exe that consumes 25% of CPU.
Full query:
select PH.PokerHand_ID,PH.PreFlopPlayerActionType_ID,PH.S treetWentAllin,PH.HandTimeStamp,GT.GameTypeDescrip tion, GT.PokerGame,PH.holecard1int,PH.holecard2int,PH.ho lecard3int,PH.holecard4int,PKH.FlopCard1int,PKH.fl opcard2int,PKH.flopcard3int,PKH.TurnCardint,PKH.Ri verCardint,PH.flopplayerActionType_ID,PH.turnplaye rActionType_ID,PH.riverplayerActionType_ID,PH.NetA mountWon,GT.BigBlind,PH.PositionType_ID,PH.preflop action_id,PH.didpfr,PH.didvpip,(case when PH.StreetWentAllIn > 0 then (Select SklanskyBucks from AllInSituations where ph.playerhand_id = allinsituations.playerhand_id) else 0 end) as SklanskyBucks,(case when PH.StreetWentAllIn > 0 then (Select EquityPct from AllInSituations where ph.playerhand_id = allinsituations.playerhand_id) else 0 end) as EquityPct,getwinningplayerjoincashver2(ph.pokerhan d_id) as winningplayerjoin from playerhandscashkeycolumns_hero ph join players pl on (pl.player_id = ph.player_id) join pokerhands_hero pkh on pkh.pokerhand_id = ph.pokerhand_id join playerhandscashmisc_hero phmisc on phmisc.playerhand_id = ph.playerhand_id join gametypes gt on gt.gametype_id = ph.gametype_id left join playerhandsflop_hero flop on ph.playerhand_id = flop.playerhand_id left join playerhandsturn_hero turn on ph.playerhand_id = turn.playerhand_id left join playerhandsriver_hero river on ph.playerhand_id = river.playerhand_id left join allinsituations_hero ev on ph.playerhand_id = ev.playerhand_id where (ph.player_id = 158041) and (ph.gametype_id = 1 or ph.gametype_id = 2 or ph.gametype_id = 9 or ph.gametype_id = 10 or ph.gametype_id = 11 or ph.gametype_id = 12 or ph.gametype_id = 23 or ph.gametype_id = 24 or ph.gametype_id = 32 or ph.gametype_id = 38 or ph.gametype_id = 39 or ph.gametype_id = 40 or ph.gametype_id = 41 or ph.gametype_id = 42 or ph.gametype_id = 43 or ph.gametype_id = 44 or ph.gametype_id = 52 or ph.gametype_id = 53 or ph.gametype_id = 64 or ph.gametype_id = 65 or ph.gametype_id = 66 or ph.gametype_id = 67 or ph.gametype_id = 68 or ph.gametype_id = 69 or ph.gametype_id = 70 or ph.gametype_id = 75 or ph.gametype_id = 77 or ph.gametype_id = 78 or ph.gametype_id = 82 or ph.gametype_id = 85 or ph.gametype_id = 86 or ph.gametype_id = 87 or ph.gametype_id = 92 or ph.gametype_id = 94 or ph.gametype_id = 104 or ph.gametype_id = 123 or ph.gametype_id = 126) and ph.handtimestamp > to_timestamp('09/01/2012 02:14:35','mm/dd/yyyy hh24:mi:ss') and gt.pokergame = 1 and GT.GameTypeDescription = '$0.25/0.5 ZOOM NL' and GT.PokerGame In (1) order by PH.HandTimeStamp desc limit 100