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*)
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:
Then:
Some details:
So all time is spent in the Postgres scan.
Some perf counters: