-- 40 lookups by a 64-character text key in one statement (40 index scans): a dictionary of a million strings over the cache is searched in the stream, and was parsed again for every scan once tested often
-- label: `sum(amount)` of 40 `WHERE ref = <a 64-character key>` scans in one statement, no btree on `ref`, `point_tid_columns = 'ref'`
-- ordered: yes
-- set: sift.enable_col_scan = on
--@ setup
ALTER TABLE orders ADD COLUMN ref text;
UPDATE orders SET ref = md5(id::text) || md5((id * 7)::text);
ALTER TABLE orders DROP CONSTRAINT orders_pkey;
DROP INDEX orders_col;
CREATE INDEX orders_col ON orders USING sift_col (id sift.col_int4_ops, ref sift.col_text_ops, amount sift.col_numeric_ops) WITH (point_tid_columns = 'ref')
--@ pg
SELECT count(*) AS n, sum(amount) AS revenue FROM (SELECT amount FROM orders WHERE ref = md5('4999') || md5(('4999'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('9998') || md5(('9998'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('14997') || md5(('14997'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('19996') || md5(('19996'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('24995') || md5(('24995'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('29994') || md5(('29994'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('34993') || md5(('34993'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('39992') || md5(('39992'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('44991') || md5(('44991'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('49990') || md5(('49990'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('54989') || md5(('54989'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('59988') || md5(('59988'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('64987') || md5(('64987'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('69986') || md5(('69986'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('74985') || md5(('74985'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('79984') || md5(('79984'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('84983') || md5(('84983'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('89982') || md5(('89982'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('94981') || md5(('94981'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('99980') || md5(('99980'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('104979') || md5(('104979'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('109978') || md5(('109978'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('114977') || md5(('114977'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('119976') || md5(('119976'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('124975') || md5(('124975'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('129974') || md5(('129974'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('134973') || md5(('134973'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('139972') || md5(('139972'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('144971') || md5(('144971'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('149970') || md5(('149970'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('154969') || md5(('154969'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('159968') || md5(('159968'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('164967') || md5(('164967'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('169966') || md5(('169966'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('174965') || md5(('174965'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('179964') || md5(('179964'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('184963') || md5(('184963'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('189962') || md5(('189962'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('194961') || md5(('194961'::int * 7)::text) UNION ALL SELECT amount FROM orders WHERE ref = md5('199960') || md5(('199960'::int * 7)::text)) s