ThatDataGuy's questions

我有一种情况，传入行 (src) 需要与目标表 (tgt) 中的行匹配。但是，对于所有传入的源行，每个目标行只能匹配一次。示例 SQL 如下：

drop table if exists src;
create table src(id int not null, join_key text not null);
insert into src(id, join_key) values
(1,'X'),
(2,'X'),
(3,'X'),
(4,'Y'),
(5,'Y')
;

drop table if exists tgt;
create table tgt(id int not null, join_key text not null);
insert into tgt(id, join_key) values
(10,'X'),
(20,'X'),
(40,'Y')
;

with a as (
    select s.id as src_id,
           s.join_key,
           t.id as tgt_id
    from src s
             left join tgt t on s.join_key = t.join_key
),b as (
    select *,
           row_number() over (partition by src_id order by tgt_id) as rw
    from a
)
     select src_id, tgt_id from b
 where rw = 1
;

这给出：

1,10
2,10
3,10
4,40
5,40

但价值观应该是：

1,10
2,20
3,null
4,40
5,null

我怎样才能做到这一点？

考虑这个片段：

create table tbl_1(name text);
create table tbl_2(name text);

create function my_insert(_name text) returns text
    language sql
as $$
   insert into tbl_2(name)
   values (_name);
   select _name;
$$;

explain analyse
with x as (select name from tbl_1)
   ,y as (select my_insert(x.name) from x)
select 0
;

返回

Result  (cost=12.72..12.72 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
  CTE x
    ->  Seq Scan on tbl_1  (cost=0.00..12.72 rows=1360 width=32) (never executed)
Planning Time: 0.053 ms 
Execution Time: 0.013 ms

现在我可以理解为什么该子句x没有被执行（y当然也可以），但该计划没有提到任何关于发生了什么的事情y。

这让我怀疑查询解析器正在发出一个不包含该子句的查询y。

我怎样才能确认这一点？我怎样才能看到解析器在这里发出了什么？或者，如何查看规划器评估的查询？

我正在关注如何在此页面中使用 postgresql 中的“创建统计信息”的示例。

CREATE TABLE tbl (
                     col1 int,
                     col2 int
);

INSERT INTO tbl SELECT i/10000, i/100000
FROM generate_series (1,10000000) s(i);

CREATE STATISTICS s1 (dependencies) on col1, col2 from tbl;
ANALYZE tbl;

SELECT stxname, stxkeys, stxdependencies
FROM pg_statistic_ext
WHERE stxname = 's1';  

返回：

stxname,stxkeys,stxdependencies
s1,1 2,{"1 => 2": 1.000000}

但是，我对这条线感到困惑

看这个，我们可以看到 Postgres 意识到 col1 完全确定 col2，因此具有 1 的系数来捕获该信息。

在示例中，col2 中有许多重复的记录。那么 col1 是如何“完全确定”col2 的呢？这是什么意思？

我有一个包含许多小型参考数据表和一些大型时间序列表的数据库。参考数据表可以很容易地放入内存中。我有各种加载数据的 ETL 作业，主要是通过将传入数据左连接到现有表中，并在需要时插入/更新/删除。

在 ETL 作业完成后调用所有引用数据表是否有意义，pg_prewarm以确保在下次 ETL 加载触发时将它们全部加载到内存中？我会注意到很大的改进吗？

假设内存足够大，不会导致缓冲区驱逐问题。

想象一下关于猫主人的下表。

drop table if exists  owners cascade;
create table owners(
    id bigint primary key generated always as identity ,
    name text not null
)
;

insert into owners(name)
select random()::text from generate_series(1,20000);
--insert 200,000 owners records

当我删除一些所有者记录时，它非常快：

delete  from owners
where id %10 = 0;

20000 行在 85 毫秒内受影响

现在我添加一个名为“cats”的表，它指的是所有者：

drop table if exists cats;
create table cats(
    id serial primary key ,
    name varchar(20000) not null,
    owner_id int not null references owners(id)
);

--insert 1bn cats records
insert into cats(name, owner_id)
select
       random()::text,
       owners.id
from generate_series(1,10), owners;

让我们删除一些所有者，但首先我们必须删除这些所有者“拥有”的猫：

--delete the records in cats so we don't get a foreign key constraint violation
delete  from cats
where owner_id %10 = 1;


---now we do the same delete on owners as we did before
delete  from owners
where id %10 = 1;

2000 行在 25 秒 828 毫秒内受影响

为什么第二次删除所有者的速度比我们没有 cat 表时慢约 5000 倍？

我看过很多关于 Postgres 'blocks' 的博客和视频。这些是否与其他数据库管理系统实现中的“页面”相同？

我感兴趣地阅读了 postgres 的遗传查询优化器。这是默认启用的吗？是否有启用它的设置？是否有关于何时使用它与何时使用它的良好经验法则？

文档链接如下：

https://www.postgresql.org/docs/11/geqo.html

MariaDB 与 PostgreSQL 的争论在我的公司继续激烈 :-( 这篇有点著名的超级博客文章助长了它。我在这里发现了一些关于写放大缓解的有趣讨论。我的问题是，像这样的任何东西都作为 PostgreSQL 的一部分发布了10 或 11？我会搜索文档，但我不确定它会被称为什么。

我想使用修改 CTE 来插入一些值。insert 语句从 select 语句中插入数据。我使用返回关键字来返回插入的值（包括自动递增的列）。但是，我也希望 CTE 返回其他列。我怎样才能做到这一点？

一个例子如下：

drop table if exists customers;

CREATE TABLE customers (
 customer_id serial PRIMARY KEY,
 name VARCHAR UNIQUE,
 email VARCHAR NOT NULL,
 active bool NOT NULL DEFAULT TRUE
);


INSERT INTO customers (NAME, email)
VALUES
 ('IBM', '[email protected]'),
 (
 'Microsoft',
 '[email protected]'
 ),
 (
 'Intel',
 '[email protected]'
 );

drop table if exists customers2;

CREATE TABLE customers2 (
 customer_id serial PRIMARY KEY,
 name VARCHAR UNIQUE,
 email VARCHAR NOT NULL
);


with x as (
    INSERT INTO customers2 (NAME, email)
    select name, email from customers
    returning customer_id, name, email, active
    )
select * from x
;

我希望最后一条语句返回列 customer_id、name、email、active。但我收到一个错误：

Error: ERROR: column "active" does not exist
  Position: 123
SQLState:  42703
ErrorCode: 0
Error occurred in:
with x as (
    INSERT INTO customers2 (NAME, email)
    select name, email from customers
    returning customer_id, name, email, active
    )
select * from x

我有一个 PostgreSQL 数据库，我有大量的负载运行。我希望这个负载尽可能快。我已经在使用复制命令等。

我一直在阅读有关timescaledb以及它如何提供改进的插入性能的信息。但是，如果我只关心插入性能，我想知道使用超表而不是常规表是否有任何缺点？

是否可以在数据库的事务历史中创建一个命名点，以便后续连接可以在日志中的那个命名点检索数据？

例如，假设有一个加载程序进程经常写入多个表。在某个时候，一个读取进程在日志中创建一个命名点，我们称之为点 A。加载器做了一些更多的更新，现在我们在点 B。然后另一个读取进程出现，并且可以选择数据作为点 A .

SQL Server 中可能出现这种情况吗？

不幸的是，表/加载器没有双时态结构，因此读取器无法提供截至日期时间作为 select 语句的谓词。