-- sift.facet_counts / sift.facet_sql (docs/FIELDS-FACETS.md): the facets of a search, several in one statement, over a table with a sift -- (BM25) index and a sift_col index. Every case runs three times: the facet function with the column scan forced (the plans that are not -- SiftAgg disabled), the facet function with the planner's own plan (sift.enable_agg off), and a hand-written GROUP BY of the same -- snapshot as the oracle; the three results are compared as sets of rows, after the build and after each kind of write. CREATE EXTENSION pg_sift; CREATE EXTENSION pg_sift_helpers; SET sift.async_maintenance = off; CREATE TABLE fc (id serial PRIMARY KEY, title text, brand text, category int, price numeric(10,2), qty int, added date, ts timestamp, active bool, tags text[], pad text DEFAULT repeat('x', 700)); ALTER TABLE fc ALTER COLUMN pad SET STORAGE PLAIN; INSERT INTO fc (title, brand, category, price, qty, added, ts, active, tags) SELECT (ARRAY['wireless','wired','portable','compact','classic'])[1 + i % 5] || ' ' || (ARRAY['headphones','speaker','charger','cable'])[1 + i % 4] || ' w' || i % 97, CASE WHEN i % 11 = 0 THEN NULL ELSE (ARRAY['acme','zeta','omni','nova','Ünï','x'])[1 + (hashint4(i) & 2147483647) % 6] END, (hashint4(i + 3) & 2147483647) % 9, CASE WHEN i % 17 = 0 THEN NULL ELSE round(((hashint4(i + 5) & 2147483647) % 60000 / 100.0)::numeric, 2) END, (hashint4(i + 7) & 2147483647) % 13 * 10, date '2024-01-01' + (hashint4(i + 9) & 2147483647) % 500, timestamp '2024-01-01' + ((hashint4(i + 9) & 2147483647) % 500) * interval '1 day' + (i % 24) * interval '1 hour', CASE WHEN i % 13 = 0 THEN NULL ELSE i % 3 = 0 END, CASE WHEN i % 19 = 0 THEN NULL ELSE ARRAY[(ARRAY['red','green','blue'])[1 + i % 3], (ARRAY['s','m','l'])[1 + i % 3], (ARRAY['red','green','blue'])[1 + i % 3]] END FROM generate_series(1, 12000) i; CREATE INDEX fc_bm ON fc USING sift (title); CREATE INDEX fc_col ON fc USING sift_col (brand, category, price, qty, added, ts, active) WITH (write_buffer_limit = 256); NOTICE: sift_col index "fc_col": 1 automatic choice (rollup_columns), 1 advice (track_changes) DETAIL: advice: track_changes = the index lacks the primary key column; rollup_columns = brand, active HINT: Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('fc_col'); VACUUM (ANALYZE) fc; -- the plans that are not SiftAgg are disabled for the pushed run; the planner's own choice with sift.enable_agg off is the other CREATE FUNCTION fc_mode(pushed bool) RETURNS void LANGUAGE sql AS $$ SELECT sift_helpers.agg_mode_scan_and_count_off(pushed) $$; -- the oracle of a terms facet: a GROUP BY of the column, the first sz values by count then value, the rest as one row, NULL as missing CREATE FUNCTION fc_terms(name text, col text, wh text, sz int DEFAULT NULL, miss bool DEFAULT false) RETURNS text LANGUAGE sql AS $$ SELECT format($f$ SELECT * FROM (WITH g AS (SELECT (%1$s)::text v, count(*) c FROM fc WHERE %3$s GROUP BY %1$s), r AS (SELECT v, c, row_number() OVER (ORDER BY c DESC, (v COLLATE "C")) rn FROM g WHERE v IS NOT NULL) SELECT %2$L, 'value', v, NULL, NULL, c FROM r WHERE %4$s IS NULL OR rn <= %4$s UNION ALL SELECT %2$L, 'other', NULL, count(*)::text, NULL, sum(c) FROM r WHERE rn > %4$s HAVING count(*) > 0 UNION ALL SELECT %2$L, 'missing', NULL, NULL, NULL, c FROM g WHERE v IS NULL AND %5$L::bool) t $f$, col, name, wh, coalesce(sz::text, 'NULL'), miss) $$; -- same / DIFFERENT for the function with the column scan, the function with the planner's plan against the oracle, and how many scans of the -- function's plan read the match set ('searches', a SiftAgg Search line each) CREATE FUNCTION fc_check(tbl regclass, q text, facets jsonb, filt text, ref text, hitwh text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE n bigint; m bigint; plan text := ''; line text; sql text := sift.facet_sql(tbl, 'title', q, facets, filt, true); BEGIN DROP TABLE IF EXISTS fc_a, fc_b, fc_c; PERFORM fc_mode(true); FOR line IN EXECUTE 'EXPLAIN (COSTS OFF) ' || sql LOOP plan := plan || line || E'\n'; END LOOP; CREATE TEMP TABLE fc_a AS SELECT row_to_json(x)::text AS r FROM sift.facet_counts(tbl, 'title', q, facets, filt, true) x; PERFORM fc_mode(false); CREATE TEMP TABLE fc_b AS SELECT row_to_json(x)::text AS r FROM sift.facet_counts(tbl, 'title', q, facets, filt, true) x; EXECUTE replace(replace('CREATE TEMP TABLE fc_c AS SELECT row_to_json(x)::text AS r FROM (' || ref || ' UNION ALL SELECT NULL, ''hits'', NULL, NULL, NULL, count(*) FROM fc WHERE ' || hitwh || ') x(facet, kind, value, lo, hi, count)', 'FROM fc ', 'FROM ' || tbl || ' '), 'FROM fc,', 'FROM ' || tbl || ','); SELECT count(*) INTO n FROM ((SELECT r FROM fc_a EXCEPT ALL SELECT r FROM fc_c) UNION ALL (SELECT r FROM fc_c EXCEPT ALL SELECT r FROM fc_a)) d; SELECT count(*) INTO m FROM ((SELECT r FROM fc_b EXCEPT ALL SELECT r FROM fc_c) UNION ALL (SELECT r FROM fc_c EXCEPT ALL SELECT r FROM fc_b)) d; RETURN CASE WHEN n = 0 THEN 'same' ELSE 'DIFFERENT' END || '/' || CASE WHEN m = 0 THEN 'same' ELSE 'DIFFERENT' END || ' ' || (SELECT count(*) FROM regexp_matches(plan, 'Custom Scan \(SiftAgg\)', 'g')) || ' SiftAgg, ' || (SELECT count(*) FROM regexp_matches(plan, 'Search: ', 'g')) || ' searching, ' || (SELECT count(*) FROM fc_c) || ' rows'; END $$; -- tag, query, facets, filter, oracle ($W is the where of the search and the filter), where of the hits row CREATE TABLE fc_q (tag text PRIMARY KEY, q text, facets jsonb, filt text, ref text, hitwh text); INSERT INTO fc_q VALUES ('terms_text', 'wireless', '["brand"]', NULL, fc_terms('brand', 'brand', $$title @@> 'wireless'$$), $$title @@> 'wireless'$$), ('terms_top', 'wired OR compact', '[{"expr": "category", "size": 3}]', NULL, fc_terms('category', 'category', $$title @@> 'wired OR compact'$$, 3), $$title @@> 'wired OR compact'$$), ('terms_bool', 'portable', '[{"expr": "active", "missing": true}]', NULL, fc_terms('active', 'active', $$title @@> 'portable'$$, NULL, true), $$title @@> 'portable'$$), ('terms_other', 'headphones', '[{"name": "b", "expr": "brand", "size": 2, "missing": true}]', NULL, fc_terms('b', 'brand', $$title @@> 'headphones'$$, 2, true), $$title @@> 'headphones'$$), ('terms_filter', 'speaker OR cable', '[{"expr": "category"}]', 'price > 100 AND active', fc_terms('category', 'category', $$title @@> 'speaker OR cable' AND (price > 100 AND active)$$), $$title @@> 'speaker OR cable' AND (price > 100 AND active)$$), ('no_search', NULL, '[{"expr": "brand"}]', 'qty > 50', fc_terms('brand', 'brand', 'qty > 50'), 'qty > 50'), ('no_match', 'zzzzzz', '["brand", {"expr": "price", "edges": [100]}]', NULL, $$SELECT 'price', 'bucket', '[,100)', NULL, '100', 0 UNION ALL SELECT 'price', 'bucket', '[100,)', '100', NULL, 0$$, $$title @@> 'zzzzzz'$$), ('range', 'wireless', '[{"name": "p", "expr": "price", "edges": [100, 300]}]', NULL, $$SELECT 'p', 'bucket', '[,100)', NULL, '100', count(*) FROM fc WHERE title @@> 'wireless' AND price < 100 UNION ALL SELECT 'p', 'bucket', '[100,300)', '100', '300', count(*) FROM fc WHERE title @@> 'wireless' AND price >= 100 AND price < 300 UNION ALL SELECT 'p', 'bucket', '[300,)', '300', NULL, count(*) FROM fc WHERE title @@> 'wireless' AND price >= 300$$, $$title @@> 'wireless'$$), ('range_dates', 'wireless OR wired', '[{"name": "d", "expr": "added", "edges": ["2024-06-01", "2025-01-01"]}]', NULL, $$SELECT 'd', 'bucket', '[,2024-06-01)', NULL, '2024-06-01', count(*) FROM fc WHERE title @@> 'wireless OR wired' AND added < '2024-06-01' UNION ALL SELECT 'd', 'bucket', '[2024-06-01,2025-01-01)', '2024-06-01', '2025-01-01', count(*) FROM fc WHERE title @@> 'wireless OR wired' AND added >= '2024-06-01' AND added < '2025-01-01' UNION ALL SELECT 'd', 'bucket', '[2025-01-01,)', '2025-01-01', NULL, count(*) FROM fc WHERE title @@> 'wireless OR wired' AND added >= '2025-01-01'$$, $$title @@> 'wireless OR wired'$$), ('histogram', 'classic', '[{"name": "q", "expr": "qty", "interval": 25, "offset": 5}]', NULL, $$SELECT 'q', 'bucket', lo::text, lo::text, (lo + 25)::text, count(*) FROM (SELECT floor((qty::numeric - 5) / 25) * 25 + 5 lo FROM fc WHERE title @@> 'classic') g WHERE lo IS NOT NULL GROUP BY lo$$, $$title @@> 'classic'$$), ('histogram_fill', 'classic OR compact', '[{"name": "q", "expr": "qty", "interval": 3, "fill": true}]', NULL, $$SELECT 'q', 'bucket', (k * 3)::text, (k * 3)::text, (k * 3 + 3)::text, coalesce(c, 0) FROM generate_series(0, (SELECT max(floor(qty / 3.0)) FROM fc WHERE title @@> 'classic OR compact')::int) k LEFT JOIN (SELECT floor(qty / 3.0)::int k, count(*) c FROM fc WHERE title @@> 'classic OR compact' GROUP BY 1) g USING (k)$$, $$title @@> 'classic OR compact'$$), ('histogram_float', 'compact', '[{"name": "p", "expr": "price::float8", "interval": 100.5}]', NULL, $$SELECT 'p', 'bucket', (k * 100.5)::text, (k * 100.5)::text, (k * 100.5 + 100.5)::text, count(*) FROM (SELECT floor(price::float8::numeric / 100.5) k FROM fc WHERE title @@> 'compact') g WHERE k IS NOT NULL GROUP BY k$$, $$title @@> 'compact'$$), ('month', 'wireless', '[{"name": "m", "expr": "added", "unit": "month"}]', NULL, $$SELECT 'm', 'bucket', k::text, k::text, (k + interval '1 month')::text, count(*) FROM (SELECT date_trunc('month', added::timestamp) k FROM fc WHERE title @@> 'wireless') g GROUP BY k$$, $$title @@> 'wireless'$$), ('week_ts', 'wired', '[{"name": "w", "expr": "ts", "unit": "week"}]', NULL, $$SELECT 'w', 'bucket', k::text, k::text, (k + interval '1 week')::text, count(*) FROM (SELECT date_trunc('week', ts) k FROM fc WHERE title @@> 'wired') g GROUP BY k$$, $$title @@> 'wired'$$), ('quarter_fill', 'classic', '[{"name": "q", "expr": "added", "unit": "quarter", "fill": true}]', NULL, $$SELECT 'q', 'bucket', k::text, k::text, (k + interval '3 months')::text, coalesce(c, 0) FROM generate_series((SELECT min(date_trunc('quarter', added::timestamp)) FROM fc WHERE title @@> 'classic'), (SELECT max(date_trunc('quarter', added::timestamp)) FROM fc WHERE title @@> 'classic'), interval '3 months') k LEFT JOIN (SELECT date_trunc('quarter', added::timestamp) k, count(*) c FROM fc WHERE title @@> 'classic' GROUP BY 1) g USING (k)$$, $$title @@> 'classic'$$), ('array', 'wireless', '[{"name": "tags", "expr": "tags", "array": true}]', NULL, $$SELECT 'tags', 'value', t, NULL, NULL, count(*) FROM (SELECT DISTINCT id, t FROM fc, unnest(tags) t WHERE title @@> 'wireless') g GROUP BY t$$, $$title @@> 'wireless'$$), ('many', 'wired OR portable', '["brand", "category", {"expr": "price", "edges": [200]}, {"expr": "added", "unit": "year"}, "qty", "active"]', NULL, fc_terms('brand', 'brand', $$title @@> 'wired OR portable'$$) || ' UNION ALL ' || fc_terms('category', 'category', $$title @@> 'wired OR portable'$$) || $$ UNION ALL SELECT 'price', 'bucket', '[,200)', NULL, '200', count(*) FROM fc WHERE title @@> 'wired OR portable' AND price < 200 UNION ALL SELECT 'price', 'bucket', '[200,)', '200', NULL, count(*) FROM fc WHERE title @@> 'wired OR portable' AND price >= 200 UNION ALL SELECT 'added', 'bucket', k::text, k::text, (k + interval '1 year')::text, count(*) FROM (SELECT date_trunc('year', added::timestamp) k FROM fc WHERE title @@> 'wired OR portable') g GROUP BY k$$ || ' UNION ALL ' || fc_terms('qty', 'qty', $$title @@> 'wired OR portable'$$) || ' UNION ALL ' || fc_terms('active', 'active', $$title @@> 'wired OR portable'$$), $$title @@> 'wired OR portable'$$), -- a facet leaves its own selection out of its counts and applies the selections of the others ('drill', 'wireless OR wired', $$[{"name": "brand", "expr": "brand", "selected": ["acme", "zeta"]}, {"name": "p", "expr": "price", "edges": [100, 300], "selected": [1]}, {"name": "category", "expr": "category"}]$$, 'active', fc_terms('brand', 'brand', $$title @@> 'wireless OR wired' AND (active) AND price >= 100 AND price < 300$$) || $$ UNION ALL SELECT 'p', 'bucket', '[,100)', NULL, '100', count(*) FROM fc WHERE title @@> 'wireless OR wired' AND (active) AND brand IN ('acme', 'zeta') AND price < 100 UNION ALL SELECT 'p', 'bucket', '[100,300)', '100', '300', count(*) FROM fc WHERE title @@> 'wireless OR wired' AND (active) AND brand IN ('acme', 'zeta') AND price >= 100 AND price < 300 UNION ALL SELECT 'p', 'bucket', '[300,)', '300', NULL, count(*) FROM fc WHERE title @@> 'wireless OR wired' AND (active) AND brand IN ('acme', 'zeta') AND price >= 300 UNION ALL $$ || fc_terms('category', 'category', $$title @@> 'wireless OR wired' AND (active) AND brand IN ('acme', 'zeta') AND price >= 100 AND price < 300$$), $$title @@> 'wireless OR wired' AND (active) AND brand IN ('acme', 'zeta') AND price >= 100 AND price < 300$$), ('drill_buckets', 'wireless', $$[{"name": "q", "expr": "qty", "interval": 25, "selected": [25, 50]}, {"name": "m", "expr": "added", "unit": "month", "selected": ["2024-03-01 00:00:00"]}, {"name": "t", "expr": "tags", "array": true, "selected": ["red"]}, {"name": "category", "expr": "category", "filter": "category > 2"}]$$, NULL, $$SELECT 'q', 'bucket', lo::text, lo::text, (lo + 25)::text, count(*) FROM (SELECT floor(qty::numeric / 25) * 25 lo FROM fc WHERE title @@> 'wireless' AND date_trunc('month', added::timestamp) = '2024-03-01' AND tags && ARRAY['red'] AND category > 2) g WHERE lo IS NOT NULL GROUP BY lo UNION ALL SELECT 'm', 'bucket', k::text, k::text, (k + interval '1 month')::text, count(*) FROM (SELECT date_trunc('month', added::timestamp) k FROM fc WHERE title @@> 'wireless' AND ((qty >= 25 AND qty < 50) OR (qty >= 50 AND qty < 75)) AND tags && ARRAY['red'] AND category > 2) g GROUP BY k UNION ALL SELECT 't', 'value', e, NULL, NULL, count(*) FROM (SELECT DISTINCT id, e FROM fc, unnest(tags) e WHERE title @@> 'wireless' AND ((qty >= 25 AND qty < 50) OR (qty >= 50 AND qty < 75)) AND date_trunc('month', added::timestamp) = '2024-03-01' AND category > 2) g GROUP BY e UNION ALL $$ || fc_terms('category', 'category', $$title @@> 'wireless' AND ((qty >= 25 AND qty < 50) OR (qty >= 50 AND qty < 75)) AND date_trunc('month', added::timestamp) = '2024-03-01' AND tags && ARRAY['red']$$), $$title @@> 'wireless' AND ((qty >= 25 AND qty < 50) OR (qty >= 50 AND qty < 75)) AND date_trunc('month', added::timestamp) = '2024-03-01' AND tags && ARRAY['red'] AND category > 2$$); INSERT INTO fc_q VALUES ('drill_dates', 'wireless', $$[{"name": "q", "expr": "qty", "interval": 25, "selected": [25, 50]}, {"name": "m", "expr": "added", "unit": "month", "selected": ["2024-03-01 00:00:00"]}, {"name": "category", "expr": "category"}]$$, NULL, $$SELECT 'q', 'bucket', lo::text, lo::text, (lo + 25)::text, count(*) FROM (SELECT floor(qty::numeric / 25) * 25 lo FROM fc WHERE title @@> 'wireless' AND date_trunc('month', added::timestamp) = '2024-03-01') g WHERE lo IS NOT NULL GROUP BY lo UNION ALL SELECT 'm', 'bucket', k::text, k::text, (k + interval '1 month')::text, count(*) FROM (SELECT date_trunc('month', added::timestamp) k FROM fc WHERE title @@> 'wireless' AND ((qty >= 25 AND qty < 50) OR (qty >= 50 AND qty < 75))) g GROUP BY k UNION ALL $$ || fc_terms('category', 'category', $$title @@> 'wireless' AND ((qty >= 25 AND qty < 50) OR (qty >= 50 AND qty < 75)) AND date_trunc('month', added::timestamp) = '2024-03-01'$$), $$title @@> 'wireless' AND ((qty >= 25 AND qty < 50) OR (qty >= 50 AND qty < 75)) AND date_trunc('month', added::timestamp) = '2024-03-01'$$); INSERT INTO fc_q VALUES ('range_missing', 'wireless OR wired', '[{"name": "p", "expr": "price", "edges": [100], "missing": true}, {"name": "q", "expr": "qty", "edges": [20, 60], "missing": true}]', NULL, $$SELECT 'p', 'bucket', '[,100)', NULL, '100', count(*) FROM fc WHERE title @@> 'wireless OR wired' AND price < 100 UNION ALL SELECT 'p', 'bucket', '[100,)', '100', NULL, count(*) FROM fc WHERE title @@> 'wireless OR wired' AND price >= 100 UNION ALL SELECT 'p', 'missing', NULL, NULL, NULL, count(*) FROM fc WHERE title @@> 'wireless OR wired' AND price IS NULL HAVING count(*) > 0 UNION ALL SELECT 'q', 'bucket', '[,20)', NULL, '20', count(*) FROM fc WHERE title @@> 'wireless OR wired' AND qty < 20 UNION ALL SELECT 'q', 'bucket', '[20,60)', '20', '60', count(*) FROM fc WHERE title @@> 'wireless OR wired' AND qty >= 20 AND qty < 60 UNION ALL SELECT 'q', 'bucket', '[60,)', '60', NULL, count(*) FROM fc WHERE title @@> 'wireless OR wired' AND qty >= 60 UNION ALL SELECT 'q', 'missing', NULL, NULL, NULL, count(*) FROM fc WHERE title @@> 'wireless OR wired' AND qty IS NULL HAVING count(*) > 0$$, $$title @@> 'wireless OR wired'$$); CREATE FUNCTION fc_stage(name text, tbl regclass DEFAULT 'fc') RETURNS TABLE (stage text, results text) LANGUAGE sql AS $$ SELECT name, string_agg(tag || '=' || fc_check(tbl, q, facets, filt, ref, hitwh), E'\n' ORDER BY tag) FROM fc_q $$; SELECT * FROM fc_stage('build'); NOTICE: table "fc_a" does not exist, skipping NOTICE: table "fc_b" does not exist, skipping NOTICE: table "fc_c" does not exist, skipping stage | results -------+---------------------------------------------------------- build | array=same/same 1 SiftAgg, 1 searching, 7 rows + | drill=same/same 4 SiftAgg, 4 searching, 19 rows + | drill_buckets=same/same 0 SiftAgg, 0 searching, 38 rows + | drill_dates=same/same 4 SiftAgg, 4 searching, 32 rows + | histogram=same/same 2 SiftAgg, 2 searching, 7 rows + | histogram_fill=same/same 2 SiftAgg, 2 searching, 42 rows+ | histogram_float=same/same 2 SiftAgg, 2 searching, 7 rows+ | many=same/same 7 SiftAgg, 7 searching, 35 rows + | month=same/same 2 SiftAgg, 2 searching, 18 rows + | no_match=same/same 3 SiftAgg, 3 searching, 3 rows + | no_search=same/same 2 SiftAgg, 0 searching, 7 rows + | quarter_fill=same/same 2 SiftAgg, 2 searching, 7 rows + | range=same/same 2 SiftAgg, 2 searching, 4 rows + | range_dates=same/same 2 SiftAgg, 2 searching, 4 rows + | range_missing=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_bool=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_filter=same/same 2 SiftAgg, 2 searching, 10 rows + | terms_other=same/same 2 SiftAgg, 2 searching, 5 rows + | terms_text=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_top=same/same 2 SiftAgg, 2 searching, 5 rows + | week_ts=same/same 2 SiftAgg, 2 searching, 73 rows (1 row) -- the order of the rows, the other row and the labels SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '[{"name": "c", "expr": "category", "size": 3, "order": "value desc", "missing": true}, {"expr": "price", "type": "range", "edges": [100, 300]}]'); facet | kind | value | lo | hi | count -------+--------+-----------+-----+-----+------- c | value | 8 | | | 268 c | value | 7 | | | 267 c | value | 6 | | | 279 c | other | | 6 | | 1586 price | bucket | [,100) | | 100 | 405 price | bucket | [100,300) | 100 | 300 | 734 price | bucket | [300,) | 300 | | 1120 (7 rows) SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '[{"name": "c", "expr": "category", "size": 2, "order": "count asc"}]'); facet | kind | value | lo | hi | count -------+-------+-------+----+----+------- c | value | 3 | | | 239 c | value | 5 | | | 241 c | other | | 7 | | 1920 (3 rows) SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '[{"name": "c", "expr": "category", "size": 2, "order": "value"}]', 'qty < 20'); facet | kind | value | lo | hi | count -------+-------+-------+----+----+------- c | value | 0 | | | 44 c | value | 1 | | | 37 c | other | | 7 | | 307 (3 rows) -- the scans of the statement: one per facet (the facets that count the same rows are not merged), the ranges with one WHERE share one SELECT (SELECT count(*) FROM regexp_matches(sift.facet_sql('fc', 'title', 'w1', '["brand", "category", "qty", "active", "added", "price"]'), ' MATERIALIZED ', 'g')) AS scans_of_six, (SELECT count(*) FROM regexp_matches(sift.facet_sql('fc', 'title', 'w1', '["brand", {"expr": "price", "edges": [100]}, {"expr": "qty", "edges": [5, 9]}]'), ' MATERIALIZED ', 'g')) AS scans_with_ranges, (SELECT count(*) FROM regexp_matches(sift.facet_sql('fc', 'title', 'w1', '[{"expr": "brand", "selected": ["a"]}, {"expr": "price", "edges": [100], "selected": [0]}, {"expr": "qty", "edges": [5]}]'), ' MATERIALIZED ', 'g')) AS scans_drill; scans_of_six | scans_with_ranges | scans_drill --------------+-------------------+------------- 6 | 2 | 3 (1 row) -- the same facets over a table without a column index (the planner's plan) and over a sift_table (its own column scan) CREATE TABLE fcn (LIKE fc INCLUDING DEFAULTS); ALTER TABLE fcn ALTER COLUMN pad SET STORAGE PLAIN; INSERT INTO fcn SELECT * FROM fc; CREATE INDEX fcn_bm ON fcn USING sift (title); CREATE TABLE fct (LIKE fc INCLUDING DEFAULTS) USING sift_table; INSERT INTO fct SELECT * FROM fc; CREATE INDEX fct_bm ON fct USING sift (title); VACUUM (ANALYZE) fcn, fct; SELECT * FROM fc_stage('no column index', 'fcn'); stage | results -----------------+---------------------------------------------------------- no column index | array=same/same 0 SiftAgg, 0 searching, 7 rows + | drill=same/same 0 SiftAgg, 0 searching, 19 rows + | drill_buckets=same/same 0 SiftAgg, 0 searching, 38 rows + | drill_dates=same/same 0 SiftAgg, 0 searching, 32 rows + | histogram=same/same 0 SiftAgg, 0 searching, 7 rows + | histogram_fill=same/same 0 SiftAgg, 0 searching, 42 rows+ | histogram_float=same/same 0 SiftAgg, 0 searching, 7 rows+ | many=same/same 0 SiftAgg, 0 searching, 35 rows + | month=same/same 0 SiftAgg, 0 searching, 18 rows + | no_match=same/same 0 SiftAgg, 0 searching, 3 rows + | no_search=same/same 0 SiftAgg, 0 searching, 7 rows + | quarter_fill=same/same 0 SiftAgg, 0 searching, 7 rows + | range=same/same 0 SiftAgg, 0 searching, 4 rows + | range_dates=same/same 0 SiftAgg, 0 searching, 4 rows + | range_missing=same/same 0 SiftAgg, 0 searching, 7 rows + | terms_bool=same/same 0 SiftAgg, 0 searching, 4 rows + | terms_filter=same/same 0 SiftAgg, 0 searching, 10 rows + | terms_other=same/same 0 SiftAgg, 0 searching, 5 rows + | terms_text=same/same 0 SiftAgg, 0 searching, 7 rows + | terms_top=same/same 0 SiftAgg, 0 searching, 5 rows + | week_ts=same/same 0 SiftAgg, 0 searching, 73 rows (1 row) SELECT * FROM fc_stage('sift_table', 'fct'); stage | results ------------+---------------------------------------------------------- sift_table | array=same/same 2 SiftAgg, 2 searching, 7 rows + | drill=same/same 4 SiftAgg, 4 searching, 19 rows + | drill_buckets=same/same 5 SiftAgg, 5 searching, 38 rows + | drill_dates=same/same 4 SiftAgg, 4 searching, 32 rows + | histogram=same/same 2 SiftAgg, 2 searching, 7 rows + | histogram_fill=same/same 2 SiftAgg, 2 searching, 42 rows+ | histogram_float=same/same 2 SiftAgg, 2 searching, 7 rows+ | many=same/same 7 SiftAgg, 7 searching, 35 rows + | month=same/same 2 SiftAgg, 2 searching, 18 rows + | no_match=same/same 3 SiftAgg, 3 searching, 3 rows + | no_search=same/same 2 SiftAgg, 0 searching, 7 rows + | quarter_fill=same/same 2 SiftAgg, 2 searching, 7 rows + | range=same/same 2 SiftAgg, 2 searching, 4 rows + | range_dates=same/same 2 SiftAgg, 2 searching, 4 rows + | range_missing=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_bool=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_filter=same/same 2 SiftAgg, 2 searching, 10 rows + | terms_other=same/same 2 SiftAgg, 2 searching, 5 rows + | terms_text=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_top=same/same 2 SiftAgg, 2 searching, 5 rows + | week_ts=same/same 2 SiftAgg, 2 searching, 73 rows (1 row) -- what the callers get wrong \set VERBOSITY terse SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '[]'); ERROR: facets must be a non-empty JSON array SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '{"expr": "brand"}'); ERROR: facets must be a non-empty JSON array SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '[{"type": "nope", "expr": "brand"}]'); ERROR: facet 1: unknown type "nope" (terms, range, histogram, date_histogram) SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '[{"type": "histogram", "expr": "qty"}]'); ERROR: facet 1: "interval" must be a positive number SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '[{"type": "range", "expr": "price", "edges": []}]'); ERROR: facet 1: "edges" must list at least one bound SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '[{"expr": "brand", "order": "random"}]'); ERROR: facet 1: "order" is count, count asc, value or value desc SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '[{"expr": "added", "unit": "fortnight"}]'); ERROR: facet 1: "unit" is second, minute, hour, day, week, month, quarter or year SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '[{"expr": "brand", "type": "histogram", "interval": 5, "array": true}]'); ERROR: facet 1: "array" is for terms facets SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '[{"size": 3}]'); ERROR: facet 1: "expr" or "name" is required SELECT * FROM sift.facet_counts('fc', NULL, 'wireless', '["brand"]'); ERROR: key: the indexed expression of the search is required with a query SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '["nope"]'); ERROR: column "nope" does not exist at character 34 SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '[{"expr": "qty", "interval": 0.0001, "fill": true}]'); ERROR: a facet with "fill" would return more than 10000 buckets SELECT * FROM sift.facet_counts('fc', 'title', 'wireless', '[{"expr": "ts", "unit": "second", "fill": true}]'); ERROR: a facet with "fill" would return more than 10000 buckets -- a role that can read the table gets the counts of what it can read (the statement runs as the caller) CREATE ROLE fc_reader; GRANT SELECT ON fc TO fc_reader; GRANT USAGE ON SCHEMA sift TO fc_reader; SET ROLE fc_reader; SELECT sum(count) FROM sift.facet_counts('fc', 'title', 'wireless', '["category"]'); sum ------ 2400 (1 row) RESET ROLE; CREATE ROLE fc_nobody; GRANT USAGE ON SCHEMA sift TO fc_nobody; SET ROLE fc_nobody; SELECT sum(count) FROM sift.facet_counts('fc', 'title', 'wireless', '["category"]'); ERROR: permission denied for table fc RESET ROLE; DROP OWNED BY fc_reader, fc_nobody; DROP ROLE fc_reader, fc_nobody; \set VERBOSITY default -- writes: each kind of change, with the facets of the table compared again SET sift.test_search_chunk_sbs = 1; SELECT * FROM fc_stage('chunks of one superblock'); stage | results --------------------------+---------------------------------------------------------- chunks of one superblock | array=same/same 1 SiftAgg, 1 searching, 7 rows + | drill=same/same 4 SiftAgg, 4 searching, 19 rows + | drill_buckets=same/same 0 SiftAgg, 0 searching, 38 rows + | drill_dates=same/same 4 SiftAgg, 4 searching, 32 rows + | histogram=same/same 2 SiftAgg, 2 searching, 7 rows + | histogram_fill=same/same 2 SiftAgg, 2 searching, 42 rows+ | histogram_float=same/same 2 SiftAgg, 2 searching, 7 rows+ | many=same/same 7 SiftAgg, 7 searching, 35 rows + | month=same/same 2 SiftAgg, 2 searching, 18 rows + | no_match=same/same 3 SiftAgg, 3 searching, 3 rows + | no_search=same/same 2 SiftAgg, 0 searching, 7 rows + | quarter_fill=same/same 2 SiftAgg, 2 searching, 7 rows + | range=same/same 2 SiftAgg, 2 searching, 4 rows + | range_dates=same/same 2 SiftAgg, 2 searching, 4 rows + | range_missing=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_bool=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_filter=same/same 2 SiftAgg, 2 searching, 10 rows + | terms_other=same/same 2 SiftAgg, 2 searching, 5 rows + | terms_text=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_top=same/same 2 SiftAgg, 2 searching, 5 rows + | week_ts=same/same 2 SiftAgg, 2 searching, 73 rows (1 row) RESET sift.test_search_chunk_sbs; INSERT INTO fc (title, brand, category, price, qty, added, ts, active, tags) SELECT 'wireless fresh w' || i, (ARRAY['acme', 'new', NULL])[1 + i % 3], 20 + i % 3, i % 700, i % 40, date '2025-06-01' + i % 90, timestamp '2025-06-01' + i % 90 * interval '1 day', i % 2 = 0, ARRAY['red', 'fresh'] FROM generate_series(1, 900) i; SELECT * FROM fc_stage('insert'); stage | results --------+---------------------------------------------------------- insert | array=same/same 1 SiftAgg, 1 searching, 8 rows + | drill=same/same 4 SiftAgg, 4 searching, 21 rows + | drill_buckets=same/same 0 SiftAgg, 0 searching, 41 rows + | drill_dates=same/same 4 SiftAgg, 4 searching, 35 rows + | histogram=same/same 2 SiftAgg, 2 searching, 7 rows + | histogram_fill=same/same 2 SiftAgg, 2 searching, 42 rows+ | histogram_float=same/same 2 SiftAgg, 2 searching, 7 rows+ | many=same/same 7 SiftAgg, 7 searching, 35 rows + | month=same/same 2 SiftAgg, 2 searching, 21 rows + | no_match=same/same 3 SiftAgg, 3 searching, 3 rows + | no_search=same/same 2 SiftAgg, 0 searching, 7 rows + | quarter_fill=same/same 2 SiftAgg, 2 searching, 7 rows + | range=same/same 2 SiftAgg, 2 searching, 4 rows + | range_dates=same/same 2 SiftAgg, 2 searching, 4 rows + | range_missing=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_bool=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_filter=same/same 2 SiftAgg, 2 searching, 10 rows + | terms_other=same/same 2 SiftAgg, 2 searching, 5 rows + | terms_text=same/same 2 SiftAgg, 2 searching, 8 rows + | terms_top=same/same 2 SiftAgg, 2 searching, 5 rows + | week_ts=same/same 2 SiftAgg, 2 searching, 73 rows (1 row) UPDATE fc SET brand = 'zeta', price = price + 150, added = added + 40, ts = ts + interval '40 days', qty = qty + 30, tags = tags || 'moved'::text WHERE id % 7 = 0; SELECT * FROM fc_stage('update'); stage | results --------+---------------------------------------------------------- update | array=same/same 1 SiftAgg, 1 searching, 9 rows + | drill=same/same 4 SiftAgg, 4 searching, 23 rows + | drill_buckets=same/same 0 SiftAgg, 0 searching, 46 rows + | drill_dates=same/same 4 SiftAgg, 4 searching, 39 rows + | histogram=same/same 2 SiftAgg, 2 searching, 8 rows + | histogram_fill=same/same 2 SiftAgg, 2 searching, 52 rows+ | histogram_float=same/same 2 SiftAgg, 2 searching, 9 rows+ | many=same/same 7 SiftAgg, 7 searching, 38 rows + | month=same/same 2 SiftAgg, 2 searching, 23 rows + | no_match=same/same 3 SiftAgg, 3 searching, 3 rows + | no_search=same/same 2 SiftAgg, 0 searching, 7 rows + | quarter_fill=same/same 2 SiftAgg, 2 searching, 7 rows + | range=same/same 2 SiftAgg, 2 searching, 4 rows + | range_dates=same/same 2 SiftAgg, 2 searching, 4 rows + | range_missing=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_bool=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_filter=same/same 2 SiftAgg, 2 searching, 10 rows + | terms_other=same/same 2 SiftAgg, 2 searching, 5 rows + | terms_text=same/same 2 SiftAgg, 2 searching, 8 rows + | terms_top=same/same 2 SiftAgg, 2 searching, 5 rows + | week_ts=same/same 2 SiftAgg, 2 searching, 79 rows (1 row) DELETE FROM fc WHERE id % 5 = 0; SELECT * FROM fc_stage('delete'); stage | results --------+---------------------------------------------------------- delete | array=same/same 1 SiftAgg, 1 searching, 4 rows + | drill=same/same 4 SiftAgg, 4 searching, 23 rows + | drill_buckets=same/same 0 SiftAgg, 0 searching, 6 rows + | drill_dates=same/same 4 SiftAgg, 4 searching, 6 rows + | histogram=same/same 2 SiftAgg, 2 searching, 8 rows + | histogram_fill=same/same 2 SiftAgg, 2 searching, 52 rows+ | histogram_float=same/same 2 SiftAgg, 2 searching, 9 rows+ | many=same/same 7 SiftAgg, 7 searching, 38 rows + | month=same/same 2 SiftAgg, 2 searching, 6 rows + | no_match=same/same 3 SiftAgg, 3 searching, 3 rows + | no_search=same/same 2 SiftAgg, 0 searching, 7 rows + | quarter_fill=same/same 2 SiftAgg, 2 searching, 7 rows + | range=same/same 2 SiftAgg, 2 searching, 4 rows + | range_dates=same/same 2 SiftAgg, 2 searching, 4 rows + | range_missing=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_bool=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_filter=same/same 2 SiftAgg, 2 searching, 10 rows + | terms_other=same/same 2 SiftAgg, 2 searching, 5 rows + | terms_text=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_top=same/same 2 SiftAgg, 2 searching, 5 rows + | week_ts=same/same 2 SiftAgg, 2 searching, 79 rows (1 row) BEGIN; DELETE FROM fc WHERE id % 3 = 0; INSERT INTO fc (title, brand, category, price, qty, added, ts, active, tags) SELECT 'wireless own w' || i, 'own', 50, 99, 1, date '2024-02-02', timestamp '2024-02-02', true, ARRAY['own'] FROM generate_series(1, 50) i; SELECT * FROM fc_stage('own transaction'); stage | results -----------------+---------------------------------------------------------- own transaction | array=same/same 1 SiftAgg, 1 searching, 5 rows + | drill=same/same 4 SiftAgg, 4 searching, 8 rows + | drill_buckets=same/same 0 SiftAgg, 0 searching, 6 rows + | drill_dates=same/same 4 SiftAgg, 4 searching, 6 rows + | histogram=same/same 2 SiftAgg, 2 searching, 8 rows + | histogram_fill=same/same 2 SiftAgg, 2 searching, 52 rows+ | histogram_float=same/same 2 SiftAgg, 2 searching, 9 rows+ | many=same/same 7 SiftAgg, 7 searching, 37 rows + | month=same/same 2 SiftAgg, 2 searching, 7 rows + | no_match=same/same 3 SiftAgg, 3 searching, 3 rows + | no_search=same/same 2 SiftAgg, 0 searching, 7 rows + | quarter_fill=same/same 2 SiftAgg, 2 searching, 7 rows + | range=same/same 2 SiftAgg, 2 searching, 4 rows + | range_dates=same/same 2 SiftAgg, 2 searching, 4 rows + | range_missing=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_bool=same/same 2 SiftAgg, 2 searching, 3 rows + | terms_filter=same/same 2 SiftAgg, 2 searching, 1 rows + | terms_other=same/same 2 SiftAgg, 2 searching, 5 rows + | terms_text=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_top=same/same 2 SiftAgg, 2 searching, 5 rows + | week_ts=same/same 2 SiftAgg, 2 searching, 79 rows (1 row) ROLLBACK; SELECT * FROM fc_stage('rollback'); stage | results ----------+---------------------------------------------------------- rollback | array=same/same 1 SiftAgg, 1 searching, 4 rows + | drill=same/same 4 SiftAgg, 4 searching, 23 rows + | drill_buckets=same/same 0 SiftAgg, 0 searching, 6 rows + | drill_dates=same/same 4 SiftAgg, 4 searching, 6 rows + | histogram=same/same 2 SiftAgg, 2 searching, 8 rows + | histogram_fill=same/same 2 SiftAgg, 2 searching, 52 rows+ | histogram_float=same/same 2 SiftAgg, 2 searching, 9 rows+ | many=same/same 7 SiftAgg, 7 searching, 38 rows + | month=same/same 2 SiftAgg, 2 searching, 6 rows + | no_match=same/same 3 SiftAgg, 3 searching, 3 rows + | no_search=same/same 2 SiftAgg, 0 searching, 7 rows + | quarter_fill=same/same 2 SiftAgg, 2 searching, 7 rows + | range=same/same 2 SiftAgg, 2 searching, 4 rows + | range_dates=same/same 2 SiftAgg, 2 searching, 4 rows + | range_missing=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_bool=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_filter=same/same 2 SiftAgg, 2 searching, 10 rows + | terms_other=same/same 2 SiftAgg, 2 searching, 5 rows + | terms_text=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_top=same/same 2 SiftAgg, 2 searching, 5 rows + | week_ts=same/same 2 SiftAgg, 2 searching, 79 rows (1 row) VACUUM fc; SELECT * FROM fc_stage('vacuum'); stage | results --------+---------------------------------------------------------- vacuum | array=same/same 1 SiftAgg, 1 searching, 4 rows + | drill=same/same 4 SiftAgg, 4 searching, 23 rows + | drill_buckets=same/same 0 SiftAgg, 0 searching, 6 rows + | drill_dates=same/same 4 SiftAgg, 4 searching, 6 rows + | histogram=same/same 2 SiftAgg, 2 searching, 8 rows + | histogram_fill=same/same 2 SiftAgg, 2 searching, 52 rows+ | histogram_float=same/same 2 SiftAgg, 2 searching, 9 rows+ | many=same/same 7 SiftAgg, 7 searching, 38 rows + | month=same/same 2 SiftAgg, 2 searching, 6 rows + | no_match=same/same 3 SiftAgg, 3 searching, 3 rows + | no_search=same/same 2 SiftAgg, 0 searching, 7 rows + | quarter_fill=same/same 2 SiftAgg, 2 searching, 7 rows + | range=same/same 2 SiftAgg, 2 searching, 4 rows + | range_dates=same/same 2 SiftAgg, 2 searching, 4 rows + | range_missing=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_bool=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_filter=same/same 2 SiftAgg, 2 searching, 10 rows + | terms_other=same/same 2 SiftAgg, 2 searching, 5 rows + | terms_text=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_top=same/same 2 SiftAgg, 2 searching, 5 rows + | week_ts=same/same 2 SiftAgg, 2 searching, 79 rows (1 row) SELECT sift.promote('fc_bm') >= 0 AS promoted_bm, sift.col_promote('fc_col') >= 0 AS promoted_col; promoted_bm | promoted_col -------------+-------------- t | t (1 row) SELECT * FROM fc_stage('promote'); stage | results ---------+---------------------------------------------------------- promote | array=same/same 1 SiftAgg, 1 searching, 4 rows + | drill=same/same 4 SiftAgg, 4 searching, 23 rows + | drill_buckets=same/same 0 SiftAgg, 0 searching, 6 rows + | drill_dates=same/same 4 SiftAgg, 4 searching, 6 rows + | histogram=same/same 2 SiftAgg, 2 searching, 8 rows + | histogram_fill=same/same 2 SiftAgg, 2 searching, 52 rows+ | histogram_float=same/same 2 SiftAgg, 2 searching, 9 rows+ | many=same/same 7 SiftAgg, 7 searching, 38 rows + | month=same/same 2 SiftAgg, 2 searching, 6 rows + | no_match=same/same 3 SiftAgg, 3 searching, 3 rows + | no_search=same/same 2 SiftAgg, 0 searching, 7 rows + | quarter_fill=same/same 2 SiftAgg, 2 searching, 7 rows + | range=same/same 2 SiftAgg, 2 searching, 4 rows + | range_dates=same/same 2 SiftAgg, 2 searching, 4 rows + | range_missing=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_bool=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_filter=same/same 2 SiftAgg, 2 searching, 10 rows + | terms_other=same/same 2 SiftAgg, 2 searching, 5 rows + | terms_text=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_top=same/same 2 SiftAgg, 2 searching, 5 rows + | week_ts=same/same 2 SiftAgg, 2 searching, 79 rows (1 row) SELECT sift.merge('fc_bm', true) >= 0 AS merged_bm, sift.col_merge('fc_col', true) >= 0 AS merged_col; merged_bm | merged_col -----------+------------ t | t (1 row) SELECT * FROM fc_stage('merge'); stage | results -------+---------------------------------------------------------- merge | array=same/same 1 SiftAgg, 1 searching, 4 rows + | drill=same/same 4 SiftAgg, 4 searching, 23 rows + | drill_buckets=same/same 0 SiftAgg, 0 searching, 6 rows + | drill_dates=same/same 4 SiftAgg, 4 searching, 6 rows + | histogram=same/same 2 SiftAgg, 2 searching, 8 rows + | histogram_fill=same/same 2 SiftAgg, 2 searching, 52 rows+ | histogram_float=same/same 2 SiftAgg, 2 searching, 9 rows+ | many=same/same 7 SiftAgg, 7 searching, 38 rows + | month=same/same 2 SiftAgg, 2 searching, 6 rows + | no_match=same/same 3 SiftAgg, 3 searching, 3 rows + | no_search=same/same 2 SiftAgg, 0 searching, 7 rows + | quarter_fill=same/same 2 SiftAgg, 2 searching, 7 rows + | range=same/same 2 SiftAgg, 2 searching, 4 rows + | range_dates=same/same 2 SiftAgg, 2 searching, 4 rows + | range_missing=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_bool=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_filter=same/same 2 SiftAgg, 2 searching, 10 rows + | terms_other=same/same 2 SiftAgg, 2 searching, 5 rows + | terms_text=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_top=same/same 2 SiftAgg, 2 searching, 5 rows + | week_ts=same/same 2 SiftAgg, 2 searching, 79 rows (1 row) SET max_parallel_workers_per_gather = 2; SET parallel_setup_cost = 0; SET parallel_tuple_cost = 0; SET min_parallel_table_scan_size = 0; SELECT * FROM fc_stage('parallel'); stage | results ----------+---------------------------------------------------------- parallel | array=same/same 1 SiftAgg, 1 searching, 4 rows + | drill=same/same 4 SiftAgg, 4 searching, 23 rows + | drill_buckets=same/same 0 SiftAgg, 0 searching, 6 rows + | drill_dates=same/same 4 SiftAgg, 4 searching, 6 rows + | histogram=same/same 2 SiftAgg, 2 searching, 8 rows + | histogram_fill=same/same 2 SiftAgg, 2 searching, 52 rows+ | histogram_float=same/same 2 SiftAgg, 2 searching, 9 rows+ | many=same/same 7 SiftAgg, 7 searching, 38 rows + | month=same/same 2 SiftAgg, 2 searching, 6 rows + | no_match=same/same 3 SiftAgg, 3 searching, 3 rows + | no_search=same/same 2 SiftAgg, 0 searching, 7 rows + | quarter_fill=same/same 2 SiftAgg, 2 searching, 7 rows + | range=same/same 2 SiftAgg, 2 searching, 4 rows + | range_dates=same/same 2 SiftAgg, 2 searching, 4 rows + | range_missing=same/same 2 SiftAgg, 2 searching, 7 rows + | terms_bool=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_filter=same/same 2 SiftAgg, 2 searching, 10 rows + | terms_other=same/same 2 SiftAgg, 2 searching, 5 rows + | terms_text=same/same 2 SiftAgg, 2 searching, 4 rows + | terms_top=same/same 2 SiftAgg, 2 searching, 5 rows + | week_ts=same/same 2 SiftAgg, 2 searching, 79 rows (1 row) DROP FUNCTION fc_mode(bool), fc_terms(text, text, text, int, bool), fc_check(regclass, text, jsonb, text, text, text), fc_stage(text, regclass); DROP TABLE fc, fc_q, fcn, fct; DROP EXTENSION pg_sift_helpers; DROP EXTENSION pg_sift;