Optimization Problem - SQL

[warrior04]

wrote a query in sql... it works for player that doesn't have a lot of data
if i check the Hero player it take over a min

Code: Select all

SELECT (case when (count(PS.id_hand) != 0)
   then (cast(sum(case when PD.id_action_p = (SELECT id_action from lookup_actions where action = 'F')
   then 1 else 0 end) as real)
    / count(PS.id_hand))
   else (-1)
   end) as result,
   count(PS.id_hand) as result2
FROM holdem_hand_player_statistics as PS  , player as P  , holdem_limit L
 , holdem_hand_player_detail as PD WHERE PS.id_player = P.id_player  AND PS.id_limit = L.id_limit
 AND PS.position = 0 AND PD.id_player = P.id_player  AND PS.id_hand = PD.id_hand
 AND PS.flg_p_face_raise  AND PD.amt_p_raise_facing = 0.16 AND P.player_name = '********'  AND L.amt_bb = 0.04


how do i avoid such a long time?
is there a problem crossing between holdem_hand_player_statistics and holdem_hand_player_detail ?

[kraada]

count(*) is fairly slow in PostgreSQL - see the PostgreSQL wiki on that for more details. If you have a lot of data on your hero, it's just going to take a while to run queries like that.

[warrior04]

maybe count is a little slow...
but if i write this query it go in under 1 sec

Code: Select all

SELECT (case when (count(PS.id_hand) != 0)
   then (cast(sum(case when flg_p_fold
   then 1 else 0 end) as real)
    / count(PS.id_hand))
   else (-1)
   end) as result,
   count(PS.id_hand) as result2
FROM holdem_hand_player_statistics as PS  , player as P  , holdem_limit L
 WHERE PS.id_player = P.id_player  AND PS.id_limit = L.id_limit
AND PS.position = 0
AND PS.flg_p_face_raise   AND P.player_name = '********'  AND L.amt_bb = 0.04

it have to do with using both holdem_hand_player_statistics and holdem_hand_player_details...

[kraada]

Ah that's why - you forgot to join on id_player as well - you want PD.id_player = PS.id_player - without that you multiply the length of time everything has to work by almost an order of magnitude.

[warrior04]

ok tnx...
I'm learning how to work with the database and SQL at one project

work much better.... still a little slow.
hope i write this one right

Code: Select all

SELECT (case when (count(PS.id_hand) != 0)
   then (cast(sum(case when PD.id_action_p = (SELECT id_action from lookup_actions where action = 'F')
   then 1 else 0 end) as real)
    / count(PS.id_hand))
   else (-1)
   end) as result,
   count(PS.id_hand) as result2
FROM player as P  , holdem_limit L,

holdem_hand_player_statistics as PS JOIN holdem_hand_player_detail as PD
ON PD.id_player = PS.id_player  AND PS.id_hand = PD.id_hand

WHERE PS.id_player = P.id_player  AND PS.id_limit = L.id_limit
AND PS.position = 0 AND PD.id_player = P.id_player  AND PS.id_hand = PD.id_hand
AND PS.flg_p_face_raise  AND PD.amt_p_raise_facing = 0.16 AND P.player_name = '***********'  AND L.amt_bb = 0.04

more suggestion maybe?

[kraada]

I'm not certain at this point what else to do to speed that up for you - because of how it loops through the database, it's just going to take time.

[warrior04]

in the SELECT i use "count(PS.id_hand)" 3 times...
how can i save "result2" and use it in the other select part in one query?

[kraada]

PostgreSQL should realize that and only calculate it once . . . could you paste the output for EXPLAIN ANALYZE for that query?

[warrior04]

ran it twice... first one is after rest (clean memory database takes longest)

"Aggregate (cost=818.14..818.16 rows=1 width=8) (actual time=53911.325..53911.325 rows=1 loops=1)"
" InitPlan 1 (returns $0)"
" -> Seq Scan on lookup_actions (cost=0.00..1.64 rows=1 width=4) (actual time=8.698..8.711 rows=1 loops=1)"
" Filter: (action = 'F'::text)"
" -> Nested Loop (cost=0.00..816.48 rows=1 width=8) (actual time=101.976..53898.685 rows=1720 loops=1)"
" -> Nested Loop (cost=0.00..811.40 rows=1 width=10) (actual time=85.251..53863.487 rows=1720 loops=1)"
" -> Nested Loop (cost=0.00..777.60 rows=4 width=16) (actual time=45.761..36688.459 rows=5435 loops=1)"
" -> Seq Scan on player p (cost=0.00..281.68 rows=1 width=4) (actual time=4.356..31.711 rows=1 loops=1)"
" Filter: (player_name = '*********'::text)"
" -> Index Scan using "hhpd:idx2-id_player" on holdem_hand_player_detail pd (cost=0.00..495.88 rows=4 width=12) (actual time=41.399..36652.036 rows=5435 loops=1)"
" Index Cond: (pd.id_player = p.id_player)"
" Filter: (pd.amt_p_raise_facing = 0.16)"
" -> Index Scan using holdem_hand_player_statistics_primary_key on holdem_hand_player_statistics ps (cost=0.00..8.43 rows=1 width=10) (actual time=3.156..3.157 rows=0 loops=5435)"
" Index Cond: ((ps.id_player = pd.id_player) AND (ps.id_hand = pd.id_hand))"
" Filter: (ps.flg_p_face_raise AND (ps."position" = 0))"
" -> Index Scan using holdem_limit_primary_key on holdem_limit l (cost=0.00..5.07 rows=1 width=2) (actual time=0.016..0.017 rows=1 loops=1720)"
" Index Cond: (l.id_limit = ps.id_limit)"
" Filter: (l.amt_bb = 0.04)"
"Total runtime: 53911.504 ms"

second one (optimize by postgre)

"Aggregate (cost=818.14..818.16 rows=1 width=8) (actual time=758.187..758.188 rows=1 loops=1)"
" InitPlan 1 (returns $0)"
" -> Seq Scan on lookup_actions (cost=0.00..1.64 rows=1 width=4) (actual time=0.017..0.024 rows=1 loops=1)"
" Filter: (action = 'F'::text)"
" -> Nested Loop (cost=0.00..816.48 rows=1 width=8) (actual time=0.763..756.884 rows=1720 loops=1)"
" -> Nested Loop (cost=0.00..811.40 rows=1 width=10) (actual time=0.756..749.142 rows=1720 loops=1)"
" -> Nested Loop (cost=0.00..777.60 rows=4 width=16) (actual time=0.711..632.017 rows=5435 loops=1)"
" -> Seq Scan on player p (cost=0.00..281.68 rows=1 width=4) (actual time=0.018..3.097 rows=1 loops=1)"
" Filter: (player_name = '********'::text)"
" -> Index Scan using "hhpd:idx2-id_player" on holdem_hand_player_detail pd (cost=0.00..495.88 rows=4 width=12) (actual time=0.690..627.395 rows=5435 loops=1)"
" Index Cond: (pd.id_player = p.id_player)"
" Filter: (pd.amt_p_raise_facing = 0.16)"
" -> Index Scan using holdem_hand_player_statistics_primary_key on holdem_hand_player_statistics ps (cost=0.00..8.43 rows=1 width=10) (actual time=0.020..0.020 rows=0 loops=5435)"
" Index Cond: ((ps.id_player = pd.id_player) AND (ps.id_hand = pd.id_hand))"
" Filter: (ps.flg_p_face_raise AND (ps."position" = 0))"
" -> Index Scan using holdem_limit_primary_key on holdem_limit l (cost=0.00..5.07 rows=1 width=2) (actual time=0.003..0.003 rows=1 loops=1720)"
" Index Cond: (l.id_limit = ps.id_limit)"
" Filter: (l.amt_bb = 0.04)"
"Total runtime: 758.339 ms"

first one bugs me... because changing to a different query act more like the first one

[kraada]

So all of the time is used in three nested loops - the first is:

(count(PS.id_hand) != 0)

The second is:

sum(case when PD.id_action_p = (SELECT id_action from lookup_actions where action = 'F') then 1 else 0 end)

The third is:

count(PS.id_hand)

Let's attack #1: We are trying to avoid a divide by zero error here. We can certainly build a faster query than counting every hand to see if a player has any hands, though! Try instead:
(SELECT PS2.id_hand from holdem_hand_player_statistics PS2, player P2 where P2.id_player = PS2.id_player and P2.player_name = '******' LIMIT 1) > 0

Comparing that with the original query portion on my system, I get a 250x speed increase there.

That's definitely the low hanging fruit at the moment - with that fix, is this query fast enough for your needs?