PokerTracker

I turned on postgres logging to show the executed SQL so I can figure how to calculate the ITM% for hyper turbos HUSNG on Pokerstars.
I am trying to tweak the query and this is what I got so far but it takes ages to return a result (I have around 30k tournaments in my DB).
Can someone please show me how to make this query?

Thanks.

Code: Select all

select avg("cnt_itm")
from
(SELECT
             ((tourney_summary.id_tourney)) AS "cnt_tourneys",
             (((CASE when(tourney_results.amt_won > 0) THEN 1 ELSE 0 END))) AS "cnt_itm"
            
      FROM tourney_blinds,
           tourney_table_type ,
           tourney_summary tsttt,
                           tourney_hand_summary,
                           tourney_hand_player_statistics ,
                           tourney_results,
                           tourney_summary
      WHERE (tourney_results.id_tourney = tourney_hand_player_statistics.id_tourney
             AND tourney_results.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_summary.id_tourney)
        AND (tourney_hand_player_statistics.id_player =
               (SELECT id_player
                FROM player
                WHERE player_name_search='my sn here'
                  AND id_site='100'))
        AND (tourney_hand_summary.id_hand = tourney_hand_player_statistics.id_hand)
        AND (tourney_blinds.id_blinds = tourney_hand_player_statistics.id_blinds)
        AND (tsttt.id_tourney = tourney_hand_player_statistics.id_tourney
             AND tsttt.id_table_type = tourney_table_type.id_table_type)
        AND ((((((((tourney_summary.id_table_type IN
                      (SELECT lttt.id_table_type
                       FROM tourney_table_type lttt
                       WHERE lttt.val_seats = 2))))))
               AND (((((tourney_hand_summary.id_gametype = 1))
                      AND ((tourney_blinds.flg_nl))))
                    AND ((((tourney_hand_summary.id_site IN (100))))))))
             AND ((tourney_hand_summary.id_gametype IN(1))
                  AND (tourney_table_type.val_speed = 'S')
                  ))
      ) as data

The easier way to do this is to create a report in My Reports, have it group by Player and add the ITM stat to it. Then you can look at the SQL we use directly in our log file - click File --> Show User Data Folder. In the window that appears, open the PokerTracker4 text file. Your query will be near the bottom.

kraada wrote:The easier way to do this is to create a report in My Reports, have it group by Player and add the ITM stat to it. Then you can look at the SQL we use directly in our log file - click File --> Show User Data Folder. In the window that appears, open the PokerTracker4 text file. Your query will be near the bottom.

This what I did and this query was obtained form the log file but it takes a long time. Also PT4 hangs for this reason when making that report.

Download, import and try the attached report here. I just tested on a database with almost a thousand tournaments and it took about 1.5 seconds to complete.

kraada wrote:Download, import and try the attached report here. I just tested on a database with almost a thousand tournaments and it took about 1.5 seconds to complete.

Ok thanks. My database has 30k tournaments, I don't know if this is the reason the report does not work.

Let me know how long that report takes to load for you.

Running your report for all tournaments has not finished running yet.
I ran it for this month only (I played just a bit over 300 tournaments) it took more than the 1.5 seconds you reported for 1k tournaments.

It seems like some database problem? My computer is relatively decent i5-2500 CPU @ 3.30GHz, 8GB RAM running Windows 7 Ultimate.

These are the last 3 entries of pg_log:

Code: Select all

2012-06-05 14:05:00 BST LOG:  duration: 23072.000 ms  statement: SELECT id_player, id_site, str_player_name, count(distinct cnt_tourneys), sum(cnt_itm) 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", ((tourney_summary.id_tourney)) as "cnt_tourneys", (( (case when(tourney_results.amt_won > 0) then  1 else  0 end) )) as "cnt_itm" FROM  tourney_blinds,      tourney_table_type , tourney_summary tsttt, tourney_hand_summary, 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_hand_player_statistics.id_player = (SELECT id_player FROM player WHERE player_name_search='erdnase1973'  AND id_site='100'))   AND (tourney_hand_summary.id_hand = tourney_hand_player_statistics.id_hand)       AND (tourney_blinds.id_blinds = tourney_hand_player_statistics.id_blinds)    AND (tsttt.id_tourney = tourney_hand_player_statistics.id_tourney  AND tsttt.id_table_type = tourney_table_type.id_table_type) AND ((((((((tourney_summary.id_table_type in (SELECT lttt.id_table_type from tourney_table_type lttt where lttt.val_seats = 2))))))AND (((((tourney_hand_summary.id_gametype = 1))AND ((tourney_blinds.flg_nl))))AND ((((tourney_hand_summary.id_site in (100))))))))AND ((tourney_hand_summary.id_gametype in(1))AND (tourney_table_type.val_speed = 'S')AND (tourney_summary.date_start >= (to_char(current_timestamp, 'YYYY-MM-01 00:00:00')::timestamp + INTERVAL '-1 HOURS') AND tourney_summary.date_start <= (to_char(current_timestamp + INTERVAL '1 MONTH', 'YYYY-MM-01 00:00:00')::timestamp - INTERVAL '1 SECOND' + INTERVAL '-1 HOURS'))))  GROUP BY tourney_results.amt_won, tourney_summary.id_tourney, (tourney_hand_player_statistics.id_player), (player_real.id_site), (player.player_name) ) AS tnySumGroup GROUP BY id_player, id_site, str_player_name
2012-06-05 14:05:15 BST LOG:  duration: 0.999 ms  statement: SELECT typrelid FROM pg_catalog.pg_type WHERE typname='tourney_cache';
2012-06-05 14:05:15 BST LOG:  duration: 0.999 ms  statement: SELECT pa.attname, pt.typname FROM pg_catalog.pg_attribute pa, pg_catalog.pg_type pt WHERE pa.attrelid=18721 AND pa.attstattarget <> 0 AND pa.atttypid=pt.oid ORDER BY pa.attnum;


You've got extra filters on in that query for heads up and super turbo - if you turn those filters (in the sidebar in blue) off and uncheck all checkboxes for tournament type, flags and other such things, does it finish considerably faster for you?

Yes without the filters it is faster. However I am interested in the filters. Any workaround?

I think it's the table joins that are causing the slowdown. I'll look into it but I don't know how much there is that we can do (other than waiting and letting the actual query finish).