HUSNG min stack for reports

[pt4pt4pt4]

It seems to get very cpu intensive if you have more than a few tables opened. My Postgres jumps to almost double in cpu utilization.

Is there a way to only allow this function to execute every 10 seconds or so (avg duration of one hand)? I am thinking that it's checking multiple times a second which isn't required since it's not a priority stat, more of a feature.

[BillGatesIII]

It's executed after each hand played, we can't control that. The 'problem' is that if you've a player at your table you have for example 5k hands on, it will be called 5.000 times for that player (returning his/her current position every time) which is of course unnecessary. Haven't found a trick for this yet. A solution might be to change the function so that the 'slow inner part' is only executed once. Or maybe it's possible to control that behaviour in the column. Have to think some time about that though. And another trick is to use tourney_cache instead of tourney_hand_player_statistics. That might speed up things.

To be continued

[pt4pt4pt4]

It's executed after each hand played, we can't control that. The 'problem' is that if you've a player at your table you have for example 5k hands on, it will be called 5.000 times for that player (returning his/her current position every time) which is of course unnecessary. Haven't found a trick for this yet. A solution might be to change the function so that the 'slow inner part' is only executed once. Or maybe it's possible to control that behaviour in the column. Have to think some time about that though. And another trick is to use tourney_cache instead of tourney_hand_player_statistics. That might speed up things.

To be continued

I have read somewhere mentioned "last X hands", would that work if capped to one?

[BillGatesIII]

Nope, that will not work, it still has to determine that last hand from all the hands.

I just had another luminous idea but I'll have to try tonight to see if it works.

[BillGatesIII]

Ok. All my ideas didn't work which is kind of frustrating. Although, I shouldn't say 'didn't work' because they all do return the correct result but none of them is faster than the original So at the moment I don't know a better solution.

[pt4pt4pt4]

I mentioned this in another thread with sawee about W L records and using chip counts of all players to determine if HU or 9 man SNG. He mentioned it was a bit of work.

If you did have all the chip counts of all the players at the table, would you still be able to rank them like you did in your function?

[BillGatesIII]

We do have all the chip counts, they are calculated inside the function. Otherwise, I don't know how to determine the ranks.

[pt4pt4pt4]

We do have all the chip counts, they are calculated inside the function. Otherwise, I don't know how to determine the ranks.

What threw me off was that you mentioned that it was going through all their hands??? I still don't know why each starting hand and the very first hand stacks are not in the DB

Anyways, tested with only 2 (SNG & 5050) and then laster a 3rd SNG and this is what is happenning:

Definitely something to do with all the player's hands they have in total.

- Previously I tested it with all 1st timers and then a SAT with a lot of regs. It's with the Sats I noticed a big hit in performance since I have a lot of hands on them.

- Today played with some players with a few hundred hands and their ranking was always at the top 2 regardless of chip counts.

- Couldn't switch to session stats

- CPU utilization up to 50 % automatically.

Looks like all hands are being searched.


If you plan on messing around with this later, i would suggest removing the ranking and just displaying the chip count that your function is calculating to see what's happening at that point. And then look at the ranking as a separate stat to compare side by side.

i know about zero on postgres and I plan on keeping it at that level

[BillGatesIII]

We do have all the chip counts, they are calculated inside the function. Otherwise, I don't know how to determine the ranks.

What threw me off was that you mentioned that it was going through all their hands??? I still don't know why each starting hand and the very first hand stacks are not in the DB

Anyways, tested with only 2 (SNG & 5050) and then laster a 3rd SNG and this is what is happenning:

Definitely something to do with all the player's hands they have in total.

- Previously I tested it with all 1st timers and then a SAT with a lot of regs. It's with the Sats I noticed a big hit in performance since I have a lot of hands on them.

I'll try to explain how PT4 works without going into too much technical details. And I'll take the number of hands as an example. You would think that the number of hands you have on a opponent is somewhere stored in the database. Well, it's not. After each hand played, the number of hands is counted by PT4 as the sum of all hands somebody played. So every hand is read from the database. If you have 10k of hands on a villain, PT4 will read 10k of hands from tourney_hand_player_statistics (or cash_hand_player_statistics).

@mods: Please correct me if I'm wrong here.

If you plan on messing around with this later, i would suggest removing the ranking and just displaying the chip count that your function is calculating to see what's happening at that point. And then look at the ranking as a separate stat to compare side by side.

Removing the ranking will not make any difference. That's the fast part. Getting to the last hand played to calculate the current stack size is the slow part. I'll post a slightly modified version of the function tonight which might perform better on large databases.

i know about zero on postgres and I plan on keeping it at that level

[BillGatesIII]

If you feel like it, give this one a try.

Code: Select all

CREATE OR REPLACE FUNCTION current_position(tourney_hand_player_statistics)
  RETURNS bigint AS
$BODY$
select all_players.cur_pos
from (select thps.id_player, id_hand, row_number() over (order by thps.amt_before - thps.amt_blind - thps.amt_ante + thps.amt_won desc) as cur_pos
      from tourney_hand_player_statistics thps
      where thps.id_hand = (select max(id_hand)
                            from tourney_hand_summary
                            where id_tourney = $1.id_tourney)) as all_players
where all_players.id_player = $1.id_player
and all_players.id_hand = (select max(id_hand)
            from tourney_hand_summary
                 where id_tourney = $1.id_tourney);
$BODY$
  LANGUAGE sql STABLE
  COST 100;
ALTER FUNCTION current_position(tourney_hand_player_statistics)
  OWNER TO postgres;