lidavidm commented on issue #685:
URL: https://github.com/apache/arrow-adbc/issues/685#issuecomment-1601527203
Looking at JDBC drivers:
- PostgreSQL basically just gives you row count of columns with indices on
them (including primary key). The debugger shows it executes this query
internally:
```sql
SELECT
tmp.TABLE_CAT,
tmp.TABLE_SCHEM,
tmp.TABLE_NAME,
tmp.NON_UNIQUE,
tmp.INDEX_QUALIFIER,
tmp.INDEX_NAME,
tmp.TYPE,
tmp.ORDINAL_POSITION,
trim(
both '"'
from
pg_catalog.pg_get_indexdef(
tmp.CI_OID, tmp.ORDINAL_POSITION,
false
)
) AS COLUMN_NAME,
CASE tmp.AM_NAME WHEN 'btree' THEN CASE
tmp.I_INDOPTION[tmp.ORDINAL_POSITION - 1] & 1 :: smallint WHEN 1 THEN 'D' ELSE
'A' END ELSE NULL END AS ASC_OR_DESC,
tmp.CARDINALITY,
tmp.PAGES,
tmp.FILTER_CONDITION
FROM
(
SELECT
NULL AS TABLE_CAT,
n.nspname AS TABLE_SCHEM,
ct.relname AS TABLE_NAME,
NOT i.indisunique AS NON_UNIQUE,
NULL AS INDEX_QUALIFIER,
ci.relname AS INDEX_NAME,
CASE i.indisclustered WHEN true THEN 1 ELSE CASE am.amname WHEN
'hash' THEN 2 ELSE 3 END END AS TYPE,
(
information_schema._pg_expandarray(i.indkey)
).n AS ORDINAL_POSITION,
ci.reltuples AS CARDINALITY,
ci.relpages AS PAGES,
pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILTER_CONDITION,
ci.oid AS CI_OID,
i.indoption AS I_INDOPTION,
am.amname AS AM_NAME
FROM
pg_catalog.pg_class ct
JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid)
JOIN pg_catalog.pg_index i ON (ct.oid = i.indrelid)
JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid)
JOIN pg_catalog.pg_am am ON (ci.relam = am.oid)
WHERE
true
AND ct.relname = 'mytable'
) AS tmp
ORDER BY
NON_UNIQUE,
TYPE,
INDEX_NAME,
ORDINAL_POSITION
```
- SQL Server gives you the row count for the table, and tells you about
columns with a UNIQUE constraint, but doesn't give you much else. Internally it
runs `EXEC sp_statistics_100 ?,?,?,?,?, ?`
It seems we shouldn't expect much here from JDBC (and to be fair,
getIndexInfo was meant to get info about indices, not really get us detailed
statistics), so if we want detailed statistics we'll have to do it per database.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
