postgres case vs. hstore vs. istore

test.sql

-- sample data
set max_parallel_workers_per_gather=1;

CREATE TABLE data AS
SELECT n, chr(n) as g, i FROM generate_series(97,122) n, generate_series(1,1e6) i ORDER BY i;

SELECT CASE g
    WHEN 'a' THEN 'one'
    WHEN 'b' THEN 'one'
    WHEN 'c' THEN 'one'
    WHEN 'd' THEN 'one'
    WHEN 'e' THEN 'one'
    WHEN 'f' THEN 'one'
    WHEN 'g' THEN 'one'
    WHEN 'h' THEN 'one'
    WHEN 'i' THEN 'one'
    WHEN 'j' THEN 'two'
    WHEN 'k' THEN 'two'
    WHEN 'l' THEN 'two'
    WHEN 'm' THEN 'two'
    WHEN 'n' THEN 'two'
    WHEN 'o' THEN 'two'
    WHEN 'p' THEN 'two'
    WHEN 'q' THEN 'two'
    WHEN 'r' THEN 'two'
    WHEN 's' THEN 'three'
    WHEN 't' THEN 'three'
    WHEN 'u' THEN 'three'
    WHEN 'v' THEN 'three'
    WHEN 'w' THEN 'three'
    WHEN 'x' THEN 'three'
    WHEN 'y' THEN 'three'
    WHEN 'z' THEN 'three'
END AS group_case_string, SUM(i) FROM data GROUP BY 1;


SELECT
'a => one, b => one, c => one, d => one, e => one, f => one, g => one, h => one, i => one, j => two, k => two, l => two, m => two, n => two, o => two, p => two, q => two, r => two, s => three, t => three, u => three, v => three, w => three, x => three, y => three, z => three'::hstore -> g
AS group_hstore,
SUM(i) FROM data GROUP BY 1;


SELECT CASE n
    WHEN 97 THEN 1
    WHEN 98 THEN 1
    WHEN 99 THEN 1
    WHEN 100 THEN 1
    WHEN 101 THEN 1
    WHEN 102 THEN 1
    WHEN 103 THEN 1
    WHEN 104 THEN 1
    WHEN 105 THEN 1
    WHEN 106 THEN 2
    WHEN 107 THEN 2
    WHEN 108 THEN 2
    WHEN 109 THEN 2
    WHEN 110 THEN 2
    WHEN 111 THEN 2
    WHEN 112 THEN 2
    WHEN 113 THEN 2
    WHEN 114 THEN 2
    WHEN 115 THEN 3
    WHEN 116 THEN 3
    WHEN 117 THEN 3
    WHEN 118 THEN 3
    WHEN 119 THEN 3
    WHEN 120 THEN 3
    WHEN 121 THEN 3
    WHEN 122 THEN 3
END AS group_case_integer, SUM(i) FROM data GROUP BY 1;


SELECT
'97 => 1, 98 => 1, 99 => 1, 100 => 1, 101 => 1, 102 => 1, 103 => 1, 104 => 1, 105 => 1, 106 => 2, 107 => 2, 108 => 2, 109 => 2, 110 => 2, 111 => 2, 112 => 2, 113 => 2, 114 => 2, 115 => 3, 116 => 3, 117 => 3, 118 => 3, 119 => 3, 120 => 3, 121 => 3, 122 => 3'::istore -> n
AS group_istore,
SUM(i) FROM data GROUP BY 1;

test_results.sql

 group_case_string |      sum
-------------------+---------------
 one               | 4500004500000
 three             | 4000004000000
 two               | 4500004500000
(3 rows)

Time: 5688,750 ms (00:05,689)


 group_hstore |      sum
--------------+---------------
 one          | 4500004500000
 three        | 4000004000000
 two          | 4500004500000
(3 rows)

Time: 3784,550 ms (00:03,785)


 group_case_integer |      sum
--------------------+---------------
                  1 | 4500004500000
                  2 | 4500004500000
                  3 | 4000004000000
(3 rows)

Time: 3685,760 ms (00:03,686)


 group_istore |      sum
--------------+---------------
            1 | 4500004500000
            2 | 4500004500000
            3 | 4000004000000
(3 rows)

Time: 2964,931 ms (00:02,965)