You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@arrow.apache.org by "mcrumiller (via GitHub)" <gi...@apache.org> on 2024/04/24 13:09:27 UTC
[I] Connecting to postgres (Greenplum) database very slow [arrow-adbc]
mcrumiller opened a new issue, #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755
### What happened?
When I perform a `dbapi.connect(uri)` call to a postgres 9.4.26 database (Greenplum 6.24.3), it takes upwards of two minutes to connect. Compare this to SQLAlchemy which connects in about one second:
```python
from time import perf_counter
import sqlalchemy as sa
from adbc_driver_postgresql import dbapi
uri = "..."
# using SQLAlchemy
t = perf_counter()
conn = sa.create_engine(uri).connect()
print(f"SQLAlchemy connected in {perf_counter() - t:0.2f}s.")
# verify result
print(conn.execute(sa.text("SELECT 1")).fetchall())
conn.close()
# using adbc
t = perf_counter()
conn = dbapi.connect(uri)
print(f"ADBC connected in {perf_counter() - t:0.2f}s.")
# verify result
with conn.cursor() as cur:
cur.execute("SELECT 1")
print(cur.fetchall())
```
```
SQLAlchemy connected in 1.31s.
[(1,)]
ADBC connected in 153.66s.
[(1,)]
```
My uri is of the form `postgresl://username:password@server:port/database". Is there another connection setting I'm missing that's causing the huge connection delay?
### How can we reproduce the bug?
_No response_
### Environment/Setup
Item | Version
----------------|----------
OS | Windows 10 Enterprise
postgres driver | 0.11.0
Postgres server | 9.4.26
Greenplum | 6.24.3
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "vaefremov95 (via GitHub)" <gi...@apache.org>.
vaefremov95 commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-5713344336
@lidavidm
Hi!
Thank you for your work on this!
I’ll definitely test this new feature once the new release is available.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-5706433843
I've merged that PR for the next release: you'll be able to set `adbc.postgresql.type_resolver_mode` to `builtin` before connecting to try it out. The driver won't query the system tables at all, so there won't be a performance hit. Compatibility relies on systems using the same OIDs as Postgres (all the ones tested apparently do). Composite types etc. will not be supported. Follow #4782 for further improvements.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "iangow (via GitHub)" <gi...@apache.org>.
iangow commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4139386782
Sorry. Is this what you want?
```sql
wrds=> WITH query AS (
wrds(> SELECT
wrds(> attrelid,
wrds(> attname,
wrds(> atttypid
wrds(> FROM
wrds(> pg_catalog.pg_attribute
wrds(> ORDER BY
wrds(> attrelid, attnum)
wrds->
wrds-> SELECT COUNT(*) FROM query;
count
---------
3564104
(1 row)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "paleolimbot (via GitHub)" <gi...@apache.org>.
paleolimbot commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4131255483
I wonder if in Python our bindings code is creating the `AdbcDatabase` twice by accident (or by necessity for something involving a context manager?)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-2456063286
This is probably not being worked on anytime soon, unfortunately (at least from my end); contributions are welcome
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Connecting to postgres (Greenplum) database very slow [arrow-adbc]
Posted by "mcrumiller (via GitHub)" <gi...@apache.org>.
mcrumiller commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-2074991467
Hi @lidavidm, thanks for your help. I'm new to ADBC so apologies that I'm not very well-versed here. I'm in a corporate environment so unfortunately I don't have any admin access to the Greenplum server. We did just upgrade from 5.x to 6.x last night so this is a pretty "fresh install," however I saw the same thing a few months ago on 5.x.
Is there anything I can do to help diagnose, perhaps export some logging or increase verbosity?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "vaefremov95 (via GitHub)" <gi...@apache.org>.
vaefremov95 commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-5026011913
@lidavidm @Mandukhai-Alimaa
Hi!
I did a small research around **point 2** (the query optimization I suggested earlier), can be useful here. I ran the optimized queries against a range of Postgres-compatible databases to check exactly the compatibility concern you raised.
The optimized queries are compatible with every engine I could test that implements `pg_inherits` - including all real-Postgres extensions/forks **and** CockroachDB's emulated catalog. The only regression is on databases that don't implement `pg_inherits` at all - CrateDB.
### Methodology
For each database I ran two checks:
1. **Executability** - run the optimized queries literally against the full catalog. This is the core compatibility question.
2. **Correctness** - create a representative schema and compare the **original (unfiltered)** query vs the **optimized** one, restricted to that schema:
- regular table, view, materialized view, composite type,
- a range-**partitioned** table with two child partitions (exercises the `pg_inherits` filter).
The optimized query is correct if it drops **exactly** what it should - partition children (via `pg_inherits`), system columns (`attnum <= 0`), and non-table relkinds (indexes) - while keeping the real columns of real relations; and for query B keeps base types (`typrelid = 0`) and rowtypes of real relations while dropping rowtypes of partition children.
### Results
| Database | Version | Executable | Regression vs original? | Verdict |
|---|---|---|---|---|
| PostgreSQL | 16 | ✅ | no | ✅ OK (baseline) |
| Citus | PG 18.4 | ✅ | no | ✅ OK |
| TimescaleDB | PG 16.14 | ✅ | no | ✅ OK |
| ParadeDB | PG 18.4 | ✅ | no | ✅ OK |
| YugabyteDB | PG 15.12 | ✅ | no | ✅ OK |
| CockroachDB | 26.2 | ✅ | no | ✅ OK |
| CedarDB | v2026-07-10 (PG 16.3-compat) | ✅ | no | ✅ OK (see notes) |
| **CrateDB** | 6.3.5 | ❌ | **yes** | ❌ **FAIL** |
| Greenplum | GP6 | ✅ | no | ✅ OK (verified in production) |
I couldn't run **Neon**, **AlloyDB** or **Yellowbrick** locally (cloud-only / no public build). Neon and AlloyDB use a real Postgres catalog, so I'd expect them to behave like stock PostgreSQL, but I haven't verified that.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Connecting to postgres (Greenplum) database very slow [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-2078581119
Yeah, I'm currently thinking we can cache things up front when there's not many types, and otherwise perhaps speculatively cache part of the info and load the rest on demand as queries are run. (And possibly provide a "please cache them all right now" control.) I'm not happy about the potential variance but I suppose that's the downside of needing to know the types to parse the binary copy format.
As for memory usage, I think we can explore switching to an LRU cache or something along those lines later if it's a concern.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "iangow (via GitHub)" <gi...@apache.org>.
iangow commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4131018958
> That's odd because the `adbcpostgresql` driver is the exact same. What versions do you have installed?
It turns out that its 21-23s in Python, and about 9s for R (pretty consistently in both cases). I think the workflow was different in R, so I wasn't incurring this cost every time.
All the time is in this part `db = adbc_driver_postgresql.connect(uri)` or equivalent in R.
```python
db2pq % uv run python -c "import adbc_driver_postgresql; print(adbc_driver_postgresql.__version__)"
1.10.0
db2pqiangow@Ians-MacBook-Pro db2pq %
```
```r
> packageDescription("adbcpostgresql")[c("Package", "Version")]
+ packageDescription("adbcdrivermanager")[c("Package", "Version")]
$Package
[1] "adbcpostgresql"
$Version
[1] "0.22.0.9000"
$Package
[1] "adbcdrivermanager"
$Version
[1] "0.22.0.9000"
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "iangow (via GitHub)" <gi...@apache.org>.
iangow commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4138198276
> I wonder if in Python our bindings code is creating the `AdbcDatabase` twice by accident (or by necessity for something involving a context manager?)
It is consistently 20 seconds versus 9 (I did it so many times!), so almost exactly double. I did a little ADBC benchmarking here: https://iangow.github.io/notes/db2pq_test/local_engine_benchmark.html. (I don't really know what I'm doing, I just have an idea of what I *want* to do … make Parquet files.)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4139461868
Thanks! A bare Postgres instance only has 3000 rows, so 3.5 million rows here is quite a bit...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "fwojciec (via GitHub)" <gi...@apache.org>.
fwojciec commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-3193941046
Not sure this is an option for ADBC (likely not, because it comes with at least one somewhat significant tradeoff) but maybe something that could be opted into via config? It's possible to do schema introspection at query time with minimal overhead (benchmarked at ~178 microseconds per query on local connections) using PREPARE statements - example Go implementation from one of my projects using Go's pgx as the postgres driver:
```go
// GetQueryMetadata uses PREPARE to extract column metadata without executing the query.
func (p *Pool) GetQueryMetadata(ctx context.Context, conn *pgxpool.Conn, sql string) (*arrow.Schema, []uint32, error) {
// Generate a unique statement name to avoid collisions in concurrent usage
stmtName := fmt.Sprintf("pgarrow_meta_%p", conn)
sd, err := conn.Conn().Prepare(ctx, stmtName, sql)
if err != nil {
return nil, nil, fmt.Errorf("failed to prepare statement for metadata discovery: %w", err)
}
defer func() {
_, _ = conn.Conn().Exec(ctx, "DEALLOCATE "+stmtName)
}()
if len(sd.Fields) == 0 {
return nil, nil, fmt.Errorf("query returned no columns - Arrow conversion requires queries that return at least one column")
}
columns := make([]ColumnInfo, len(sd.Fields))
fieldOIDs := make([]uint32, len(sd.Fields))
for i, field := range sd.Fields {
columns[i] = ColumnInfo{
Name: field.Name,
OID: field.DataTypeOID,
}
fieldOIDs[i] = field.DataTypeOID
}
schema, err := CreateSchema(columns)
if err != nil {
return nil, nil, &SchemaError{
Columns: columns,
Err: err,
}
}
return schema, fieldOIDs, nil
}
```
The main tradeoff is that this method doesn't detect column nullability - which I've personally found to be a good tradeoff since there's no performance benefit to knowing nullability when converting data (as far as I know) and the query engine I was using at the time (DuckDB) treats all Arrow data as nullable anyway (which arguably also follows Arrow's design philosophy).
Just sharing because it might be something to consider in this context - no idea how to write it in C++ though... :/
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Connecting to postgres (Greenplum) database very slow [arrow-adbc]
Posted by "mcrumiller (via GitHub)" <gi...@apache.org>.
mcrumiller commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-2075049611
9s for the first, 261ms for the second.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Connecting to postgres (Greenplum) database very slow [arrow-adbc]
Posted by "mcrumiller (via GitHub)" <gi...@apache.org>.
mcrumiller commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-2075082956
First query: 6,667,495 rows in 97.72s.
Second query: 141,851 rows in 11.59s.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-5580399894
Sorry for the delay here...I've added testing of most of these backends to CI. I've also investigated, as a first step, only trying to optimize kColumnsQuery in https://github.com/apache/arrow-adbc/issues/4749. At least with Postgres, I'm not setting that these alternative queries actually speed things up. Possibly this depends on the exact structure of your database? Or, the speedups actually come from the other query.
But it seems more promising to (1) hardcode the base OIDs (2) lazily load this information, in terms of latency/memory usage (ideally queries on "base" types would never need to load this information at all).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Connecting to postgres (Greenplum) database very slow [arrow-adbc]
Posted by "davlee1972 (via GitHub)" <gi...@apache.org>.
davlee1972 commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-2078329153
Hmm I got different results for my Greenplum test.. 6 seconds for the first query..
I'm calling cursor.fetch_arrow_table() to pull results.
>>> a.num_rows
3028922
>>> total
datetime.timedelta(seconds=6, microseconds=607360)
>>> b.num_rows
120072
>>> total2
datetime.timedelta(microseconds=469822)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "iangow (via GitHub)" <gi...@apache.org>.
iangow commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4141529498
In case it's helpful for any replication analysis you might do, I provide some details on the empirical distribution of the underlying types for the 3 million rows for that query here (this is R code, but hopefully its pretty transparent what SQL it is running):
``` r
library(DBI)
library(dplyr, warn.conflicts = FALSE)
library(dbplyr, warn.conflicts = FALSE)
db <- dbConnect(RPostgres::Postgres(),
host = "wrds-pgdata.wharton.upenn.edu",
port = 9737L,
user = Sys.getenv("WRDS_ID"),
dbname ="wrds",
bigint = "integer")
pg_attribute_tbl <-
tbl(db, in_schema("pg_catalog", "pg_attribute")) |>
select(attrelid, attnum, attname, atttypid)
pg_type_tbl <-
tbl(db, in_schema("pg_catalog", "pg_type")) |>
select(
oid,
typname,
typtype,
typreceive,
typsend,
typbasetype,
typrelid,
typarray
)
attribute_types <-
pg_attribute_tbl |>
left_join(pg_type_tbl, by = c("atttypid" = "oid")) |>
mutate(
driver_bucket = case_when(
sql("typreceive::text = 'array_recv'") ~ "array (resolved from pg_type/typarray)",
typrelid != 0L ~ "composite/record (needs pg_attribute via typrelid)",
typbasetype != 0L ~ "domain (resolved from pg_type via typbasetype)",
TRUE ~ "direct pg_type lookup"
)
)
attribute_types |>
count(driver_bucket, sort = TRUE) |>
collect()
#> # A tibble: 3 × 2
#> driver_bucket n
#> <chr> <int>
#> 1 direct pg_type lookup 3563772
#> 2 domain (resolved from pg_type via typbasetype) 683
#> 3 array (resolved from pg_type/typarray) 80
attribute_types |>
count(driver_bucket, typname, sort = TRUE) |>
collect() |>
print(n = Inf)
#> # A tibble: 54 × 3
#> driver_bucket typname n
#> <chr> <chr> <int>
#> 1 direct pg_type lookup varchar 1062019
#> 2 direct pg_type lookup float8 707320
#> 3 direct pg_type lookup numeric 319186
#> 4 direct pg_type lookup int4 274798
#> 5 direct pg_type lookup text 258006
#> 6 direct pg_type lookup xid 159289
#> 7 direct pg_type lookup cid 159278
#> 8 direct pg_type lookup date 137778
#> 9 direct pg_type lookup int2 125724
#> 10 direct pg_type lookup time 117338
#> 11 direct pg_type lookup oid 106590
#> 12 direct pg_type lookup tid 79639
#> 13 direct pg_type lookup int8 37160
#> 14 direct pg_type lookup bytea 13265
#> 15 direct pg_type lookup timestamp 5437
#> 16 domain (resolved from pg_type via typbasetype) sql_identifier 448
#> 17 direct pg_type lookup bool 428
#> 18 direct pg_type lookup name 153
#> 19 domain (resolved from pg_type via typbasetype) character_data 106
#> 20 domain (resolved from pg_type via typbasetype) cardinal_number 76
#> 21 direct pg_type lookup tsvector 68
#> 22 direct pg_type lookup timestamptz 65
#> 23 domain (resolved from pg_type via typbasetype) yes_or_no 50
#> 24 direct pg_type lookup char 45
#> 25 direct pg_type lookup regproc 36
#> 26 direct pg_type lookup cstring 34
#> 27 array (resolved from pg_type/typarray) _text 30
#> 28 direct pg_type lookup jsonb 17
#> 29 direct pg_type lookup pg_lsn 16
#> 30 direct pg_type lookup pg_node_tree 16
#> 31 direct pg_type lookup float4 14
#> 32 direct pg_type lookup anyarray 14
#> 33 array (resolved from pg_type/typarray) _aclitem 14
#> 34 array (resolved from pg_type/typarray) _float4 11
#> 35 array (resolved from pg_type/typarray) _oid 9
#> 36 direct pg_type lookup regclass 8
#> 37 direct pg_type lookup interval 8
#> 38 direct pg_type lookup oidvector 6
#> 39 direct pg_type lookup int2vector 6
#> 40 array (resolved from pg_type/typarray) _name 4
#> 41 array (resolved from pg_type/typarray) _int2 3
#> 42 domain (resolved from pg_type via typbasetype) time_stamp 3
#> 43 array (resolved from pg_type/typarray) _char 3
#> 44 direct pg_type lookup <NA> 2
#> 45 direct pg_type lookup pg_dependencies 2
#> 46 direct pg_type lookup inet 2
#> 47 direct pg_type lookup pg_ndistinct 2
#> 48 array (resolved from pg_type/typarray) _regtype 2
#> 49 array (resolved from pg_type/typarray) _float8 2
#> 50 direct pg_type lookup tsquery 1
#> 51 direct pg_type lookup pg_mcv_list 1
#> 52 direct pg_type lookup regtype 1
#> 53 array (resolved from pg_type/typarray) _bool 1
#> 54 array (resolved from pg_type/typarray) _pg_statistic 1
```
<sup>Created on 2026-03-27 with [reprex v2.1.1](https://reprex.tidyverse.org)</sup>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "vaefremov95 (via GitHub)" <gi...@apache.org>.
vaefremov95 commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4799090052
Hi! I ran into the same problem.
In my database pg_catalog.pg_attribute has about 14 million rows. On connect the driver runs this query:
`SELECT attrelid, attname, atttypid FROM pg_catalog.pg_attribute ORDER BY attrelid, attnum`
What I see:
1. the query takes about 2 minutes;
2. the whole catalog is loaded into memory - RAM jumps to about 3 GB right after connect, before reading any data;
3. the ORDER BY creates about 2 GB of spill files on the server while the query runs.
Is there any update on the status of this issue? Thanks!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "Mandukhai-Alimaa (via GitHub)" <gi...@apache.org>.
Mandukhai-Alimaa commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-5025697323
Sounds good. I will get on it
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Connecting to postgres (Greenplum) database very slow [arrow-adbc]
Posted by "mcrumiller (via GitHub)" <gi...@apache.org>.
mcrumiller commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-2075393348
@lidavidm it appears that ADBC downloads the data type for every single column in the database, even if there are thousands of tables? Is there a way to put this off until query time and only get the data types of the needed columns?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Connecting to postgres (Greenplum) database very slow [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-2075949500
> @lidavidm it appears that ADBC downloads the data type for every single column in the database, even if there are thousands of tables? Is there a way to put this off until query time and only get the data types of the needed columns?
Well that would add additional latency in front of each query. We could possibly do it lazily and cache though.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Connecting to postgres (Greenplum) database very slow [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-2075948811
Yikes! Well that would explain why. Thanks for the numbers. I'll think about whether we can avoid this query at all...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4139326307
The driver runs this query on startup:
```sql
SELECT
attrelid,
attname,
atttypid
FROM
pg_catalog.pg_attribute
ORDER BY
attrelid, attnum
```
Then it runs this a few times to try to pick up recursive structures:
```sql
SELECT oid, typname, typreceive, typbasetype, typrelid, typarray
FROM pg_catalog.pg_type
WHERE (typreceive != 0 OR typsend != 0) AND typtype != 'r' AND typreceive::TEXT != 'array_recv'
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4827599177
Hi @vaefremov95, it still needs a contributor to investigate all the details and propose a solution
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Connecting to postgres (Greenplum) database very slow [arrow-adbc]
Posted by "mcrumiller (via GitHub)" <gi...@apache.org>.
mcrumiller commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-2078376774
@davlee1972 well sure, the issue isn't that it's Greenplum (I included that information in case it was important), it's how many objects are defined in the database. The DB I use at work obviously has a *lot* of tables and columns, and as a result the initial connection takes a really long time, and probably consumes quite a bit of memory as well.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "iangow (via GitHub)" <gi...@apache.org>.
iangow commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4139370051
> Is it possible to confirm that your database does have a lot of OIDs?
Is there a query I could run to answer your question?
> Then we can try to replicate, I think. At least figure out why Python seems to be double-loading the metadata. We should also probably see if we can borrow any tricks from DuckDB. (That said, IIRC the DuckDB extension pulls some tricks that I don't think we'd want to pull.)
> The driver runs this query on startup:
>
> SELECT
> attrelid,
> attname,
> atttypid
> FROM
> pg_catalog.pg_attribute
> ORDER BY
> attrelid, attnum
> Then it runs this a few times to try to pick up recursive structures:
>
> SELECT oid, typname, typreceive, typbasetype, typrelid, typarray
> FROM pg_catalog.pg_type
> WHERE (typreceive != 0 OR typsend != 0) AND typtype != 'r' AND typreceive::TEXT != 'array_recv'
I ran both these queries using `psql` with output set to the attached (quite large) file.
[oids.txt.zip](https://github.com/user-attachments/files/26291679/oids.txt.zip)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "mcrumiller (via GitHub)" <gi...@apache.org>.
mcrumiller commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4139377659
@iangow just do a `SELECT COUNT(*) FROM (query)`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-5018873677
To summarize, I think there's a variety of things that need to be done here:
- Expand integration testing to cover Postgres-compatible databases
- It appears people have [tested](https://github.com/columnar-tech/adbc-quickstarts/tree/main/python/postgresql) all of the following: CedarDB, Citus, CockroachDB, CrateDB, Greenplum, Neon, ParadeDB, TimescaleDB, Yellowbrick, YugabyteDB. AlloyDB has not been tested AFAIK
- If we change our queries or approach, it would be best to verify (ideally automatically) that we haven't broken compatibility (or explicitly note that some DBs are not fully compatible), so if we can add some of these to CI, that would be ideal
- Redshift is explicitly out-of-scope and IIRC it is known that Materialize is not compatible
- Consider if we can simply optimize the query (as @vaefremov95 suggests)
- The optimized query is subject to the same compatibility question, although again, the driver is already not necessarily compatible with all Postgres-"compatible" systems
- Consider loading the OID -> typrecv mapping dynamically
- Consider if we can hardcode the OID -> typrecv mapping for core data types and avoid the lookup unless you actually query a composite or other user-defined type; this depends on all Postgres versions (and other databases) having a consistent mapping
@Mandukhai-Alimaa if you still want to work on this, it might be best to break this down
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "Mandukhai-Alimaa (via GitHub)" <gi...@apache.org>.
Mandukhai-Alimaa commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4224821093
take
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-3193959847
We already do this to figure out the schema (we have to, as COPY isn't self-describing). However, that gives us the OIDs...and we need that lookup to know what the OIDs map to. We could hardcode the core types and skip the initial lookup for that, perhaps.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "iangow (via GitHub)" <gi...@apache.org>.
iangow commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4130887936
I'm having an issue that seems very similar to the above.
`adbc_driver_postgresql.connect(uri)`: about 21-23s
The thing is, I don't have this issue (not even close) with the `adbcpostgresql` driver with R.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4204476389
@Mandukhai-Alimaa are you interested in looking at this? I think there are a few questions in play:
- Is it safe to hardcode the a list of "base" OIDs so that we don't have to do this lookup at the start? (We'd need to confirm that they're stable across PostgreSQL versions, and whether forks/PostgreSQL-compatible databases use the same OIDs or not. I know Redshift does _not_ use the same OIDs, but I want us to drop Redshift support anyways: https://github.com/apache/arrow-adbc/issues/4151)
- Can we replicate the issue if we set up Postgres with a ton of OIDs?
- Can we replicate/figure out if we're accidentally double-initializing something in the Python bindings that would cause this apparent doubling of initialization time?
If we can replicate it, then we can consider lazily caching or something else to mitigate it (or ideally, just hardcoding OIDs, at least for Postgres itself)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4139319604
Is it possible to confirm that your database does have a lot of OIDs? Then we can try to replicate, I think. At least figure out why Python seems to be double-loading the metadata. We should also probably see if we can borrow any tricks from DuckDB. (That said, IIRC the DuckDB extension pulls some tricks that I don't think we'd want to pull.)
Also, that pg_parquet extension is interesting...@kou has been trying for years to get an extension that would allow pluggable COPY into core, but navigating Postgres's contributor structure has been challenging.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "vaefremov95 (via GitHub)" <gi...@apache.org>.
vaefremov95 commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4837266338
Hi @lidavidm !
As far as I understand, this problem happens on databases with a huge number of tables, for example Greenplum. As a quick fix, what if we reduce the number of objects in the queries by dropping the unnecessary ones (partitions and inheritance children, indexes, TOAST tables, sequences, Greenplum system tables, etc.) and keeping only the needed ones (regular tables, views, materialized views, composite types, foreign tables)?
New queries will look something like this:
```
pg_attribute:
SELECT a.attrelid, a.attname, a.atttypid
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON c.oid = a.attrelid
WHERE c.relkind IN ('r','v','m','c','f','p')
AND a.attnum > 0
AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_inherits i WHERE i.inhrelid = a.attrelid)
ORDER BY a.attrelid, a.attnum
```
```
pg_type:
SELECT oid, typname, typreceive, typbasetype, typrelid, typarray
FROM pg_catalog.pg_type
WHERE (typreceive != 0 OR typsend != 0)
AND typtype != 'r'
AND typreceive::TEXT != 'array_recv'
AND (typrelid = 0 OR typrelid IN (
SELECT c.oid FROM pg_catalog.pg_class c
WHERE c.relkind IN ('r','v','m','c','f','p')
AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_inherits i WHERE i.inhrelid = c.oid)
))
```
Checked locally - connect time dropped from 2.5 minutes to 8 seconds, and memory usage down to 128 MB.
I tested it on regular queries and everything works as expected. And for backward compatibility we could put this behind a flag/option.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-5629138794
https://github.com/apache/arrow-adbc/pull/4775 is an experimental PR that adds an option to only use a hardcoded set of OIDs, eliminating the need for any query on startup. Before merging I plan to add a basic fallback where it will build the type table if needed when a query requires it; longer-term we would build the type table on-the-fly instead of requesting everything up front.
In Postgres, every table is automatically a composite type which unfortunately means that it's hard to optimize the underlying query in a fully correct way (although most likely, the vast majority of tables are never used in such a way, we have no way of knowing up front).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "paleolimbot (via GitHub)" <gi...@apache.org>.
paleolimbot commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-2457853597
I am not sure I'll get to this soon but I think some reasonable steps might be:
Add a `virtual ArrowErrorCode Find(std::vector<uint32_t> oids, std::vector<PostgresType>* out, ArrowError* error)` overload to look up more than one OID at a time:
https://github.com/apache/arrow-adbc/blob/9ac8f6c78bbb28c80d81609e37b823b255a7f09d/c/driver/postgresql/postgres_type.h#L390
...and wire up existing uses of `Find()` to use the "bulk" approach where possible. Then, somewhere around here:
https://github.com/apache/arrow-adbc/blob/9ac8f6c78bbb28c80d81609e37b823b255a7f09d/c/driver/postgresql/database.cc#L125-L132
...make a concrete subclass of the PostgresTypeResolver that implements the "bulk" and non-bulk finds by connecting to the database and issuing a query (if needed). I think the query would be much like the existing one except filtering on OID and possibly issuing an additional query(queries?) if one of those types is a "record" type (because the record type won't insert unless its column types are also available in the cache). There's an example of issuing a query with a parameterized "any of these integer things is equal to" in the GetObjects helper:
https://github.com/apache/arrow-adbc/blob/9ac8f6c78bbb28c80d81609e37b823b255a7f09d/c/driver/postgresql/connection.cc#L67-L77
Connecting to a database, issuing one or more queries, then disconnecting is obviously slow, so if we did this we might need to stay connected for some amount of time before disconnecting. Also, to avoid doing this at all when a user issues a query, we probably want to cache all the types in `PostgresTypeIdAll(/*nested*/false)`.
https://github.com/apache/arrow-adbc/blob/9ac8f6c78bbb28c80d81609e37b823b255a7f09d/c/driver/postgresql/postgres_type.h#L948-L1034
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Connecting to postgres (Greenplum) database very slow [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-2075030228
How long do these queries take for you?
https://github.com/apache/arrow-adbc/blob/1129accd8b5070c21567427f8d6b286f2c134fdd/c/driver/postgresql/database.cc#L140-L173
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Connecting to postgres (Greenplum) database very slow [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-2074929091
Is there a way to spin up Greenplum/does this happen on a fresh install that we could poke at?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Connecting to postgres (Greenplum) database very slow [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-2074920475
Hmm, the main thing I would suspect is that we have to read all the type OIDs on first connect so that we know what Arrow type to map them to later. I don't think this has ever come up as taking a long time, but perhaps this deployment or Greenplum makes this slower somehow? (Or the type list is simply very long?)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "kou (via GitHub)" <gi...@apache.org>.
kou commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4139344096
> [@kou](https://github.com/kou) has been trying for years to get a patch that would allow pluggable COPY into core, but navigating Postgres's contributor structure has been challenging.
FYI: https://www.postgresql.org/message-id/flat/20231204.153548.2126325458835528809.kou@clear-code.com is the thread for the proposal. I'm waiting for responses from PostgreSQL committers.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #1755:
URL: https://github.com/apache/arrow-adbc/issues/1755#issuecomment-4130909134
That's odd because the `adbcpostgresql` driver is the exact same. What versions do you have installed?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Initial connection to postgres very slow when number of OIDs is large [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm closed issue #1755: Initial connection to postgres very slow when number of OIDs is large
URL: https://github.com/apache/arrow-adbc/issues/1755
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org