Database extremely slow after creating custom stats

[Solwa54]

Hi,

I recently added some custom stats that show how often a players flop bet was air/draw/value. Obviously these stats rely on both cash_hand_player_statistics and cash_hand_player_combination. But ever since that my database has become increasingly slower to import hands, taking 10+ seconds. Normal database housekeeping does nothing to fix the issue. Neither did upgrading to PostgreSQL 16.

Is this just a bridge too far for Pokertracker, or is there something I can do to speed things up?

Sample column I used:

Code: Select all

sum(if[cash_hand_player_statistics.flg_f_bet AND cash_hand_player_combinations.flg_f_highcard AND not cash_hand_player_combinations.flg_f_gutshot_draw AND not cash_hand_player_combinations.flg_f_straight_draw AND not cash_hand_player_combinations.flg_f_2gutshot_draw AND not cash_hand_player_combinations.flg_f_flush_draw AND cash_hand_player_statistics.id_gametype = 1 AND cash_hand_player_statistics.holecard_1 > 0 AND cash_hand_player_statistics.holecard_2 > 0,1,0])

[Flag_Hippo]

Rebuild your database cache via 'Rebuild Cache -> Full Cache Rebuild' option which is above the 'Housekeeping' option.

[Solwa54]

Rebuild your database cache via 'Rebuild Cache -> Full Cache Rebuild' option which is above the 'Housekeeping' option.

Thanks, but I already did that. I even removed those new stats from the cache (then rebuilt it again), which sped things up a little bit, but it's still 4.5-5 seconds to update the cache for every hand.

Code: Select all

2025-02-18 12:18:53.807 CET [13559] pokertracker@PT4_DB LOG:  duration: 5066.323 ms  statement: SELECT setting_value, update_cash_cache(), update_cash_custom_cache() FROM settings WHERE setting_name='cache_update:cash';
2025-02-18 12:19:02.405 CET [13559] pokertracker@PT4_DB LOG:  duration: 4745.974 ms  statement: SELECT setting_value, update_cash_cache(), update_cash_custom_cache() FROM settings WHERE setting_name='cache_update:cash';
2025-02-18 12:19:07.300 CET [13559] pokertracker@PT4_DB LOG:  duration: 4893.519 ms  statement: SELECT setting_value, update_cash_cache(), update_cash_custom_cache() FROM settings WHERE setting_name='cache_update:cash';
2025-02-18 12:19:13.541 CET [13559] pokertracker@PT4_DB LOG:  duration: 4659.442 ms  statement: SELECT setting_value, update_cash_cache(), update_cash_custom_cache() FROM settings WHERE setting_name='cache_update:cash';
2025-02-18 12:19:18.323 CET [13559] pokertracker@PT4_DB LOG:  duration: 4766.879 ms  statement: SELECT setting_value, update_cash_cache(), update_cash_custom_cache() FROM settings WHERE setting_name='cache_update:cash';
2025-02-18 12:19:54.528 CET [13559] pokertracker@PT4_DB LOG:  duration: 4447.492 ms  statement: SELECT setting_value, update_cash_cache(), update_cash_custom_cache() FROM settings WHERE setting_name='cache_update:cash';
2025-02-18 12:19:59.315 CET [13559] pokertracker@PT4_DB LOG:  duration: 4729.028 ms  statement: SELECT setting_value, update_cash_cache(), update_cash_custom_cache() FROM settings WHERE setting_name='cache_update:cash';
2025-02-18 12:20:12.252 CET [13559] pokertracker@PT4_DB LOG:  duration: 4737.738 ms  statement: SELECT setting_value, update_cash_cache(), update_cash_custom_cache() FROM settings WHERE setting_name='cache_update:cash';
2025-02-18 12:20:17.214 CET [13559] pokertracker@PT4_DB LOG:  duration: 4959.466 ms  statement: SELECT setting_value, update_cash_cache(), update_cash_custom_cache() FROM settings WHERE setting_name='cache_update:cash';

[Flag_Hippo]

I even removed those new stats from the cache (then rebuilt it again), which sped things up a little bit, but it's still 4.5-5 seconds to update the cache for every hand.

Did you just delete the statistics or did you delete the statistics and the custom columns? If you did not delete the custom columns then delete those as well before trying the cache rebuild again.

[Solwa54]

I even removed those new stats from the cache (then rebuilt it again), which sped things up a little bit, but it's still 4.5-5 seconds to update the cache for every hand.

Did you just delete the statistics or did you delete the statistics and the custom columns? If you did not delete the custom columns then delete those as well before trying the cache rebuild again.

I deselected the cache option on the columns and saved them, then checked that the columns were gone from the cash_custom_cache table after the cache rebuild.

[Flag_Hippo]

In that case please turn on 'Enable Logging' in PokerTracker 4 via 'Configure -> Options' and then restart PokerTracker 4. Next reproduce this issue and then create a support bundle via the 'File -> Create Support Bundle' menu option in PokerTracker 4. When that file has been saved attach your support bundle to a Support Ticket where a technician can review your logs follow up with you.

[Solwa54]

In that case please turn on 'Enable Logging' in PokerTracker 4 via 'Configure -> Options' and then restart PokerTracker 4. Next reproduce this issue and then create a support bundle via the 'File -> Create Support Bundle' menu option in PokerTracker 4. When that file has been saved attach your support bundle to a Support Ticket where a technician can review your logs follow up with you.

Will do. Thanks!

[fedayn10]

I have the same problem when added some custom stats Fold to Cbet IP/OOP SPR, and Delayed Cbet River + some stats from Download Warehouse Delayed River CBet, Fold to Preflop 4bet After Squeeze, Fold to Cold 4bet after 3bet
CBet flop vs 2 opponents in non 3Bet pot, and Preflop Open Limp SB and Fold + the statistic Fold to Cbet IP & OOP to can create custom statistic to Fold to Cbet IP/OOP SPR Flop, Turn and River.
When the direct stats appear, had to click on each player individually and the numbers то Popup appeared after 7-8 seconds. I was trying after to Rebuild database cache via 'Rebuild Cache -> Full Cache Rebuild' and waited about 30 minutes
" i have big database" and decided to quit the procedure, but there was no way from PT4 options and restarted the computer. When I loaded Pt4 again i ahd note for crash report with my Email to send which i did.
Now tryed to play 4 tables and i had Hud only to one or 2 tables, it didn't show at the other tables. When I pressed stop import - PT4 i had note not responding.. Close-open again and even now i dont have HUD.

[fedayn10]

Tryed again to Rebuild database cache via 'Rebuild Cache -> Full Cache Rebuild and to wait till the end, but this time is not Reindexing.. so, now i can only Rebuild database cache via 'Rebuild Cache -> Custom

[Flag_Hippo]

I have the same problem when added some custom stats Fold to Cbet IP/OOP SPR, and Delayed Cbet River + some stats from Download Warehouse Delayed River CBet, Fold to Preflop 4bet After Squeeze, Fold to Cold 4bet after 3bet
CBet flop vs 2 opponents in non 3Bet pot, and Preflop Open Limp SB and Fold + the statistic Fold to Cbet IP & OOP to can create custom statistic to Fold to Cbet IP/OOP SPR Flop, Turn and River.
When the direct stats appear, had to click on each player individually and the numbers то Popup appeared after 7-8 seconds. I was trying after to Rebuild database cache via 'Rebuild Cache -> Full Cache Rebuild' and waited about 30 minutes

If you've added new custom statistics then that would just require a custom cache rebuild so those stats can use the database cache.

" i have big database" and decided to quit the procedure, but there was no way from PT4 options and restarted the computer. When I loaded Pt4 again i ahd note for crash report with my Email to send which i did.
Now tryed to play 4 tables and i had Hud only to one or 2 tables, it didn't show at the other tables. When I pressed stop import - PT4 i had note not responding.. Close-open again and even now i dont have HUD.

Once you have started a cache rebuild it needs to finish. Since you quit while it was in progress that database will perform poorly and potentially be unusable until the cache rebuild has been started again and completed. If you have a large database and/or a slow HDD you'll want to run it when you're not using PokerTracker 4 for a while (such as overnight maybe). Make sure that your computer does not power itself off or go to sleep while it's running or you will need to run it again from the start. You should also make sure that you have enough free space on your computer (equivalent to the size of the database) for the cache rebuild to complete.

Tryed again to Rebuild database cache via 'Rebuild Cache -> Full Cache Rebuild and to wait till the end, but this time is not Reindexing.. so, now i can only Rebuild database cache via 'Rebuild Cache -> Custom

A custom cache rebuild probably isn't going to fix things since you've already started and cancelled a full cache rebuild. You will need to start a new full cache rebuild and allow that to complete. If you want to run housekeeping then do that before the full cache rebuild in this order:

1. Tune PostgreSQL (this only needs to be completed once ever. If this step fails, you can safely skip to step #2)
2. Reindex
3. Cluster
4. Vacuum/Analyze

After the housekeeping is complete rebuild the database cache via 'Rebuild Cache -> Full Cache Rebuild'.