Can the same queries be achieved without generate_subscriptions which I have found to be…
- User
Can the same queries be achieved without generate_subscriptions which I have found to be very slow?
Assistant
To address performance concerns with
generate_subscripts, we can replace it withUNNEST(... WITH ORDINALITY)for faster index tracking. Here's an optimized version of the solution withoutgenerate_subscriptswhile maintaining efficiency:
Revised Query Function (Without
generate_subscripts)CREATE OR REPLACE FUNCTION query_columnar_data( start_time timestamptz, end_time timestamptz, min_value numeric DEFAULT NULL, max_value numeric DEFAULT NULL ) RETURNS TABLE ( id bigint, ts timestamptz, val numeric, txt text ) AS $$ BEGIN RETURN QUERY WITH matching_chunks AS ( SELECT chunk_id, id_array, timestamp_array, value_array, text_array, deletion_bitmap FROM columnar_store WHERE time_range && tstzrange(start_time, end_time, '[]') AND (min_value IS NULL OR (value_stats->>'max_value')::numeric >= min_value) AND (max_value IS NULL OR (value_stats->>'min_value')::numeric <= max_value) ), unnested AS ( SELECT chunk_id, id, ts, val, txt, array_index FROM matching_chunks, UNNEST( id_array, timestamp_array, value_array, text_array ) WITH ORDINALITY AS unnest(id, ts, val, txt, array_index) ) SELECT id, ts, val, txt FROM unnested WHERE ts BETWEEN start_time AND end_time AND (min_value IS NULL OR val >= min_value) AND (max_value IS NULL OR val <= max_value) AND (deletion_bitmap IS NULL OR NOT get_bit(deletion_bitmap, array_index - 1)); END; $$ LANGUAGE plpgsql;
Key Improvements
-
UNNEST(... WITH ORDINALITY):- Replaces
generate_subscriptswith a built-in PostgreSQL feature that returns the ordinal position of each element in parallel-unnested arrays. - This is faster and avoids the overhead of
generate_subscripts, which is a function call for each element.
- Replaces
-
Parallel Unnesting:
- The
UNNESTclause processes all arrays (id_array,timestamp_array, etc.) in parallel, ensuring elements are aligned by their original array indices. - The
array_indexcolumn now directly tracks the position (starting at1).
- The
-
Deletion Bitmap Check:
- The
get_bitcheck usesarray_index - 1to convert the 1-based ordinality to a 0-based bit position (since bits are zero-indexed).
- The
Additional Optimizations
1. Indexing for Statistics
-
Precompute and store statistics (min, max, avg) in dedicated columns instead of
jsonbfor faster lookups:ALTER TABLE columnar_store ADD COLUMN value_min numeric, ADD COLUMN value_max numeric, ADD COLUMN value_avg numeric; -- Update existing chunks with stats UPDATE columnar_store SET value_min = min(value_array), value_max = max(value_array), value_avg = avg(value_array); -
Replace
jsonbfiltering with direct column comparisons:-- In the query's WHERE clause: AND (min_value IS NULL OR value_max >= min_value) AND (max_value IS NULL OR value_min <= max_value)
2. Efficient Soft Delete Updates
- Use
bitwise ORto update the deletion bitmap in a single operation instead of looping:-- In soft_delete_columnar_data(): UPDATE columnar_store SET deletion_bitmap = (deletion_bitmap::bit(ARRAY_LENGTH(id_array, 1)) | (SELECT bitor(ARRAY_AGG( (1 << (idx - 1))::bit(ARRAY_LENGTH(id_array, 1)) )) FROM UNNEST(p_array_indexes) AS idx)) WHERE chunk_id = p_chunk_id;
3. Avoiding Chunk Proliferation
-
For updates, instead of creating a new chunk, use a "delta" table to track changes (if updates are rare):
CREATE TABLE delta_store ( chunk_id bigint REFERENCES columnar_store(chunk_id), array_index int, new_val numeric, new_text text, PRIMARY KEY (chunk_id, array_index) ); -
Query joins can then reconcile the delta table with the main chunk data.
Example Usage
-- Insert test data (unchanged) SELECT insert_columnar_chunk( 'SELECT generate_series(1,1000) as id, now() + (interval ''1 minute'' * generate_series(1,1000)) as timestamp_col, random() * 100 as value_col, md5(random()::text) as text_col' ); -- Query data (uses optimized unnesting) SELECT * FROM query_columnar_data( now(), now() + interval '1 day', 50, 100 ); -- Soft delete (uses bitwise OR optimization) SELECT soft_delete_columnar_data(1, ARRAY[1,2,3,4,5]); -- Update (uses delta table approach) INSERT INTO delta_store (chunk_id, array_index, new_val, new_text) VALUES (1, 10, 99.99, 'updated text');
Summary of Performance Gains
Area Before After Index Tracking generate_subscripts(slow)UNNEST(... WITH ORDINALITY)(fast)Statistics JSONB lookup (slow) Dedicated columns (fast) Deletion Updates Loop-based array_reduce(slow)Bitwise OR (fast) This approach maintains the core benefits of columnar storage (compression, vectorized operations) while addressing the performance bottleneck of
generate_subscripts.-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.