Version v2.7.0 of the documentation is no longer actively maintained. The site that you are currently viewing is an archived snapshot. For up-to-date documentation, see the latest version.

Solve the 24-Point Card Game with One SQL Statement

A PostgreSQL solution that returns one expression for the 24-point card game using a single SQL statement.

Develop

Visualization

Problem

The challenge is described in Database Programming Contest: Calculate 24 with One SQL Statement.

A cards table has an auto-incrementing id and four columns, c1 through c4; each card is a random integer from 1 to 10. One SQL statement must return a valid arithmetic expression whose result is 24, or NULL when no solution exists.

The rules allow only addition, subtraction, multiplication, division, and parentheses. Every input number must be used exactly once. Built-in database functions are allowed, but stored procedures, user-defined functions, and code blocks are not. The submitted SQL must be smaller than 10 KB and was evaluated on a 4-core, 32 GB server.

Prime-number lookup is the fastest general approach, but the complete lookup text is slightly larger than 10 KB. MySQL provides built-in COMPRESS and UNCOMPRESS; PostgreSQL uses the pgsql-gzip extension for the compressed variant. The following is the PostgreSQL solution.

Create Random Test Data

CREATE SCHEMA poker24;
DROP TABLE IF EXISTS poker24.cards;
CREATE TABLE poker24.cards AS
SELECT i                   AS id,
       ceil(random() * 10) AS c1,
       ceil(random() * 10) AS c2,
       ceil(random() * 10) AS c3,
       ceil(random() * 10) AS c4
FROM generate_series(1, 1000000) i;

ALTER TABLE poker24.cards ADD PRIMARY KEY (id);

Prime-Encoded Lookup Solution

The core idea assigns each card value a prime number. The product of four primes is an order-independent key, so every solvable multiset can be joined to one precomputed expression in constant time.

EXPLAIN ANALYZE
WITH a(i, result) AS (
    SELECT (split_part(kv, ':', 1))::INTEGER AS i, split_part(kv, ':', 2) AS result
    FROM regexp_split_to_table('152:((1+1)+1)*8,156:(6*2)*(1+1),204:(7+1)*(2+1),228:((1*1)+2)*8,276:(9-1)*(2+1),348:(10+2)*(1+1),140:(4*3)*(1+1),220:(5+1)*(3+1),260:((1+1)+6)*3,340:((1*1)+7)*3,380:(8*3)+(1-1),460:(9+3)*(1+1),580:(10-(1+1))*3,196:((1+1)+4)*4,308:((1*1)+5)*4,364:(6*4)+(1-1),476:(7-(1*1))*4,532:(8+4)*(1+1),644:(9-1)*(4-1),812:((1+1)*10)+4,484:(5*5)-(1*1),572:(5-(1*1))*6,748:(7+5)*(1+1),836:(5-(1+1))*8,676:(6+6)*(1+1),988:(8*6)/(1+1),1196:((1+1)*9)+6,1972:((1+1)*7)+10,1444:((1+1)*8)+8,126:(4*2)*(2+1),198:(2+2)*(5+1),234:(6+2)*(2+1),306:(2+2)*(7-1),342:((2-1)+2)*8,414:((2+1)+9)*2,522:(10-2)*(2+1),150:(3*2)*(3+1),210:((2+1)+3)*4,330:(5+3)*(2+1),390:((2-1)+3)*6,510:(7*3)+(2+1),570:(8*3)*(2-1),690:(9*3)-(2+1),870:(10-(2*1))*3,294:(4+4)*(2+1),462:((2-1)+5)*4,546:(6*4)*(2-1),714:(7-(2-1))*4,798:(4-(2-1))*8,966:(9-(2+1))*4,1218:((2*1)*10)+4,726:(5*5)-(2-1),858:(5-(2-1))*6,1122:(7+5)*(2*1),1254:(5-(2*1))*8,1518:((2+1)*5)+9,1914:(10*2)+(5-1),1014:((2+1)*6)+6,1326:(7-(2+1))*6,1482:(6-(2+1))*8,1794:((2*1)*9)+6,2262:((2+1)*10)-6,1734:((7*7)-1)/2,1938:(8*2)+(7+1),2346:(9*2)+(7-1),2958:((2*1)*7)+10,2166:((2*1)*8)+8,2622:(9*8)/(2+1),3306:((8-1)*2)+10,250:(3+3)*(3+1),350:((3+1)+4)*3,550:(5+3)*(3*1),650:((3-1)+6)*3,850:(7*3)+(3*1),950:((8+1)*3)-3,1150:(9-3)*(3+1),1450:(10-(3-1))*3,490:((3-1)+4)*4,770:(5*4)+(3+1),910:6/(1-(3/4)),1190:(7*4)-(3+1),1330:((3+1)*4)+8,1610:(9-(3*1))*4,2030:(10-4)*(3+1),1430:(6*3)+(5+1),1870:(7+5)*(3-1),2090:(5-(3-1))*8,2530:((3*1)*5)+9,3190:(10*3)-(5+1),1690:(6+6)*(3-1),2210:(7-(3*1))*6,2470:(8-(3+1))*6,2990:((3-1)*9)+6,3770:((3*1)*10)-6,2890:(7-3)*(7-1),3230:(7-(3+1))*8,3910:(9/3)*(7+1),4930:((3-1)*7)+10,3610:((3+1)*8)-8,4370:(9*8)/(3*1),5510:(8/3)*(10-1),5290:(9/3)*(9-1),6670:((10+1)*3)-9,8410:(10+10)+(3+1),686:((4+1)*4)+4,1078:(5*4)+(4*1),1274:((6+1)*4)-4,1666:(7*4)-(4*1),1862:((4*1)*4)+8,2254:(9-(4-1))*4,2842:(10-4)*(4*1),1694:(5*4)+(5-1),2002:6/((5/4)-1),2618:(7*4)-(5-1),2926:(8-4)*(5+1),3542:((4-1)*5)+9,4466:(10-4)*(5-1),2366:((4+1)*6)-6,3094:(7-(4-1))*6,3458:(6-(4-1))*8,4186:(9-(4+1))*6,5278:((4-1)*10)-6,4046:(7-4)*(7+1),4522:(7-(4*1))*8,5474:(7-4)*(9-1),5054:(8-(4+1))*8,6118:(9*8)/(4-1),9338:(10+9)+(4+1),11774:(10+10)+(4*1),2662:(5-(1/5))*5,3146:(6*5)-(5+1),5566:(9-5)*(5+1),7018:((10-5)*5)-1,3718:((5*1)*6)-6,4862:(6*5)-(7-1),5434:(8-(5-1))*6,6578:(9-(5*1))*6,8294:(10-6)*(5+1),7106:(7-(5-1))*8,8602:(9-5)*(7-1),10846:(7*5)-(10+1),7942:((5-1)*8)-8,9614:(9-(5+1))*8,12122:(10+8)+(5+1),11638:(9+9)+(5+1),14674:(10+9)+(5*1),18502:(10+10)+(5-1),4394:((6-1)*6)-6,6422:6/(1-(6/8)),7774:(9-(6-1))*6,9802:(10-6)*(6*1),10166:(9-6)*(7+1),12818:(10+7)+(6+1),9386:(8-(6-1))*8,11362:(9+8)+(6+1),14326:(10-(6+1))*8,13754:(9+9)+(6*1),17342:(10+9)+(6-1),13294:(9+7)+(7+1),16762:(10-7)*(7+1),12274:(8+8)+(7+1),14858:(9-(7-1))*8,18734:(10+8)+(7-1),17986:(9+9)+(7-1),22678:(10-7)*(9-1),13718:(8+8)+(8*1),16606:(9+8)+(8-1),20938:(10-(8-1))*8,135:(3*2)*(2+2),189:(4+2)*(2+2),297:((5*2)+2)*2,459:((7*2)-2)*2,513:(8-2)*(2+2),621:((9+2)*2)+2,783:(10*2)+(2+2),225:(3+3)*(2+2),315:((2+2)+4)*3,495:((5*2)-2)*3,585:((2/2)+3)*6,765:((2/2)+7)*3,855:(8*3)+(2-2),1035:(9-3)*(2+2),1305:((10+3)*2)-2,441:((4*2)-2)*4,693:(5*4)+(2+2),819:(6*4)+(2-2),1071:(7*4)-(2+2),1197:((2+2)*4)+8,1449:(9*2)+(4+2),1827:(10-4)*(2+2),1089:(5*5)-(2/2),1287:(5-(2/2))*6,1683:(7*2)+(5*2),1881:((8+5)*2)-2,2277:((5-2)+9)*2,2871:((5+2)*2)+10,1521:(6/2)*(6+2),1989:((7+2)*2)+6,2223:(8-(2+2))*6,2691:((6/2)+9)*2,3393:(10*2)+(6-2),2601:((7-2)+7)*2,2907:(7-(2+2))*8,4437:((10/2)+7)*2,3249:((2+2)*8)-8,3933:(9*2)+(8-2),4959:(10-2)+(8*2),6003:((9-2)*2)+10,7569:(10+10)+(2+2),375:((3+2)+3)*3,825:((5+2)*3)+3,975:((3-2)+3)*6,1275:((3-2)+7)*3,1425:(8*3)*(3-2),1725:((3+2)*3)+9,2175:(10*3)-(3*2),735:((3+2)*4)+4,1155:((3-2)+5)*4,1365:(6*4)*(3-2),1785:(7-(3-2))*4,1995:(4-(3-2))*8,2415:(9*4)/(3/2),3045:(10*3)-(4+2),1815:(5*5)-(3-2),2145:(5-(3-2))*6,2805:(7*3)+(5-2),3135:(5+3)+(8*2),3795:(9-5)*(3*2),4785:(5-3)*(10+2),2535:((3+2)*6)-6,3315:(7*3)+(6/2),3705:((8+2)*3)-6,4485:(9-(3+2))*6,5655:(10-6)*(3*2),4335:(7+3)+(7*2),4845:(8/3)*(7+2),5865:(9+7)*(3/2),7395:(7-3)+(10*2),5415:(8-(3+2))*8,6555:(9-(3*2))*8,8265:(10+8)+(3*2),7935:(9+9)+(3*2),10005:(10+9)+(3+2),12615:((10-3)*2)+10,1029:((4-2)+4)*4,1617:((5+2)*4)-4,1911:((4*2)-4)*6,2499:(7-4)*(4*2),2793:(8-4)*(4+2),3381:((9-2)*4)-4,4263:((4-2)*10)+4,2541:((5+5)*2)+4,3003:(6*5)-(4+2),3927:(7+5)*(4-2),4389:(5-(4-2))*8,5313:(9-5)*(4+2),6699:(10+4)+(5*2),3549:(6+6)*(4-2),4641:(7-4)*(6+2),5187:(8*6)/(4-2),6279:((4-2)*9)+6,7917:(10-6)*(4+2),6069:((7+7)*2)-4,6783:((7*2)-8)*4,8211:(9+7)+(4*2),10353:((4-2)*7)+10,7581:((4-2)*8)+8,9177:(9-(4+2))*8,11571:(10+8)+(4+2),11109:(9+9)+(4+2),14007:(10-4)+(9*2),17661:((4/10)+2)*10,6171:(5+5)+(7*2),6897:((5/5)+2)*8,8349:((5-2)*5)+9,10527:(5-(2/10))*5,5577:((5-2)*6)+6,7293:(7-(5-2))*6,8151:(6-(5-2))*8,9867:((5/2)*6)+9,12441:((5-2)*10)-6,9537:(7+7)+(5*2),10659:((5*2)-7)*8,12903:(7*5)-(9+2),16269:(10+7)+(5+2),11913:(8*5)-(8*2),14421:(9+8)+(5+2),18183:(10-(5+2))*8,22011:(9-5)+(10*2),27753:(10/5)*(10+2),6591:(6+6)+(6*2),8619:(7-(6/2))*6,9633:(8-(6-2))*6,11661:(9-6)*(6+2),14703:(10+6)+(6+2),12597:(7-(6-2))*8,15249:(9+7)+(6+2),19227:(10-7)*(6+2),14079:(8+8)+(6+2),17043:((6*2)-9)*8,21489:(10-8)*(6*2),20631:((9-6)+9)*2,26013:(9-6)*(10-2),32799:(10+10)+(6-2),16473:(8+7)+(7+2),25143:((10/7)+2)*7,18411:(8-(7-2))*8,22287:((9+7)*2)-8,34017:(10+9)+(7-2),42891:(10-7)*(10-2),20577:((8/2)*8)-8,24909:(9-(8-2))*8,31407:(10+8)+(8-2),30153:(9+9)+(8-2),38019:(10-(9-2))*8,47937:(10+10)+(8/2),58029:(10+9)+(10/2),625:((3*3)*3)-3,875:((3*3)-3)*4,1375:(5*3)+(3*3),1625:(6*3)+(3+3),2125:(7-3)*(3+3),2375:((3+3)-3)*8,2875:(9-(3/3))*3,3625:(10*3)-(3+3),1225:(4*3)+(4*3),1925:((3/3)+5)*4,2275:(6*4)+(3-3),2975:(7-(3/3))*4,3325:(8-4)*(3+3),4025:(9-(4-3))*3,3025:(5*5)-(3/3),3575:(6*5)-(3+3),4675:((5*3)-7)*3,6325:(9-5)*(3+3),7975:(10+5)+(3*3),4225:((6/3)+6)*3,5525:(7*3)+(6-3),6175:((3*3)-6)*8,7475:(9+6)+(3*3),9425:(10-6)*(3+3),7225:((3/7)+3)*7,8075:(8+7)+(3*3),9775:(9-3)*(7-3),9025:8/(3-(8/3)),10925:(9-(3+3))*8,13775:(10+8)+(3+3),13225:(9+9)+(3+3),16675:(10*3)-(9-3),1715:((4+3)*4)-4,2695:((4-3)+5)*4,3185:(6*4)*(4-3),4165:(7-(4-3))*4,4655:((4+3)-4)*8,5635:(9*4)-(4*3),7105:((10-3)*4)-4,4235:(5*5)-(4-3),5005:(5-(4-3))*6,6545:(7+5)+(4*3),7315:(8*4)-(5+3),8855:((5*3)-9)*4,11165:(10/5)*(4*3),5915:(6+6)+(4*3),8645:(8-6)*(4*3),10465:(9-(6-3))*4,13195:(10-4)+(6*3),10115:(7*4)-(7-3),11305:((7-3)*4)+8,13685:(9-7)*(4*3),17255:(10+7)+(4+3),15295:(9+8)+(4+3),19285:(10-(4+3))*8,18515:(9+9)*(4/3),29435:(10*3)-(10-4),7865:((5+5)*3)-6,10285:(7+5)*(5-3),11495:(8-5)*(5+3),13915:(9-(5/5))*3,9295:(6+6)*(5-3),12155:(7+5)*(6/3),13585:(8*6)/(5-3),16445:(9-6)*(5+3),20735:(10+6)+(5+3),17765:(8-5)+(7*3),21505:(9+7)+(5+3),27115:(10-7)*(5+3),19855:(8+8)+(5+3),24035:(9*3)-(8-5),29095:((5/3)*9)+9,36685:(10/5)*(9+3),46255:(10-(10/5))*3,10985:((6-3)*6)+6,14365:(7-(6-3))*6,16055:((6+3)-6)*8,19435:(9+6)+(6+3),24505:((6-3)*10)-6,18785:((7-6)+7)*3,20995:(8+7)+(6+3),25415:(9-6)+(7*3),32045:((6/3)*7)+10,23465:((6/3)*8)+8,28405:(9*8)/(6-3),35815:(10-(8-6))*3,34385:(9*3)-(9-6),43355:(10-6)*(9-3),54665:(3-(6/10))*10,24565:(7+7)+(7+3),27455:((7+3)-7)*8,33235:(9-(7/7))*3,41905:(10-7)+(7*3),30685:((7-3)*8)-8,37145:(9-(8-7))*3,44965:(9-7)*(9+3),56695:(9*3)-(10-7),71485:(10+10)+(7-3),34295:((8+3)-8)*8,41515:(9-8)*(8*3),52345:((10*8)-8)/3,50255:(9-9)+(8*3),63365:(10+9)+(8-3),79895:(10-10)+(8*3),60835:(9+9)+(9-3),76705:((9+9)-10)*3,96715:(9-(10/10))*3,2401:(4*4)+(4+4),3773:((4/4)+5)*4,4459:((4+4)-4)*6,5831:(7-4)*(4+4),6517:(8*4)-(4+4),7889:((9-4)*4)+4,9947:(10*4)-(4*4),5929:(5*5)-(4/4),7007:(5-(4/4))*6,9163:(7-(5-4))*4,10241:(8-5)*(4+4),15631:((10-5)*4)+4,12103:(8+4)*(6-4),14651:(9-6)*(4+4),18473:(10+6)+(4+4),14161:(4-(4/7))*7,15827:(7*4)-(8-4),19159:(9+7)+(4+4),24157:(10-7)*(4+4),17689:(8+8)+(4+4),21413:(9*4)-(8+4),26999:(10-4)*(8-4),41209:((10*10)-4)/4,9317:(5*5)-(5-4),11011:((5+4)-5)*6,14399:(7-(5/5))*4,16093:(4-(5/5))*8,19481:(9-5)+(5*4),24563:(10+5)+(5+4),13013:(6-5)*(6*4),17017:(7+5)*(6-4),19019:((5+4)-6)*8,23023:(9+6)+(5+4),29029:(10-6)+(5*4),22253:(7*5)-(7+4),24871:(8+7)+(5+4),30107:((7-4)*5)+9,37961:((7-5)*10)+4,27797:(5-(8/4))*8,33649:(9-(8-5))*4,42427:(10/5)*(8+4),40733:((9/9)+5)*4,51359:(9-5)*(10-4),64757:((10/5)*10)+4,15379:((6+4)-6)*6,20111:(7-6)*(6*4),22477:(8+6)+(6+4),27209:((6-4)*9)+6,34307:(10+6)*(6/4),26299:(7+7)+(6+4),29393:((6+4)-7)*8,35581:(9+7)*(6/4),44863:((6-4)*7)+10,32851:((6-4)*8)+8,39767:(9-8)*(6*4),50141:((8-6)*10)+4,48139:(9-9)+(6*4),60697:(10-9)*(6*4),76531:(10-10)+(6*4),34391:(7-(7/7))*4,38437:((7+7)-8)*4,42959:((7+4)-8)*8,52003:(9*8)/(7-4),65569:((7/4)*8)+10,62951:(7-(9/9))*4,79373:(10*4)-(9+7),100079:(7-(10/10))*4,48013:((8-4)*8)-8,58121:((8+4)-9)*8,73283:(10-8)*(8+4),70357:(4-(9/9))*8,88711:((9+4)-10)*8,111853:(10+10)+(8-4),107387:(10+9)+(9-4),14641:(5*5)-(5/5),17303:(5*5)-(6-5),30613:(9+5)+(5+5),20449:((5+5)-6)*6,26741:(5*5)-(7-6),29887:(8+6)+(5+5),34969:(7+7)+(5+5),39083:((5+5)-7)*8,59653:(10/5)*(7+5),43681:(5*5)-(8/8),52877:(5*5)-(9-8),66671:(10+5)*(8/5),64009:(5*5)-(9/9),80707:(5*5)-(10-9),101761:(5*5)-(10/10),24167:(5-(6/6))*6,31603:(7+6)+(6+5),35321:((8-5)*6)+6,42757:(9*6)-(6*5),53911:((10-5)*6)-6,41327:(5-(7/7))*6,46189:(8-6)*(7+5),55913:((7-5)*9)+6,51623:((6+5)-8)*8,62491:((8+5)-9)*6,78793:(6*5)/(10/8),75647:((9-6)*5)+9,95381:((9+5)-10)*6,120263:(10+10)*(6/5),73117:(9-7)*(7+5),92191:((7-5)*7)+10,67507:((7-5)*8)+8,81719:((7+5)-9)*8,103037:(10-8)*(7+5),124729:((10-7)*5)+9,157267:((7/5)*10)+10,75449:(8*5)-(8+8),91333:(9*8)/(8-5),115159:((8+5)-10)*8,212773:(10+10)+(9-5),28561:(6+6)+(6+6),41743:(8-6)*(6+6),50531:(6*6)/(9/6),63713:(10*6)-(6*6),66079:(9-7)*(6+6),83317:((10-7)*6)+6,61009:(8*6)/(8-6),73853:((6+6)-9)*8,93119:(10-8)*(6+6),112723:((9-6)*10)-6,108953:((7+7)-10)*6,96577:(8*6)/(9-7),121771:((7+6)-10)*8,116909:(7*6)-(9+9),185861:((10-7)*10)-6,89167:((8-6)*8)+8,107939:(9*8)-(8*6),136097:(8*6)/(10-8),130663:(9+9)*(8/6),164749:((10-8)*9)+6,199433:((9/6)*10)+9,317057:(10+10)+(10-6),192763:((9-7)*7)+10,141151:((9-7)*8)+8,177973:(10*8)-(8*7),215441:(9*8)/(10-7),271643:((10-8)*7)+10,198911:((10-8)*8)+8', ',') AS kv
)
SELECT c.id, c1, c2, c3, c4, result
FROM poker24.cards c LEFT JOIN a a ON a.i =
( CASE c1 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
* CASE c2 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
* CASE c3 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
* CASE c4 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END);

The uncompressed SQL is 10,896 characters. Converting repeated logic into an inline function or using hexadecimal keys can reduce it, but the contest rules disallow stored procedures, so the large lookup string is the main compression target.

Compressed Variant

Pigsty provides the pgsql-gzip extension used by this historical solution:

CREATE EXTENSION IF NOT EXISTS gzip;

Compressing the lookup table reduces its 10,018 characters to 7,796 and the complete statement to 8,796 characters, within the contest limit:

WITH a(i, result) AS (SELECT (split_part(kv, ':', 1))::INTEGER AS i, split_part(kv, ':', 2) AS result
FROM regexp_split_to_table(encode(gunzip('\x1F8B08000000000000034D5A5B722B2B0CDC4EE278CABC24E0EE7F6157DD2DF0A9CA47860109F468B518576BFFFDFCD4BFFA1B7FAFF5AEE6FFFDF8ABFDBE38F86E65FCF733F1EEA7F1B92DCC7FC5FC86F96DC6FCFDDCF77DC4FB5AFEAE803ACA7F3FE3D5AFC016CF46819DCF5ECE06FCF7D54340390A269F573CAF58FFF75343CD7B60FEFEBBF20CEF6B79F8840575FB11387E5FE3DDCBDDB1F1D9074E38AE409C603E9C81F7D6C3220B6BA5C0C738271C98BFEAB1D8AB96D0F11E2B26D8CB7E25E36D3326D811E8EF09934C2897C0D55DEFB9E1F5766CC0717ABDDF6BE1C4FEFB490B7E4FF4DA61A538E1BC5B98E1B71246C62635B27EFFC28DCD61F576DC5277C86CF48AD1EA1D46F8BBEF7BF1F37E3E742334B4E7B87954C8C7D4BFFDFB6A6F6B8D56FF2AB07043A742B9B596B3A0D3EA9D6EEF57E12E474187910CF327DDCCF736D3ED2F4E7A3BE6EF787EF47ECD747B7BC9ED6DC70E07DDC609C3EF09E8761B2EB7A7C0891385DBF180F713161AE779BDB733B0290CEF6BAB8823A84BBF4FD8587EA7C4658B7E958470538591E4782C0B113634E3251DD524136EB3B06CB809BBAA25ECF81713B1A65CCB4744C0F9BD295EB5B118182BD4F81908A9738FB353C64B6BB24586EC136B26FC1FF69EBFA1E4D3427167D041EFCC00C1F935808DB46DF7FC0ABA5661228D30E8424DC39A15912B2733AC7E1692A7690DC38461C030E978E6BFF05C7F9BDD30E93099EBFD73D061D90D13BEDF7CBF70B2088D487EC6E17EAE823A2C03A53F0A94B1AF48E2C3442419F1802B7644A247EAC58ACFF865FA51E7083F4B246399FF635518DC2B95724B10D94A97D2F1DD06469C1B67025606B082A3D3BE056AECEC33AC6952F33AC1D1B991080E248184302B041D12C2B49B6727E1FAC13CD2CE39B0EFF1151C9DE7971A05475B3C306D2830683DA56684F5CD037F3883C9B6FB95AAE0E8B4898CB47E9F40903ECB090EBACE98F28B42C2541869FB8ADD4C7AE7DEA29CC8BFFBBD46A50DFE908232AD2FC4D8486F44A296B98E4387D26E22D85D339E98E1085C795433161304FFCBA38D991A1E1D890E6D8D763DAA35BEC75163726069089C1F8BB0E18023BBA54633365277518629FA09B35022178F819DA51AADE97E8FE7F04E2F5BC0351266FA4062FA1900562F41D7489F5B8345A4462E1E651044C67520017DCA1E1062638E3383BEB00293AC2335CA56C5F1E45016C6DDBB6AFF86E555B9E61C5F77D16ECD3DCBE3C7428E45580B99ED44B599A0D78E9966214C86590C767A6A042D47EC75AC32E8410961CCDAE8DAAEA599DC6084B08A656A2C568C10EA574F2D82564B4B2E2FEDEC640A8D170DA7625FB868D38758A240DF5E153B76F0B85555CBBF75B3BF3A6CB5692A8D0C4F5371485169A57DADC770189DD8EECF39B88FD612AEFCB302AE264D1EEA3D0FBE97A4F09CFE524D9185FDB8BFB655E5BBC85E664A78733158534E1CA376D878F3149EA0D614AE7CE6A43E99393C8594CDAED4D110ADD869FA4D65D21F1C489B9CDF2D316D17D56964B0C26E7998DA16EB585A561E8A3AEE67032A5CCDE7BAB2B736C0F891ECA56CF6E2E7702BF158E1FCF05987B3C371409542FD06E5B8CF6D4F066523696A91549B45B6FD3E7CB6DA61D13BDF5B8DF79B9363C97BAE7E8BBF04363BD592CFBD1AEB78CB6A39B6A542088DEAB9F8FED392544DBFCF53D5D30E976E0F0E507022554B9DA81713E0F65F4A0D44AA4446A918C1C3FA413DAE58751F369D22673DA02791955621B754B51C631F663164C6362FE8694D8165935A7D1AE3738A39C513498FC356533CE94521AB9209586E3CC287DE884D89B28608CCB0343758B3C101FE81439C7AF7A2C7720A9853A3CBB82D964FDF10823592DAFBFE3ACD6181686830653EB27A28DE6526636B02E8A885B4F2E74CE90D369191082221B51F232162E0EA9D8CFB8F3CEDEDA57484CF73CF33CDF7172F1431D3588515111101CD8E0D220AFA7BEBFD7322266AE51D60C8D4D1EC10F14E07CF764442C40E1A8825494B901DEFD9EF0C55E46A5728B97820891D329E4211B960184F13DBDE08ED7106A2220FC4FE8E25411F1012BD8CAFDA8C234C51D4506AAB98624708980DC25BF41181110985AD826FA651FBDC76109F6719DC993D62294C4AFB1E4F15996929A9CEAD4D66D19289509DC63211C40C2373B38BC9D2D32175722793030B9B5FC9B11A1A5D180DA0F9920566DF269EF6A7008CA2879DACA3276AB4592A7E696035B70B9872D62606102F39504B297601BBD3B24165840B4FBFC953DA26A968C9A38305CF135BA259BB5EEC1822A37B1F4E81D1779BBB1F4244170683A827A6296334EFA925BBAE60664A6326FA1FFA7BE4814ABF846CE089A8F560EE74C2C9C327929B0E249697B9C47D2B73C6C193A066FB506B0971E8D5E60916D1BBCDD3A77386C771CE5E49ADE7AEF37A597A8A0B6026512AE09498AF1AB160C5D5603495C6F14A8CBE269899E7B41247D878859E998CAF65AD36805DFA59D9516BD9C7D11A19A51CE0FD23D6441EBA53F407C6A6CD83E74114EC9D91E94B752EF89B2E0756277A21A3B28D2DD60E5E8720B03CB30BC7EA636783EF49B6941391BD953CD6D24B51C8A5474B426C5335B28C86C8AC6D9DBE9EB70E1467D565519CA25FBBF4C3D9360FEE2D8192CBB24AB13873129120CA54AB871158E28B0AF4C367A2522B74D7633707A3EC18677DEC42466CA92A98FE78B716C4B2321388172469DE7BB2AD2C70959E104753718A568E82254679695BA5C5D36651D2585D93C7B1A6B52CAFF32BA92052573239FABD0C041936F76C9E2CD8960ACE226DC4C98A7765A79F921AA5AE9F4DB23645259BFB9F22C48A587D4C9C2EF91E31B4525F58693288665877C094EB61E5947159F505798D5571146554B23BA46574ABF51E4F7B6845C1B63EA79C865118FC0F8B295B5818E166084B6C2F159E53866864952A23109258BB03B2E6F77C50812BC8B6EFB658D6030C582450377931B1E6797EBA4AE064B1D24D466750DAB92100E50B0F343B6DB8064E31978C054693E81E558277A594714A31D65452C841A9836AB636162B548B1B2BBE185C7F3A596CD6624AC5D51D29405E66C48C519A65779C7A3990953756057A4AA89D7D447723AADA9995FDEDBD7D0B2D66CC2D1A419CA14506F71E29D2F3F2C7AC7D0B2DB6EAF56B558745E6A045982194B147FBA7D0524F4B034C529EF95E056B149C5A33E765C530FF7BE3782B78C7C37A0C90D9ED14F47EFA9EF94F61A5E93B356565E588FB3F54090A22F15858072F495110029938F01CFFF4BA2E57C268B4F76E790120FF0C9209CA808FA2BC794FAEF4C8E9D1D87ECB77D6D57E3D46A9C6A26F472AFAE561AAA21939933467E93A03C7596C27E4D3CD98AE55EC82D0C745017C76908F03CB496B5412199065B865C3AAF3D45EB7DDBAE49A54C5B106FB7B8C64AB327524F415DDC5B2E6151D54D52ECE0FBAC01A095ED64525C492363EABADB49A9E0B491FE6C4E85FCF7167D1ADB91D224296178C68D9211EA64DB2435B7995C1A0A041703BC0EB8F60E0DC9088861635D2658941F0A3EF5C76A886E6F818768097825B99DA214D2D5D93FDDF7A54B9092956EC54072D9B346CC2A7966DB58959F83069A84FE4E1210E2D8D5A4FD0D3CDCB49F7F5F5FD56CEA7F91F0DB39D289B3D2A7C9DF7D9A3673C7B465EF5C2C0F2BF93D655E6DF59F9B8259E4472F24E7B91AA8724CFDE255AF87D535BCBC49059C16492DED847D0D0E75EBB332235A48BED356837DE751179C2236937C6B23E5CF575AD040DE4F45FF461BEDB70C8EEA8FC6446D0378C16C8F248AF0C5A000F223181C16AD57F6600177BFFBACBF1DC394BF17573426DE4AC8A93D8652E1BDB6F96D04DE6849C7D427BF2DB889CA922C784EB83811AD6ECA4AAB867549A90212C667B984E4043F5BF9FC0ECD2D482B0A86252301DFFF617EB21F6AFCC7815554E2BEBDB98D076D3D557610813913C3E339D22C268CF8E68AD2879BCFF0D428F1B6E12E8CF48481DBA98C14B3526B6FAE5F65CE256E7C13A0ECCC59B818D29EC69F71EA40109B20350D7EEA50574BD27E9B5E98924AFFAA1BC434857DAA8071FA8A79A3896EE3AD53DB75AFAF922E9409E1AF179B9A1962D32ACCC7E0D459DA86CA10723261896F1A24520BA2828C0E8B245AE429BFD658B12347D5DB6A84921BB9F02B33812FDD3BE7738943D6A03E58289909FD1B787D13ACC2A13193750C99F03664294E96B565793980089BEB2A05318678470B02EEBC65D1453A85FF660DC76273775DA16E51324B7DEC65086DCE477524FA4694165FA411ACA09A813B92366485B14F6DB514C998D974B2B8175904CC2FB0A2A7DBE99DB753164B7979D732B4416430479EEE310551D7FB421FE4E60A5B583B9765EFD7CF6F9B81925621F3AA5F2149CDBF296E9EA0B7ECB16D5F3BCD19287FD19FA7EACD4DA00795E89B51899F2244C96DF8C464FF2CC651F4640A3DF126B69385E8D499940CCD8B8EA0A83ABC658ECEF293A3F1C4511AD6788E8DBF7F47970867BB452D90892469C8FF0515A0FCE70127A6D85F23EEBA21EF6FAC5198EC559362D90C81A8C6BE97E0E6751530EE853DF3E12FBACF1D6411561D2DE66EAED3FDA373AE75826D1F0943E3277E529530786E075CB54C41F0CC36918BC22DD44F2B05CD305E7C80E6D86A5FAEDD01818D120C2E7E3288CD63CE25297CC439889BB81E037FD9F1686991021B5BEADD54E988195335D238A70975FFA194166A1E4100A32EF8C3F18D16D403C648CF9FCCA41A44568AC75638CAB7A94A51B3E1AD985572394C3F0B1A85CDFCE1A691C15D6D715BD3E0B2568CD8B310899B707EBAE890D61279CC3472917AB612A3401E52E63C880734EAFDF31F806F8E8CA58FFB83EBF053E010C325FB073EB7215110DF912190CBF6CDC17B22B7A5BD7E598705E9FB0A261906805628C38BF30ACFC4E8365C66B0A610853D1A26F54965986A647B37BAEC211291EC58BF76C50FCC141C228D37CCCECE5054FDBF2EE0DCB1029B80C2ECD6FA420658D6D409D87407053BBD57D814D491C7D4EA29F6512AFE874944296F11B55ADA895660053540DF069AA1A90AFCB249B8D1741F30019AFC01865795F13A5298A6BEF372349522BF8C93E9650F047533DE73FC1BFC96697F9F77EE60FCCADCED18FE539127413D0E1E4E03B7C1F3C6656E5B2BC8A21672AEFBC6A71FCD687252FCFC360F0CAE0139B8786302913922B649B2894F59FDB1748AAB1F3D68FCB92F296E04DFD40959C164932EFBD2476020231F9E903317A41C0792332B979302A743DCBEBDDAB34ACCD789725F4CBA238229116B84435E8BCCABE3AB969945FF77E9AA80583E11A68A473D7FD2953507BD5B28472FCCE2178DE3F772C2CBDE8D3A6EBFCF3FBB3A7CA4B438D697B28A9728BF637D9F6F0E250B1218A1B8D8FE715143693F282879EB45C12F83F3248B10120270000'), 'escape'), ',') AS kv)
SELECT c.id, c1, c2, c3, c4, result FROM poker24.cards c LEFT JOIN a a ON a.i =
(CASE c1 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
*CASE c2 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
*CASE c3 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
*CASE c4 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END);

Result

On a local M1 MacBook Pro, single-core execution took about 0.58 seconds, slightly faster than the winning 0.67-second result. The contest PostgreSQL instance did not provide the gzip extension, so this compressed form was not submitted there.

 Merge Right Join  (cost=118104.17..768224.17 rows=5000000 width=68) (actual time=457.485..555.265 rows=1000000 loops=1)
   Merge Cond: (((split_part(kv.kv, ':'::text, 1))::integer) = ((((CASE c.c1 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END * CASE c.c2 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c3 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c4 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END)))
   ->  Sort  (cost=62.33..64.83 rows=1000 width=64) (actual time=0.851..0.872 rows=566 loops=1)
         Sort Key: ((split_part(kv.kv, ':'::text, 1))::integer)
         Sort Method: quicksort  Memory: 59kB
         ->  Function Scan on regexp_split_to_table kv  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.491..0.654 rows=566 loops=1)
   ->  Sort  (cost=118041.84..120541.84 rows=1000000 width=36) (actual time=456.629..494.693 rows=1000000 loops=1)
         Sort Key: ((((CASE c.c1 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END * CASE c.c2 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c3 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c4 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END))
         Sort Method: external sort  Disk: 56760kB
         ->  Seq Scan on cards c  (cost=0.00..18384.00 rows=1000000 width=36) (actual time=0.028..213.760 rows=1000000 loops=1)
 Planning Time: 0.363 ms
 Execution Time: 581.782 ms

This demonstrates a one-statement PostgreSQL solution to the 24-point card game. Parallel execution could improve it further; a PostgreSQL-specific alternative would be a C extension exposing the lookup as a function, although that would fall outside the contest rules.