Joishi Bodio's questions

我有一个历史表，它有一个“ID”和“TIMESTAMP”列

CREATE TABLE hist (
  HIST_ID INTEGER,
  HIST_TIMESTAMP TIMESTAMP,
  ID INTEGER, -- this is the id of the table that is being tracked
  --OTHER COLS
);

我在这张表上也有一个索引

CREATE INDEX hist_ix ON hist (ID, HIST_TIMESTAMP);

该表有很多针对它的插入，目前有大约 3000 万行。

当我尝试运行以下查询时，oracle 执行全表扫描而不是使用索引（索引……至少我相信……它应该能够使用）。

SELECT ID, MAX(HIST_TIMESTAMP) FROM hist WHERE HIST_TIMESTAMP <= <<A TIMESTAMP>> GROUP BY ID;

在我看来，Oracle 应该能够使用索引通过查看 id-by-id 上的 id/timestamp 索引来快速识别哪个 id/timestamp 对位于特定时间点的“左侧”基础，但它坚持全表扫描。

如果能更快地运行此查询，我们将不胜感激。

我运行了以下命令以确保统计数据是最新的

EXEC DBMS_STATS.GATHER_TABLE_STATS('<meh>','hist');

此外，历史表中大约有 1k 个不同的 ID 值。

关于数据分布……在大约 1k 个 ID 中，50 个在表中的条目少于 100 个，70 个在 100 到 1000 个条目之间，146 个在 1000 到 10000 个条目之间，其余的条目在 10k 到 60k 之间。超过一半的条目至少有 30k 条记录。

我有一个accounts包含 ~200k 行的表和这些列的索引：

  account_type_id BIGINT
, member_id BIGINT
, external_id VARCHAR(64)

CREATE INDEX account_full_qualifiers_idx
ON normal_object.account (account_type_id, member_id, external_id) TABLESPACE index_tbsp;

我有一个函数正在使用以下查询执行一些 ETL 工作：

EXECUTE '
   SELECT * FROM normal_object.account
   WHERE account_type_id = $1
   AND member_id = $2
   AND external_id = $3'
INTO e_row
USING r_row.account_type_id, r_row.member_id, r_row.external_id;

然而，该EXECUTE命令正在NOT使用索引，我不确定为什么。我唯一的猜测是数据类型没有对齐。然而r_row.account_type_id是一个BIGINT，r_row.member_id是一个BIGINT，而且r_row.external_id是一个VARCHAR(64)。

关于为什么不使用索引的任何建议？
我怎样才能让它使用索引？（我已经试过enable_seqscan出发了。）

我是我们公司唯一的 DBA，在这个行业相对较新（现在有 2.5 年的经验）。开发人员和我目前正在讨论如何为正在开发的新软件正确设计数据库。他们在模块中开发他们的应用程序，并认为每个模块都有一个私有状态表是最好的，我认为最好有一个表来保存所有内部生成的状态（然后可能还有额外的表来赋予这些状态一些意义）。下面的示例代码是用 postgresql 编写的，因为这是我们使用的。

我的方法：

CREATE TABLE status (
  status_id SERIAL PRIMARY KEY,
  status_code VARCHAR(3) UNIQUE NOT NULL, -- short description
  status_description VARCHAR(50) UNIQUE NOT NULL -- long description
);

CREATE TABLE member_status (
  status_id INT PRIMARY KEY REFERENCES status (status_id)
);

他们的做法：

CREATE TABLE member_status (
  status_id SERIAL PRIMARY KEY,
  status_code VARCHAR(3) UNIQUE NOT NULL, -- short description
  status_description VARCHAR(50) UNIQUE NOT NULL -- long description
);
-- rinse and repeat for every object that may need a status

他们只看到我的方法有“问题”（尚未明确说明），虽然我没有看到他们的方法有问题，但我确实认为这不是最好的设计。

任何设计更好的输入以及为什么会受到赞赏。

我正在写一份将数据从旧设计转换为新设计的工作。在这个过程中，我需要将插入中的 id 放入单独的表中，并将其用于插入到目标表中，如下所示：

CREATE TABLE t1 {
  t1_id BIGSERIAL,
  col1 VARCHAR
};
CREATE TABLE t2 {
  t2_id BIGSERIAL,
  col2 VARCHAR, -- renamed from col1 to avoid confusion
  t1_id BIGINT REFERENCES t1.t1_id
};

我定义了与以下形式匹配的 SQL：

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, (SELECT * FROM ins)
FROM t3 a;

我希望它为..SELECT * FROM ins的每一行运行SELECT.. 但它只运行一次并将该值用于 . 中的所有行SELECT。如何重组我的 SQL 以获得所需的行为？

编辑4

t1 最终看起来像：

1,<NULL>
(1 row)

t2 最终看起来像：

10,'a',1
11,'b',1 -- problem with id from t1 being 1
12,'c',1 -- problem with id from t1 being 1
.
.

我希望 t1 看起来像什么：

1,<NULL>
2,<NULL>
3,<NULL>
.
.

我希望 t2 看起来像什么：

10,'a',1
11,'b',2 -- id from t1 of 2
12,'c',3 -- id from t1 of 3
.
.

编辑 为了解决 a_horse_with_no_name 所说的问题，我也尝试了这个（结果相同）：

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, b.t1_id
FROM t3 a
JOIN ins b ON TRUE;

编辑2 我只是尝试SEQUENCE在我的查询中直接引用适当的，并且确实有效 - 但我根本不喜欢那个解决方案（主要是因为我不喜欢硬编码对象名称。）如果有任何其他解决方案比直接引用名称SEQUENCE我会很感激。:)

编辑3 我想另一种解决方案是使用 a代替 CTE .. 但我仍然会欣赏选项/建议PROCEDURE。INSERT

以下是关于 PostgresQL 9.1 的：

我正在使用 right() 和 left() 字符串函数来修复一些将数据输入到我的数据库中的实例“相差一个”..所以我正在按照right(field1,1)||left(field2,-1) 的方式做一些事情来获得正确的值等等. 在此过程中，我在 char(2) 列上遇到了意外结果。

如果我这样做SELECT RIGHT('A ',1)，它会给我预期的' '回报。

但是，如果我这样做SELECT RIGHT('A '::CHAR(2),1)，它会给我'A'.

为什么要这样做？这是一个错误吗？我试过SELECT RIGHT('A '::CHAR(3),1)了，它也给出'A'了结果。

所以我目前正在创建一些 SQL 来读取 postgres (9.1) 目录以构建表定义。但是，我遇到了 SERIAL/BIGSERIAL 数据类型的问题。

例子：

CREATE TABLE cruft.temp ( id BIGSERIAL PRIMARY KEY );
SELECT * FROM information_schema.columns WHERE table_schema='cruft' AND table_name='temp';
"db","cruft","temp","id",1,"nextval('cruft.temp_id_seq'::regclass)","NO","bigint",,,64,2,0,,,,,,,,,,,,,"db","pg_catalog","int8",,,,,"1","NO","NO",,,,,,,"NEVER",,"YES"

它给了我数据库名称 (db)、模式名称 (cruft)、表名称 (temp)、列名称 (id)、默认值 (nextval( ... )) 和数据类型 (bigint 和 int8 .. NOT bigserial) ...我意识到我可以检查默认值是否是一个序列 - 但我不相信这会 100% 准确，因为我可以手动创建一个序列并创建一个默认值为的非序列列那个序列。

有没有人对我如何做到这一点有建议？除了检查 nextval(*_seq) 的默认值之外还有什么？

在 TL;DR 或不熟悉 pg_catalog 的新用户的情况下，针对此处添加的 SQL 解决方案进行了编辑：

with sequences as (
  select oid, relname as sequencename from pg_class where relkind = 'S'
) select
  sch.nspname as schemaname, tab.relname as tablename, col.attname as columnname, col.attnum as columnnumber, seqs.sequencename
from pg_attribute col
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid and deps.deptype = 'n'
left join sequences seqs on deps.refobjid = seqs.oid
where sch.nspname != 'information_schema' and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
  and col.attnum > 0
  and seqs.sequencename is not null -- TO ONLY VIEW SERIAL/BIGSERIAL COLUMNS
order by sch.nspname, tab.relname, col.attnum;