Custom reports

[Pyr00]

BUMP, any help ?

[WhiteRider]

I have reminded our PostgreSQL expert about your ticket.

[BillGatesIII]

Cant edit

I tried this on the All players report


This filters to hands where someone was dealt in with less than 300 hands in the database:

tourney_hand_player_statistics.id_hand in (SELECT thps.id_hand from tourney_hand_player_statistics thps where thps.id_player in (SELECT tc.id_player from tourney_cache tc where true group by tc.id_player HAVING sum(tc.cnt_hands) > 300))


but dont work :s, the tracker said like loading.. all the time https://gyazo.com/e74eea43296a970b9ea0a8e6cbd0d7a9

This should work if no other filters are selected.

See https://www.pokertracker.com/forums/viewtopic.php?f=61&t=58819&start=10#p289811 for more information.

[powi8]

Look closer, the report will take forever.

I'm still a rookie at custom PT4 reports and Pg but fortunately it hasn't taken long to learn to use PgAdmin for debugging.

Standard fast expression using cash_cache for All Players Report containing Player, My Currency Won and Hands:

player.id_player in (select cc.id_player from cash_cache cc where cc.id_player = player.id_player group by cc.id_player having sum(cc.cnt_hands) > 300)

I don't have a tournament database.

The report takes less than 1 minute to complete for a 14GB database containing 1 limit with over 22K players and over 2M hands and under 5 minutes for a 39GB database with 43K players and almost 6M hands.

Our expression is crippled when using the Small Stakes license of PT4 because of the following filter expression PT adds to ours to prevent high stakes players from using the SS license to create reports above NL50:

AND ((cash_hand_player_statistics.id_gametype = 1)AND (cash_hand_player_statistics.id_gametype<>1 OR (cash_hand_player_statistics.id_gametype=1 AND (cash_hand_player_statistics.id_limit in (SELECT hlrl.id_limit FROM cash_limit hlrl WHERE (hlrl.flg_nlpl=false and (CASE WHEN hlrl.limit_currency='SEK' THEN (hlrl.amt_bb*0.15) ELSE (CASE WHEN hlrl.limit_currency='INR' THEN (hlrl.amt_bb*0.020) ELSE (CASE WHEN hlrl.limit_currency='XSC' THEN 0.0 ELSE (CASE WHEN hlrl.limit_currency='PLY' THEN 0.0 ELSE hlrl.amt_bb END) END) END) END)<=1.01) or (hlrl.flg_nlpl=true and (CASE WHEN hlrl.limit_currency='SEK' THEN (hlrl.amt_bb*0.15) ELSE (CASE WHEN hlrl.limit_currency='INR' THEN (hlrl.amt_bb*0.020) ELSE (CASE WHEN hlrl.limit_currency='XSC' THEN 0.0 ELSE (CASE WHEN hlrl.limit_currency='PLY' THEN 0.0 ELSE hlrl.amt_bb END) END) END) END)<=0.51))))))

What it looks like when PT has added it's anti-piracy filter expression in the wrong place:



What it looks like without the anti-piracy filter expression:



We small stakes players can use the following expression and will benefit from the Hash Joins with our Small Stakes license of PT4:

player.id_player in (select player.id_player from cash_cache cc where cc.id_player = player.id_player having sum(cc.cnt_hands) > 300)

PT now places it's anti-piracy expression outside our expression because we are not using a group by clause.

[WhiteRider]

I'll make sure our PostgreSQL expert sees this, thanks.

[powi8]

Your anti-piracy filter expression is more trouble than it's worth especially now I have found an easy method for Postgresql to remove it.

Today I made a related report to the one I posted about earlier and it took 434 seconds to run because your anti-piracy expression caused Pg to use nested loops instead of hash joins. After I removed your anti-piracy expression and restarted the Postgresql service it took only 38 seconds to run. I shouldn't have to use PgAdmin to find out if your anti-piracy expression has crippled performance so I searched for a way for Pg to remove that expression and fortunately for my sanity I succeeded.

I'm sure there are robust methods you could have used to enforce your anti-piracy measures but even if there wasn't I would immediately upgrade my SS license to the full version if I needed to produce reports above NL50. I'm sure most people would upgrade because your software is awesome and if I was playing above NL50 I'd buy everything on your site.

[Pyr00]

This above is for cash game ? what about 3max tournament (spins) ?

[WhiteRider]

The same type of query extension is included for tournaments too. The development team are aware of this and i expect it to be changed soon. Any change will be applied to both cash and tournaments.