Hello,
We are having performance issues with a table partitioned by date, using composite type columns.
I have attached the table definition, full reproducible of the issue and execution plans to this email.
Ultimately, we want to sum certain fields contained in those composite types,
using a simple status filter across one month (31 partition) and group by date.
But, we fail to get a satisfactory performance even on a single partition.
I have tried multiple indexing options, but none work for us, as I will explain.
I will refer to these indexes as: ix1, ix2, ix3, ix4, ix5, ix6 and for the second part ix7.
ix1-ix6 are defined in the attached repro.sql file and the performance of the query with each of them is shown in exec_plans file.
ix7 is defined in the repro_part_vs_parent.sql and performance of relevant queries in exec_plans_part_vs_parent file.
This is the query targeting single partition:
SELECT
SUM(COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0)) AS val1,
SUM(COALESCE((col2).y, 0)) AS val2
FROM
public."mytable:2020-12-09" --single partition of public.mytable
WHERE status IN (1,2,3,4);
We get the best performance using ix2, while I would expect to get better performance using ix3, and perhaps ix5.
Questions:
1. Why cannot Postgres plan for index-only scan with ix3?
2. Why is the query cost so high when using ix3?
3. Is it possible to define an index such as ix3, that is, with a drastically reduced size and listing only expressions we project?
4. Are there any other indexing or query rewrite options that are worth trying here?
5. Judging by execution time, it seems that Postgres can leverage defined expressions in ix2, so why not in ix3? Why must it fetch col1 and col2 from the table when I force ix3 usage?
6. As ix3 is only 53MB in size (see repro.sql) as opposed to ix1 and ix2 which are 266MB and 280MB respectively, I would expect Postgres to use it instead?
In addition to this, please look at the attached repro_part_vs_parent.sql file and its related execution plans file.
There, I tried running a similar query on a partitioned table targeting a single partition, and afterwards on the partition itself.
The results confuse me. I would expect to get similar performance in both situations, but the query runs much slower through the parent table.
By looking at the output of the seq scan node (parent query), it seems that running the query on the parent table prepends partition name as an alias to projected columns.
Does that make Postgres unable to recognize the _expression_ in the index, or is there something else happening here?
These are the queries:
--partitioned (parent) table, targeting single partition
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, SETTINGS)
SELECT
dt,
SUM(COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0)) AS expected,
SUM(COALESCE((col2).y, 0)) AS repayments
FROM
public.mytable
WHERE
dt = '2020-12-09'
AND status IN (1,2,3,4)
GROUP BY
dt;
--querying the partition directly instead:
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, SETTINGS)
SELECT
dt,
SUM(COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0)) AS expected,
SUM(COALESCE((col2).y, 0)) AS repayments
FROM
public."mytable:2020-12-09"
WHERE
dt = '2020-12-09'
AND status IN (1,2,3,4)
GROUP BY
dt;
Relevant setup information:
pg version/OS (1): PostgreSQL 12.5, compiled by Visual C++ build 1914, 64-bit / Windows 10
pg version/OS (2): PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit / CentOS Linux release 7.8.2003 (Core)
total number of table partitions: 31
single partition size (with PK, no other indexes): 4GB
single partition number of rows: 2M
Postgres configuration settings can be observed in the provided execution plans
depesz links:
no index: https://explain.depesz.com/s/8H93
ix1: https://explain.depesz.com/s/kEYi
ix2: https://explain.depesz.com/s/yydX
ix3: https://explain.depesz.com/s/gAFm
ix4: https://explain.depesz.com/s/8lbh
ix5: https://explain.depesz.com/s/WIqwK
ix6: https://explain.depesz.com/s/BNUc
ix7 (parent): https://explain.depesz.com/s/DqUf
ix7 (child): https://explain.depesz.com/s/ejmP
Attached files:
1. repro.sql: contains the code which will reproduce my issue
2. exec_plans: lists execution plans for repro.sql I got on my machine with each of the mentioned indexes in place
3. repro_part_vs_parent.sql: contains queries showing the unexpected performance difference for the identical query ran on parent table vs. single partition
4. exec_plans_part_vs_parent: lists relevant execution plans for repro_part_vs_parent.sql
Thank you very much in advance.
We are having performance issues with a table partitioned by date, using composite type columns.
I have attached the table definition, full reproducible of the issue and execution plans to this email.
Ultimately, we want to sum certain fields contained in those composite types,
using a simple status filter across one month (31 partition) and group by date.
But, we fail to get a satisfactory performance even on a single partition.
I have tried multiple indexing options, but none work for us, as I will explain.
I will refer to these indexes as: ix1, ix2, ix3, ix4, ix5, ix6 and for the second part ix7.
ix1-ix6 are defined in the attached repro.sql file and the performance of the query with each of them is shown in exec_plans file.
ix7 is defined in the repro_part_vs_parent.sql and performance of relevant queries in exec_plans_part_vs_parent file.
This is the query targeting single partition:
SELECT
SUM(COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0)) AS val1,
SUM(COALESCE((col2).y, 0)) AS val2
FROM
public."mytable:2020-12-09" --single partition of public.mytable
WHERE status IN (1,2,3,4);
We get the best performance using ix2, while I would expect to get better performance using ix3, and perhaps ix5.
Questions:
1. Why cannot Postgres plan for index-only scan with ix3?
2. Why is the query cost so high when using ix3?
3. Is it possible to define an index such as ix3, that is, with a drastically reduced size and listing only expressions we project?
4. Are there any other indexing or query rewrite options that are worth trying here?
5. Judging by execution time, it seems that Postgres can leverage defined expressions in ix2, so why not in ix3? Why must it fetch col1 and col2 from the table when I force ix3 usage?
6. As ix3 is only 53MB in size (see repro.sql) as opposed to ix1 and ix2 which are 266MB and 280MB respectively, I would expect Postgres to use it instead?
In addition to this, please look at the attached repro_part_vs_parent.sql file and its related execution plans file.
There, I tried running a similar query on a partitioned table targeting a single partition, and afterwards on the partition itself.
The results confuse me. I would expect to get similar performance in both situations, but the query runs much slower through the parent table.
By looking at the output of the seq scan node (parent query), it seems that running the query on the parent table prepends partition name as an alias to projected columns.
Does that make Postgres unable to recognize the _expression_ in the index, or is there something else happening here?
These are the queries:
--partitioned (parent) table, targeting single partition
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, SETTINGS)
SELECT
dt,
SUM(COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0)) AS expected,
SUM(COALESCE((col2).y, 0)) AS repayments
FROM
public.mytable
WHERE
dt = '2020-12-09'
AND status IN (1,2,3,4)
GROUP BY
dt;
--querying the partition directly instead:
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, SETTINGS)
SELECT
dt,
SUM(COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0)) AS expected,
SUM(COALESCE((col2).y, 0)) AS repayments
FROM
public."mytable:2020-12-09"
WHERE
dt = '2020-12-09'
AND status IN (1,2,3,4)
GROUP BY
dt;
Relevant setup information:
pg version/OS (1): PostgreSQL 12.5, compiled by Visual C++ build 1914, 64-bit / Windows 10
pg version/OS (2): PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit / CentOS Linux release 7.8.2003 (Core)
total number of table partitions: 31
single partition size (with PK, no other indexes): 4GB
single partition number of rows: 2M
Postgres configuration settings can be observed in the provided execution plans
depesz links:
no index: https://explain.depesz.com/s/8H93
ix1: https://explain.depesz.com/s/kEYi
ix2: https://explain.depesz.com/s/yydX
ix3: https://explain.depesz.com/s/gAFm
ix4: https://explain.depesz.com/s/8lbh
ix5: https://explain.depesz.com/s/WIqwK
ix6: https://explain.depesz.com/s/BNUc
ix7 (parent): https://explain.depesz.com/s/DqUf
ix7 (child): https://explain.depesz.com/s/ejmP
Attached files:
1. repro.sql: contains the code which will reproduce my issue
2. exec_plans: lists execution plans for repro.sql I got on my machine with each of the mentioned indexes in place
3. repro_part_vs_parent.sql: contains queries showing the unexpected performance difference for the identical query ran on parent table vs. single partition
4. exec_plans_part_vs_parent: lists relevant execution plans for repro_part_vs_parent.sql
Thank you very much in advance.
Please let me know if something is unclear or if I can provide any other relevant info.
Best regards,
Sebastijan Wieser
Best regards,
Sebastijan Wieser
DROP INDEX IF EXISTS ix1,ix2,ix3,ix4,ix5,ix6; CREATE UNIQUE INDEX ix7 ON public.mytable ( dt, COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0), COALESCE((col2).y, 0), col1,col2, id ) WHERE status IN (1,2,3,4); VACUUM ANALYZE public.mytable; VACUUM ANALYZE public."mytable:2020-12-09"; --partitioned (parent) table, targeting single partition: EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, SETTINGS) SELECT dt, SUM(COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0)) AS expected, SUM(COALESCE((col2).y, 0)) AS repayments FROM public.mytable WHERE dt = '2020-12-09' AND status IN (1,2,3,4) GROUP BY dt; --querying the partition directly instead: EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, SETTINGS) SELECT dt, SUM(COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0)) AS expected, SUM(COALESCE((col2).y, 0)) AS repayments FROM public."mytable:2020-12-09" WHERE dt = '2020-12-09' AND status IN (1,2,3,4) GROUP BY dt;
Attachment:
exec_plans
Description: Binary data
Attachment:
exec_plans_part_vs_parent
Description: Binary data
/*
set work_mem = '32MB';
set effective_cache_size = '24GB';
set random_page_cost = 1.1; --ssd
set shared_buffers = 8GB
set fsync = 'on';
set synchronous_commit = 'on';
set max_parallel_workers_per_gather = 0;
set temp_buffers = '80MB';
set enable_partitionwise_aggregate = 'on';
*/
CREATE OR REPLACE
FUNCTION public.add_partitions(_schema TEXT,
_table TEXT,
_values TEXT[]) RETURNS void LANGUAGE plpgsql AS $function$ DECLARE n TEXT;
parent_table TEXT = quote_ident(_schema) || '.' || quote_ident(_table);
BEGIN FOR n IN
SELECT
'CREATE TABLE IF NOT EXISTS ' || quote_ident(_schema) || '.' || quote_ident(_table || ':' || v) || ' (LIKE ' || parent_table || ' INCLUDING ALL);
ALTER TABLE ' || parent_table || ' ATTACH PARTITION ' || quote_ident(_schema) || '.' || quote_ident(_table || ':' || v) || ' FOR VALUES IN (' || quote_literal(v) || ');'
FROM
UNNEST(_values) v
WHERE
NOT EXISTS(
SELECT
*
FROM
pg_type t
INNER JOIN pg_namespace n ON
t.typnamespace = n.oid
INNER JOIN LATERAL (
SELECT
1
FROM
pg_partition_tree(parent_table)
WHERE
relid = (quote_ident(_schema) || '.' || quote_ident(_table || ':' || v))::REGCLASS
AND isleaf ) p ON
TRUE
WHERE
n.nspname = _schema
AND t.typname = _table || ':' || v ) LOOP EXECUTE n;
END LOOP;
END;
$function$ ;
CREATE TYPE public.mytype1 AS (
a numeric(22,2),
b numeric(22,2),
c numeric(22,2),
d numeric(22,2)
);
CREATE TYPE public.mytype2 AS (
x date,
y numeric(22,2),
z bigint[]
);
CREATE TYPE public.sometype AS (
a date,
b date,
c mytype1,
d mytype1,
e mytype1,
f mytype1,
g mytype1,
h mytype1);
CREATE TYPE public.someenum AS ENUM (
'A',
'B'
);
DROP TABLE IF EXISTS public.mytable;
CREATE TABLE public.mytable (
dt date NOT NULL,
id int8 NOT NULL,
status int4 NOT NULL,
col1 public.mytype1 NULL,
col2 public.mytype2 NOT NULL,
a int4 NOT NULL,
b date NULL,
c date NULL,
d int4 NOT NULL,
e public.mytype1 NOT NULL,
f public.mytype1 NOT NULL,
g public.mytype1 NOT NULL,
h public.mytype2 NULL,
i public.mytype2 NULL,
j public.mytype2 NULL,
k date NOT NULL,
l int4 NOT NULL,
m int4 NOT NULL,
n public.mytype2 NULL,
o date NULL,
p int8 NULL,
q public.sometype NULL,
r public.sometype NULL,
s public.sometype NULL,
t public.sometype NULL,
u public.sometype NULL,
v public.sometype NULL,
w public.mytype1 NOT NULL,
x public.someenum NOT NULL,
y bool NOT NULL,
z public.mytype1 NOT NULL,
a1 int4 NOT NULL,
b1 numeric(22,2) NULL,
c1 sometype NULL,
d1 int4 NOT NULL,
e1 int4 NOT NULL,
f1 int4 NOT NULL,
g1 mytype1 NOT NULL,
h1 int4 NOT NULL,
i1 int4 NOT NULL,
j1 int4 NOT NULL,
k1 numeric(22,2) NULL,
l1 numeric(22,2) NULL,
m1 int8 NOT NULL,
n1 int4 NOT NULL,
o1 public.mytype1 NOT NULL,
p1 int4 NOT NULL,
q1 int8 NOT NULL,
r1 int8 NOT NULL,
s1 int8 NOT NULL
)
PARTITION BY LIST (dt);
SELECT public.add_partitions('public','mytable',array_agg(dt::date::text))
FROM generate_series('2020-11-15','2020-12-15','1 day'::INTERVAL) AS dates(dt);
INSERT INTO public.mytable
SELECT
--dt::date,
'2020-12-09'::date,
id,
(random()*4)::int+1 AS status,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1 AS col1,
(current_date-(random()*365)::int,random()*1000,array_fill(1, ARRAY[18]))::public.mytype2 AS col2,
0,current_date,current_date,0,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(current_date-(random()*365)::int,random()*1000,array_fill(1, ARRAY[18]))::mytype2,
(current_date-(random()*365)::int,random()*1000,array_fill(1, ARRAY[18]))::mytype2,
(current_date-(random()*365)::int,random()*1000,array_fill(1, ARRAY[18]))::mytype2,
current_date,0,0,
(current_date-(random()*365)::int,random()*1000,array_fill(1, ARRAY[18]))::mytype2,
current_date,0,
(current_date,current_date,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1)::public.sometype,
(current_date,current_date,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1)::public.sometype,
(current_date,current_date,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1)::public.sometype,
(current_date,current_date,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1)::public.sometype,
(current_date,current_date,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1)::public.sometype,
(current_date,current_date,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1)::public.sometype,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
'A'::public.someenum,
TRUE,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
0,0,
(current_date,current_date,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1)::public.sometype,
0,0,0,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
0,0,0,0,0,0,0,
(random()*1000,random()*1000,random()*1000,random()*1000)::public.mytype1,
0,0,0,0
FROM
generate_series(1::bigint,2000000,1) AS ids(id);
-- , generate_series('2020-11-15','2020-12-15','1 day'::INTERVAL) AS dates(dt);
ALTER TABLE public.mytable ADD PRIMARY KEY (id,dt);
--big&slow:
CREATE INDEX ix1 ON public.mytable (col1,col2) WHERE status IN (1,2,3,4);
--cost higher than ix1, so ix1 needs to be dropped before ix2 will be used (but faster than ix1):
CREATE INDEX ix2 ON public.mytable (COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0),COALESCE((col2).y, 0),col1,col2) WHERE status IN (1,2,3,4);
--cost too high, won't be used unless I drop ix1,ix2 and set enable_seqscan and enable_bitmapscan to off:
CREATE INDEX ix3 ON public.mytable (COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0),COALESCE((col2).y, 0)) WHERE status IN (1,2,3,4);
--ix4, ix5 and ix6 are just some variants I tried
CREATE INDEX ix4 ON public.mytable (((col1).a),((col1).b),((col1).c),((col1).d),((col2).y),col1,col2) WHERE status IN (1,2,3,4);
CREATE INDEX ix5 ON public.mytable (((col1).a),((col1).b),((col1).c),((col1).d),((col2).y)) WHERE status IN (1,2,3,4);
CREATE INDEX ix6 ON public.mytable (COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0),COALESCE((col2).y, 0)) INCLUDE (col1,col2) WHERE status IN (1,2,3,4);
VACUUM ANALYZE public.mytable;
VACUUM ANALYZE public."mytable:2020-12-09";
SELECT
pg_size_pretty(pg_total_relation_size('public."mytable:2020-12-09_col1_col2_idx"')) ix1,
pg_size_pretty(pg_total_relation_size('public."mytable:2020-12-09_coalesce_coalesce1_col1_col2_idx"')) ix2,
pg_size_pretty(pg_total_relation_size('public."mytable:2020-12-09_coalesce_coalesce1_idx"')) ix3,
pg_size_pretty(pg_total_relation_size('public."mytable:2020-12-09_a_b_c_d_y_col1_col2_idx"')) ix4,
pg_size_pretty(pg_total_relation_size('public."mytable:2020-12-09_a_b_c_d_y_idx"')) ix5,
pg_size_pretty(pg_total_relation_size('public."mytable:2020-12-09_coalesce_coalesce1_col1_col2_idx1"')) ix6,
pg_size_pretty(pg_total_relation_size('public."mytable:2020-12-09_pkey"')) pkey,
pg_size_pretty(pg_total_relation_size('mytable:2020-12-09')) part_with_ix,
pg_size_pretty(pg_relation_size('mytable:2020-12-09')) part;
--ix1 ix2 ix3 ix4 ix5 ix6 pkey part_with_ix part
--266 MB 280 MB 53 MB 327 MB 98 MB 280 MB 60 MB 6902 MB 3906 MB
--the query
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, SETTINGS)
SELECT
SUM(COALESCE((col1).a + (col1).b + (col1).c + (col1).d, 0)) AS val1,
SUM(COALESCE((col2).y, 0)) AS val2
FROM
public."mytable:2020-12-09"
WHERE
status IN (1,
2,
3,
4);
