Skip to content
TheChatArchiveThe Chat Archive
Qwenqwq-32b· Alibaba (Qwen)Hugging Face LMArena Human Preference 140K

Can the same queries be achieved without generate_subscriptions which I have found to be…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Qwenqwen-max-2025-01-25’s answer.
  1. User

    Can the same queries be achieved without generate_subscriptions which I have found to be very slow?

  2. Qwen Assistant

    To address performance concerns with generate_subscripts, we can replace it with UNNEST(... WITH ORDINALITY) for faster index tracking. Here's an optimized version of the solution without generate_subscripts while 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

    1. UNNEST(... WITH ORDINALITY):

      • Replaces generate_subscripts with 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.
    2. Parallel Unnesting:

      • The UNNEST clause processes all arrays (id_array, timestamp_array, etc.) in parallel, ensuring elements are aligned by their original array indices.
      • The array_index column now directly tracks the position (starting at 1).
    3. Deletion Bitmap Check:

      • The get_bit check uses array_index - 1 to convert the 1-based ordinality to a 0-based bit position (since bits are zero-indexed).

    Additional Optimizations

    1. Indexing for Statistics

    • Precompute and store statistics (min, max, avg) in dedicated columns instead of jsonb for 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 jsonb filtering 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 OR to 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

    AreaBeforeAfter
    Index Trackinggenerate_subscripts (slow)UNNEST(... WITH ORDINALITY) (fast)
    StatisticsJSONB lookup (slow)Dedicated columns (fast)
    Deletion UpdatesLoop-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.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.