Postgres 12 partitions performance by coll type

Question

I do performance tests on posgtres 12 with partitions and a lot of data. Each partition contains only one site with 400k of rows and I generated around 1k of partitions tables;

For the first test suite I use UUID for ids, but I thought that if I change the ids types to bigint much less space will be used, so higher performance. After tables are populated I run the below select a hundred times with different data

SELECT SUM(amount),
FROM   test_table
WHERE  date >= '2021-02-06'
AND date <= '2021-02-07'
AND site IN ('c3b3771c-4b48-41a9-88eb-4c47d1630644', 'cbb11cdc-cd31-4da2-b14e-9ef878ce03c5', '2609ac86-995b-4320-a3b7-46ba175aa5e2') // randomly picked from site pool
GROUP  BY site
ORDER  BY site;

UUID test suite no date index:

CREATE TABLE public.test_table
(
    id UUID   NOT NULL,
    site UUID,
    archive UUID,
    location UUID,
    col_1 UUID,
    col_2 UUID,
    col_3 UUID,
    amount numeric(8,2)
    date timestamp with time zone,
    ....
) PARTITION BY LIST (site);


CREATE TABLE test_table_${site} PARTITION OF test_table  FOR VALUES IN ('${site}');

One table size: "265 MB"

BIGINT test suite no date index:

CREATE TABLE public.test_table
(
    id bigint   NOT NULL,
    site bigint,
    archive bigint,
    location bigint,
    col_1 bigint,
    col_2 bigint,
    col_3 bigint,
    amount numeric(8,2)
    date timestamp with time zone,
    ...
) PARTITION BY LIST (site);


CREATE TABLE test_table_${site} PARTITION OF test_table  FOR VALUES IN ('${site}');

One table size: "118 MB"

Test results

UUID test results (ms) for 100 serial selects
median          1,425.00
percentile 95%  1,930.05

BIGINT test results (ms) for 100 serial selects
median          4,456.00
percentile 95%  9,037.50

Same Explain:

UUID

"GroupAggregate  (cost=61944.56..61947.03 rows=90 width=88)"
"  Group Key: test_table_c3b3771c_4b48_41a9_88eb_4c47d1630644.site"
"  ->  Sort  (cost=61944.56..61944.78 rows=90 width=48)"
"        Sort Key: test_table_c3b3771c_4b48_41a9_88eb_4c47d1630644.site"
"        ->  Gather  (cost=1000.00..61941.63 rows=90 width=48)"
"              Workers Planned: 3"
"              ->  Parallel Append  (cost=0.00..60932.63 rows=30 width=48)"
"                    ->  Parallel Seq Scan on test_table_c3b3771c_4b48_41a9_88eb_4c47d1630644  (cost=0.00..20311.16 rows=10 width=48)"
"                          Filter: ((date_fiscal >= '2021-02-06 00:00:00+00'::timestamp with time zone) AND (date_fiscal <= '2021-02-07 00:00:00+00'::timestamp with time zone) AND (site = ANY ('{c3b3771c-4b48-41a9-88eb-4c47d1630644,cbb11cdc-cd31-4da2-b14e-9ef878ce03c5,2609ac86-995b-4320-a3b7-46ba175aa5e2}'::uuid[])))"
"                    ->  Parallel Seq Scan on test_table_cbb11cdc_cd31_4da2_b14e_9ef878ce03c5  (cost=0.00..20311.16 rows=10 width=48)"
"                          Filter: ((date_fiscal >= '2021-02-06 00:00:00+00'::timestamp with time zone) AND (date_fiscal <= '2021-02-07 00:00:00+00'::timestamp with time zone) AND (site = ANY ('{c3b3771c-4b48-41a9-88eb-4c47d1630644,cbb11cdc-cd31-4da2-b14e-9ef878ce03c5,2609ac86-995b-4320-a3b7-46ba175aa5e2}'::uuid[])))"
"                    ->  Parallel Seq Scan on test_table_2609ac86_995b_4320_a3b7_46ba175aa5e2  (cost=0.00..20310.16 rows=10 width=48)"
"                          Filter: ((date_fiscal >= '2021-02-06 00:00:00+00'::timestamp with time zone) AND (date_fiscal <= '2021-02-07 00:00:00+00'::timestamp with time zone) AND (site = ANY ('{c3b3771c-4b48-41a9-88eb-4c47d1630644,cbb11cdc-cd31-4da2-b14e-9ef878ce03c5,2609ac86-995b-4320-a3b7-46ba175aa5e2}'::uuid[])))"

BIGINT

"Finalize GroupAggregate  (cost=47951.35..47954.22 rows=21 width=80)"
"  Group Key: test_table_121.site"
"  ->  Gather Merge  (cost=47951.35..47953.63 rows=18 width=80)"
"        Workers Planned: 3"
"        ->  Partial GroupAggregate  (cost=46951.31..46951.48 rows=6 width=80)"
"              Group Key: test_table_121.site"
"              ->  Sort  (cost=46951.31..46951.33 rows=6 width=40)"
"                    Sort Key: test_table_121.site"
"                    ->  Parallel Append  (cost=0.00..46951.24 rows=6 width=40)"
"                          ->  Parallel Seq Scan on test_table_121  (cost=0.00..15651.09 rows=2 width=40)"
"                                Filter: ((date_fiscal >= '2021-02-06 00:00:00+00'::timestamp with time zone) AND (date_fiscal <= '2021-02-07 00:00:00+00'::timestamp with time zone) AND (site = ANY ('{121,122,242}'::bigint[])))"
"                          ->  Parallel Seq Scan on test_table_242  (cost=0.00..15651.09 rows=2 width=40)"
"                                Filter: ((date_fiscal >= '2021-02-06 00:00:00+00'::timestamp with time zone) AND (date_fiscal <= '2021-02-07 00:00:00+00'::timestamp with time zone) AND (site = ANY ('{121,122,242}'::bigint[])))"
"                          ->  Parallel Seq Scan on test_table_122  (cost=0.00..15649.02 rows=2 width=40)"
"                                Filter: ((date_fiscal >= '2021-02-06 00:00:00+00'::timestamp with time zone) AND (date_fiscal <= '2021-02-07 00:00:00+00'::timestamp with time zone) AND (site = ANY ('{121,122,242}'::bigint[])))"

How is it possible to have such a big difference on select time with a smaller volume of data? or maybe I made a mistake during the tests.

Thanks in advance!

Answer 1

My guess would be the fallout comes from how you're running your tests. I believe it is possible to run into heavily favored test parameters for one set of your data vs the other after inspecting your example test query. Specifically your WHERE clause:

WHERE  date >= '2021-02-06'
AND date <= '2021-02-07'
AND site IN ('c3b3771c-4b48-41a9-88eb-4c47d1630644', 'cbb11cdc-cd31-4da2-b14e-9ef878ce03c5', '2609ac86-995b-4320-a3b7-46ba175aa5e2') // randomly picked from site pool

Without seeing the equivalent test query you run for your BIGINT set of data, it's hard to compare, but I see the potential for unbalanced tests to occur due to the following possible reasons:

1. The date range you're using might heavily favor how the data partitions by the UUID site field instead of the BIGINT site field, especially since BIGINT is assumptively going to be more contiguous in values than a UUID.

2. The way you're selecting values for the site predicate in your WHERE clause could also be favored towards your UUID test's partitions as opposed to the BIGINT test. It looks like you said you're randomly picking them from the site pool, but this will really depend on how random it really is, coupled with the fact again the ordering of your partitions for a UUID are going to be much different than the ordering of those partitions for a BIGINT. Again without seeing your equivalent example query for the BIGINT test, and how you're randomly selecting that predicate in both cases, it's hard to say how much of an impact this has.

In summary, I don't see anything else that would warrant much difference in outcomes, which leaves me to theorize the above. Unfortunately if it is an issue of how you're testing your data, such as I suspect, then there won't be any reputable sources to provide you an answer around that. Rather you should look to simplify your tests first to eliminate the potential variables causing weighted outcomes, and work your way up from there.

For example, maybe start with bounds testing by hand picking your first partition value, last partition value, a close to the middle partition value, and running your test for all partitions, without the date predicate in any of those cases, to eliminate the potential sources of error I mentioned above. Then introduce a date range predicate you know encompasses an equal number of partitions with equal numbers of rows per partition, for the specific site predicates you're testing with. Essentially controlled tests will provide you with more meaningful information here than random ones.

Answer 2

I run same tests on same data

SELECT SUM(amount),
FROM   test_table
WHERE  date betwen (day | week | month)
AND site IN ('site id 1', 'site id 2', 'site id 3') // randomly picked from site pool
GROUP  BY site
ORDER  BY site;

Test 1-day interval, 3 sites ids:

UUID test results (ms) for 100 serial selects
median          1,425.00
percentile 95%  1,930.05

BIGINT test results (ms) for 100 serial selects
median          1,116.50
percentile 95%  1,641.55

Test 1-week interval, 3 sites ids:

UUID test results (ms) for 100 serial selects
median          1,406.50
percentile 95%  1,849.10

BIGINT test results (ms) for 100 serial selects
median          1,147.00
percentile 95%  1,563.75

Test 1-month interval, 3 sites ids:

UUID test results (ms) for 100 serial selects
median          1,446.00
percentile 95%  1,876.05

BIGINT test results (ms) for 100 serial selects
median          1,146.50
percentile 95%  1,430.15

I received a more explicit difference when I added 10 site ids in the select:

Test 1-day interval, 10 sites ids:

UUID test results (ms) for 100 serial selects
median          4,431.00
percentile 95%  5,237.55

BIGINT test results (ms) for 100 serial selects
median          3,607.50
percentile 95%  4,220.05

Test 1-week interval, 10 sites ids:

UUID test results (ms) for 100 serial selects
median          4,458.50
percentile 95%  5,308.10

BIGINT test results (ms) for 100 serial selects
median          3,405.50
percentile 95%  4,193.55

Test 1-month interval, 10 sites ids:

UUID test results (ms) for 100 serial selects
median          4,533.50
percentile 95%  5,540.70

BIGINT test results (ms) for 100 serial selects
median          3,549.00
percentile 95%  4,162.90

I think the problem was that the tests were run just before the data was generated and postgres server possibly run some data reorganization tasks