Almost no performance gain / What am I doing wrong

[JamesInform]

Hi,

I have built citus extension from the github master for Mac. Server is PostgreSQL 13.2 on Mac using "Postgresapp.com"'s Mac App.

I have done a little performance testing based on the following blog: Citus 10 brings columnar compression to Postgres

I modified the script to create tables with 100 million records. I have pg_prewarm as a preloaded library. First run columnar table it a bit faster than row table (but only about 25%). But in the second run citus is much slower (11 seconds columnar to 4 seconds row)

Please tell me if I missed something?

Here comes the script run including timing:

postgres=# --
postgres=# -- Like all Postgres extensions, citus needs to be enabled
postgres=# -- for this database.
postgres=# --
postgres=# -- First check which citus version is available
postgres=# \timing
Timing is on.
postgres=# select * from pg_available_extensions where name = 'citus';
 name  | default_version | installed_version |          comment
-------+-----------------+-------------------+----------------------------
 citus | 10.1-1          |                   | Citus distributed database
(1 row)

Time: 4,466 ms
postgres=#
postgres=# CREATE EXTENSION citus;
CREATE EXTENSION
Time: 200,715 ms
postgres=#
postgres=# --
postgres=# -- Make an ordinary table, which is row-based storage, and a
postgres=# -- columnar table. Both with 100 Million records.
postgres=# --
postgres=# CREATE TABLE simple_row(i INT8);
ERROR:  relation "simple_row" already exists
Time: 10,074 ms
postgres=# CREATE TABLE simple_columnar(i INT8) USING columnar;
CREATE TABLE
Time: 1,660 ms
postgres=#
postgres=# --
postgres=# -- Columnar tables act like row tables
postgres=# --
postgres=# INSERT INTO simple_row SELECT generate_series(1,100000000);
INSERT 0 100000000
Time: 126576,191 ms (02:06,576)
postgres=# INSERT INTO simple_columnar SELECT generate_series(1,100000000);
INSERT 0 100000000
Time: 36331,625 ms (00:36,332)
postgres=#
postgres=# SELECT AVG(i) FROM simple_row;
          avg
-----------------------
 50000000.500000000000
(1 row)

Time: 15314,327 ms (00:15,314)
postgres=#
postgres=# SELECT AVG(i) FROM simple_columnar;
          avg
-----------------------
 50000000.500000000000
(1 row)

Time: 11693,808 ms (00:11,694)
postgres=# SELECT AVG(i) FROM simple_row;
          avg
-----------------------
 50000000.500000000000
(1 row)

Time: 4322,531 ms (00:04,323)
postgres=# SELECT AVG(i) FROM simple_columnar;
          avg
-----------------------
 50000000.500000000000
(1 row)

Time: 11962,963 ms (00:11,963)
postgres=# explain SELECT AVG(i) FROM simple_row;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=964311.72..964311.73 rows=1 width=32)
   ->  Gather  (cost=964311.50..964311.71 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=963311.50..963311.51 rows=1 width=32)
               ->  Parallel Seq Scan on simple_row  (cost=0.00..859144.80 rows=41666680 width=8)
(5 rows)

Time: 0,631 ms
postgres=# explain SELECT AVG(i) FROM simple_columnar;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Aggregate  (cost=263967.59..263967.60 rows=1 width=32)
   ->  Custom Scan (ColumnarScan) on simple_columnar  (cost=0.00..13967.59 rows=100000000 width=8)
(2 rows)

Time: 1,442 ms

[jeff-davis]

Columnar shines when IO is the bottleneck, or when memory or storage is scarce.

For 100M rows, I see table sizes of 3.5GB for simple_row, and 115MB for simple_columnar. Columnar is compressing well, but both of those are easily small enough to fit in memory, so it's not an IO-bound workload. Also keep in mind that local SSDs are fast, so you are more likely to see an IO-bound workload when using, e.g., managed disks.

[JamesInform]

Well, I see.

But why is the query to simple_row more than 2 times faster (4322,531 ms row IN CONTRAST TO 11962,963 ms columnar), although only 2 parallel workers are involved with the row version?

Is there something like a rule of thumb beside the IO-bound workload on when to use citus? It seems that using citus only makes sense, when you can't throw more ram into the system.

I expected citus to make such easy queries like in my example to return results within millisecond. This is what one expects from columnar in memory solutions like in SQL Server and others.

Or is the citus extension not comparable to other columnar solutions?

[marcocitus]

But why is the query to simple_row more than 2 times faster (4322,531 ms row IN CONTRAST TO 11962,963 ms columnar), although only 2 parallel workers are involved with the row version?

Most likely due to the cost of decompression.

You could try using SELECT create_distributed_table('simple_columnar', 'i') to get more parallelism.

I expected citus to make such easy queries like in my example to return results within millisecond. This is what one expects from columnar in memory solutions like in SQL Server and others.

The PostgreSQL executor processes tuples row-by-row regardless of the storage format, hence the amount of computational work is similar when using columnar storage except for decompression. Longer term, it may be possible to implement vectorized execution in PostgreSQL, but for now our main goal is to reduce I/O for queries on large tables.

[JamesInform]

That makes thing clear.

Thanks