Skip to content

Instantly share code, notes, and snippets.

@andyatkinson

andyatkinson/enable_partitionwise_aggregate.sql

Created October 11, 2023 03:02
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save andyatkinson/7af81fb8a5b9e677af6049e29ab2cb73 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save andyatkinson/7af81fb8a5b9e677af6049e29ab2cb73 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. andyatkinson created this gist Oct 11, 2023.
    35 changes: 35 additions & 0 deletions enable_partitionwise_aggregate.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,35 @@
    create table t (id serial, created_at timestamptz) partition by range (created_at);
    create table t_202309 partition of t for values from ('2023-09-01') TO ('2023-10-01');
    create table t_202310 partition of t for values from ('2023-10-01') TO ('2023-11-01');


    postgres@[local]:5432 testing# \d t
    Partitioned table "public.t"
    Column | Type | Collation | Nullable | Default
    ------------+--------------------------+-----------+----------+-------------------------------
    id | integer | | not null | nextval('t_id_seq'::regclass)
    created_at | timestamp with time zone | | |
    Partition key: RANGE (created_at)
    Number of partitions: 2 (Use \d+ to list them.)

    show enable_partitionwise_aggregate;
    enable_partitionwise_aggregate
    --------------------------------
    off

    explain analyze select sum(id) from t group by id;


    -- seq scan on each partition table
    -- Append node
    -- Group key above append node

    set enable_partitionwise_aggregate = on;

    explain analyze select sum(id) from t group by id;

    -- seq scan on each partition table
    -- Batch, Group Key
    -- Partial HashAggregate on each partition table
    -- Append node
    -- Grouping above append node