New About Yours API Help
4.4 KB, SQL
        explain analyze SELECT 
                        g.gid,
                        EXTRACT(EPOCH FROM g.created)::int AS created,
                        EXTRACT(EPOCH FROM g.finished)::int AS finished,
                        g.letters AS letters,
                        g.values AS values,
                        g.bid AS bid,
                        CARDINALITY(g.pile) AS pilelen,
                        m.tiles AS tiles,
                        m.score AS score,
                        CASE WHEN g.player1 = 5 THEN g.player1 ELSE g.player2 END AS player1,
                        CASE WHEN g.player1 = 5 THEN g.player2 ELSE g.player1 END AS player2,
                        CASE WHEN g.player1 = 5 THEN g.score1  ELSE g.score2  END AS score1,
                        CASE WHEN g.player1 = 5 THEN g.score2  ELSE g.score1  END AS score2,
                        CASE WHEN g.player1 = 5 THEN g.state1  ELSE g.state2  END AS state1,
                        CASE WHEN g.player1 = 5 THEN g.hint1   ELSE g.hint2   END AS hint1,
                        CASE WHEN g.player1 = 5 THEN g.chat1   ELSE g.chat2   END AS chat1,
                        u1.elo AS elo1,
                        u2.elo AS elo2,                        
                        i2.lat AS lat2,
                        i2.lng AS lng2,                         
                        s1.given AS given1,
                        s2.given AS given2,
                        s1.photo AS photo1,
                        s2.photo AS photo2,
                        EXTRACT(EPOCH FROM CASE WHEN g.player1 = 5 THEN g.played1 ELSE g.played2 END)::int AS played1,
                        EXTRACT(EPOCH FROM CASE WHEN g.player1 = 5 THEN g.played2 ELSE g.played1 END)::int AS played2,
                        ARRAY_TO_STRING(CASE WHEN g.player1 = 5 THEN g.hand1 ELSE g.hand2 END, '') AS hand1,
                        CASE 
                                
                                WHEN g.finished IS NOT NULL THEN NULL
                                
                                WHEN g.player2 IS NULL THEN NULL
                                
                                WHEN g.player1 = 5 AND g.played1 < g.played2 
                                        
                                        THEN EXTRACT(EPOCH FROM g.played2 + interval '24 hour' - CURRENT_TIMESTAMP)::int
                                
                                WHEN g.player2 = 5 AND (g.played2 IS NULL OR g.played2 < g.played1)
                                        
                                        THEN EXTRACT(EPOCH FROM g.played1 + interval '24 hour' - CURRENT_TIMESTAMP)::int
                                ELSE NULL
                        END AS left1,
                        CASE 
                                
                                WHEN g.finished IS NOT NULL THEN NULL
                                
                                WHEN g.player2 IS NULL THEN NULL
                                
                                WHEN g.player1 = 5 AND (g.played2 IS NULL OR g.played2 < g.played1)
                                        
                                        THEN EXTRACT(EPOCH FROM g.played1 + interval '24 hour' - CURRENT_TIMESTAMP)::int
                                
                                WHEN g.player2 = 5 AND g.played1 < g.played2 
                                        
                                        THEN EXTRACT(EPOCH FROM g.played2 + interval '24 hour' - CURRENT_TIMESTAMP)::int
                                ELSE NULL
                        END AS left2
                FROM words_games g 
                LEFT JOIN LATERAL (select tiles, score from words_moves m where m.gid = g.gid order by m.played desc limit 1) as m on true
                LEFT JOIN words_users u1 ON u1.uid = 5
                LEFT JOIN words_users u2 ON u2.uid = (CASE WHEN g.player1 = 5 THEN g.player2 ELSE g.player1 END)
                LEFT JOIN words_geoip i2 ON (CASE WHEN g.player1 = 5 THEN u2.ip ELSE u1.ip END) <<= i2.block               
                LEFT JOIN LATERAL (select * from words_social as s1 where s1.uid = u1.uid order by s1.stamp desc limit 1) as s1 on true
                LEFT JOIN LATERAL (select * from words_social as s2 where s2.uid = u2.uid order by s2.stamp desc limit 1) as s2 on true
                WHERE 5 IN (g.player1, g.player2)
                AND COALESCE(g.finished,'infinity') > (CURRENT_TIMESTAMP - INTERVAL '1 day')
Pasted 10 months, 2 weeks ago — Expires in 45 days
URL: http://dpaste.com/1AQM800