Simple scan of native table is 2x slower with pg_duckdb enabled

[lhofhansl]

Not sure what I was expecting. It seems when scanning a Postgres native table, all time is spent in reading from it, and - at least in this simple case - dwarfs any vectorization in DuckDB.

Feel free to close if this is expected.

I created a simple table:

=> \d x
Table "x"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
y      | numeric |           |          |
z      | numeric |           |          |

=> select count(*) from x;
count
----------
16777216
(1 row)

=> analyze x;
ANALYZE

=> set max_parallel_workers=0;
SET

Then:

=> select sum(z) from x;
sum
--------------------------------
8385930.2158780710128370101138
(1 row)

Time: 3167.987 ms (00:03.168)

=> set duckdb.execution to true;
SET

=> select sum(z) from x;
sum
-------------------
8385930.215876569
(1 row)

Time: 6474.084 ms (00:06.474)

Some details:

=> explain select sum(z) from x;

QUERY PLAN
------------------------------------------------------------------------------------------------------------
Custom Scan (DuckDBScan)  (cost=0.00..0.00 rows=0 width=0) (actual time=6483.263..6483.289 rows=1 loops=1)
DuckDB Execution Plan:

┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYZE  SELECT sum(z) AS sum    FROM x
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││         Total Time: 6.44s         ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌───────────────────────────┐
│         QUERY_ROOT        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        Cardinality:       │
│             0             │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          Timing:          │
│           0.00s           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        Cardinality:       │
│             0             │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          Timing:          │
│           0.00s           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    UNGROUPED_AGGREGATE    │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        Aggregates:        │
│          sum(#0)          │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        Cardinality:       │
│             1             │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          Timing:          │
│           0.04s           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        Projections:       │
│             z             │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│   Estimated Cardinality:  │
│          16777087         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        Cardinality:       │
│          16777216         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          Timing:          │
│           0.01s           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│     POSTGRES_SEQ_SCAN     │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        Projections:       │
│             z             │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│   Estimated Cardinality:  │
│          16777087         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        Cardinality:       │
│          16777216         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          Timing:          │
│           6.38s           │
└───────────────────────────┘


Planning Time: 10.024 ms
Execution Time: 6484.588 ms
(78 rows)

So all time is spent in the Postgres scan.

Some perf counters:

12.96%  pg_duckdb.so                             [.] pgduckdb::InsertTupleIntoChunk(duckdb::DataChunk&, duckdb::shared_ptr<pgduckdb::PostgresScanGlobalState, true>, duckdb::shared_ptr<pgduckdb::PostgresScanLo...
9.69%  pg_duckdb.so                             [.] pgduckdb::HeapReader::ReadPageTuples(duckdb::DataChunk&)
6.42%  pg_duckdb.so                             [.] duckdb::shared_ptr<duckdb::TemplatedValidityData<unsigned long>, true>::AssertNotNull(bool)
6.03%  pg_duckdb.so                             [.] pgduckdb::ConvertPostgresToDuckValue(unsigned long, duckdb::Vector&, unsigned long)
5.71%  pg_duckdb.so                             [.] pgduckdb::DetoastPostgresDatum(varlena*, bool*)

[wuputah]

Right, this is not unexpected at this time. The main purpose of the Postgres scan is not for performance, but utility. Given the current implementation, it's not expected that we can scan and process an entire Postgres table faster than Postgres can. That said there are still many things that can still be done to improve the performance of the Postgres scan, the implementation is still quite young.

[JelteF]

Totally agreed that this is expected in this case. But there are definitely cases where using duckdb execution will greatly outperform postgres its execution. This only happens though when the gains of using duckdb for the actual data processing are large enough to overcome the overhead of moving postgres data into duckdb. A very simple example where using duckdb.execution = true results in an 8x speedup on my machine is the following:

\timing on

drop table if exists t1, t2;
create table t1(a int);
create table t2(b int);
INSERT INTO t1 SELECT generate_series(1, 10000);
INSERT INTO t2 SELECT generate_series(1, 10000);
ANALYZE t1, t2;
set duckdb.execution = true;
SELECT count(*) FROM t1, t2 WHERE t1.a = t2.b or t1.a = t2.b + 1;
set duckdb.execution = false;
SELECT count(*) FROM t1, t2 WHERE t1.a = t2.b or t1.a = t2.b + 1;

[mkaruza]

Right, as @wuputah pointed we are not expecting that reading heap tables would be faster (especially in this simple query) because there is overhead of copying values to duckdb vector for processing (for example). Also, you can now read heap tables through multiple threads but we have global lock to control access to heap tables page functions (which are not thread safe). There are some ideas how to improve reading/fetching heap pages (#63) but this is still work in progress.

[ggam]

@wuputah has this changed after #477 got merged? Are there any guidelines on when we can expect DuckDB execution to be slower than regular Postgres?

I understand as of now we cannot simply set duckdb.force_execution=true and expect it to just work. Having some documentation on known quirks would be really help adoption.