在研究 RVC 时，我发现了与比较运算符有关的官方文档<, >, >=, <=，尽管很清楚：
select (1,2) < (1,3)=true
由于文档中已经提到了公式a < c OR (a = c AND b < d)，所以不清楚原因：
select (1,2,null) < (1,3,0)=true
引用文档：
对于 <、<=、> 和 >= 情况，行元素从左到右比较，一旦找到不相等或空的元素对就停止。
和
例如，ROW(1,2,NULL) < ROW(1,3,0) 产生 true 而不是 null，因为不考虑第三对元素。
什么是“第三对”以及公式如何扩展/适应越来越多的列（例如select (1,2,3,4,5) < (1,3,0,8,9)
另外，为什么这会在我一开始就期望的时候select (1,2,0) >= (1,2,null);返回。nulltrue1>=1
情景#1
基本上，假设我们有一个使用动态 SQL 的函数，但也知道它总是为相同的输入返回相同的输出并声明它是不可变的，它真的是不可变的吗？aka 编译器/查询处理器会知道这种特定情况吗？
根据官方文档
IMMUTABLE 函数不能修改数据库，并且保证在给定相同参数的情况下永远返回相同的结果。此类别允许优化器在查询使用常量参数调用该函数时预先评估该函数。
按照这个逻辑，它应该可以正常工作，因为它只关心输入是否相同。
情景#2
同上，但输入类型为anyelementor anyarray。考虑到 SQL 是一种强类型语言（来自官方文档）
SQL 是一种强类型语言。也就是说，每个数据项都有一个关联的数据类型，它决定了它的行为和允许的使用。
并且知道这anyelement只是一个伪类型，因为实际上在处理函数期间它知道它（例如）是一个整数或字符串。因此，从这个角度来看，一个函数anyelement在不可变时是否接受并不重要。
我想知道上面写的是否确实是 PostgreSQL 在这些条件下的真实行为。如果与使用常规 SQL 的常规不可变函数相比有什么不同的话。
为了澄清我遇到的问题，我正在 DB 上编写 pgtap 单元测试，我的一个函数返回一个表。但是，pgtap 函数function_returns没有表检查及其列（名称和数据类型）的定义。
这些都是函数的重载：
SELECT function_returns( :schema, :function, :args, :type, :description );
SELECT function_returns( :schema, :function, :args, :type );
SELECT function_returns( :schema, :function, :type, :description );
SELECT function_returns( :schema, :function, :type );
SELECT function_returns( :function, :args, :type, :description );
SELECT function_returns( :function, :args, :type );
SELECT function_returns( :function, :type, :description );
SELECT function_returns( :function, :type );
您可以阅读官方文档中的完整定义以及每个参数的含义
基本上问题是它:type不适用于table.
所以我想知道这些用户定义的函数保存在 Postgres 中的哪里（尝试编写我自己的版本并希望为 pgtap 项目做出贡献）。
在挖掘源代码后，我找到了后台使用的函数来检查这个，它被称为_func_compare
CREATE OR REPLACE FUNCTION _func_compare( NAME, NAME, NAME[], anyelement, anyelement, TEXT)
RETURNS TEXT AS $$
SELECT CASE WHEN $4 IS NULL
THEN ok( FALSE, $6 ) || _nosuch($1, $2, $3)
ELSE is( $4, $5, $6 )
END;
$$ LANGUAGE SQL;
而且我不知道作者是如何检查函数的有效返回定义的。
查看 PostgreSQL 的文档，我在timenow(). 但是，如果我调用该函数，它就可以工作。
那么now()和 和有什么不一样timenow()？
我猜这now()是基于事务，而timenow()服务器操作系统时间还是我完全关闭？
以下是 DataGrip 的自动生成定义。所有系统功能（例如sum）都具有missing source code并且您可以看到它是pg_catalog模式的一部分。
随着我在 PostgreSQL 方面获得更多经验，我开始质疑 PLPython 的存在。它被认为是一种“不受信任”的语言https://www.postgresql.org/docs/10/plpython.html
我想知道的是，何时或为什么有人需要使用它？PLPGSQL 已经是一种相当强大的语言，它可以让你做很多事情。这里有没有人需要使用它，如果有，为了什么？
我想学习如何制作一个基于 PostgreSQL 的 DBMS（关系型）。不是为了生产目的而制作商业产品，而是为了学习。
我计划使用 C++，但是要找到关于如何构建自己的（或至少从哪里开始）的信息并不容易。
我知道这个社区中有些人甚至是 PostgreSQL 源代码的贡献者（例如 Laurenz Albe），所以在这里提出这个问题是有意义的。
我一直在学习本课程https://www.youtube.com/playlist?list=PLSE8ODhjZXjbohkNBWQs_otTrBTrjyohi以了解 DBMS 的来龙去脉。但由于大部分只是解释理论，我没有机会看到实际或如何实际去做。
只是为了提供一个快速的 DDL 让我的问题更容易理解：
------------------ creating tables ------------------
create table if not exists users(
user_id bigserial primary key,
first_name text,
last_name text,
user_name text
);
create table if not exists managers(
manager_id bigserial primary key,
user_id bigint references users(user_id) not null,
permissions text[]
);
create table if not exists transactions(
transaction_id bigserial primary key,
user_id bigint references users(user_id) not null,
amount numeric,
is_approved boolean,
manager_id bigint references managers(manager_id) null
);
------------------ inserting data ------------------
insert into users(first_name, last_name, user_name)
values ('first_name','last_name','ohyea'),
('manager','manager','manager');
insert into managers(user_id)
values (2);
insert into transactions(user_id, amount, is_approved, manager_id)
values (1,10,true,null),
(1,1000,true,1);
现在我基本上将使用这种方式获取经理的姓名：
select
t.transaction_id,
t.user_id,
u.user_name,
t.amount,
t.is_approved,
mu.user_name as manager_name
from transactions t
join users u on u.user_id = t.user_id
left join managers m
join users mu on mu.user_id = m.user_id
on m.manager_id = t.manager_id;
我感兴趣的部分是：
left join managers m
join users mu on mu.user_id = m.user_id
on m.manager_id = t.manager_id
这对 PostgreSQL 来说是什么？一个后缀？就像它如何解释这个以及做这样的事情和这个有什么区别：
left join (
select m.manager_id, u.user_name
from managers m
join users u on u.user_id = m.user_id
) mu on mu.manager_id = t.manager_id
编辑：
经过大量测试，结果证明这两种方法绝对、100%、1:1 相同。相同的执行计划，相同的执行时间（具有微小的毫秒变化），使用第一种方法获得的唯一好处就是代码更简洁，并且在具有大量子查询连接的更大函数中更容易遵循。
在 SQL Server 中，您可以使用该DBCC PAGE命令检查特定的索引信息。postgres中有类似的东西吗？
如果没有，那么您将如何获得相同的信息？这只是出于研究目的。
此命令返回：
Page @0x00000004ED67A000
m_pageId = (1:299) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4
m_objId (AllocUnitId.idObj) = 121 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594045857792
Metadata: PartitionId = 72057594040614912 Metadata: IndexId = 1
Metadata: ObjectId = 99 m_prevPage = (1:297) m_nextPage = (1:300)
pminlen = 4008 m_slotCnt = 2 m_freeCnt = 70
m_freeData = 8118 m_reservedCnt = 0 m_lsn = (35:184:44)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0 DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60 Length 4011
因此，当他调用函数时，我不仅尝试记录 postgres 用户，还尝试从我的表中记录他的 ID（例如）Person (person_id)。
到目前为止，据我所知，我几乎可以从这些系统表中获得所需的所有信息：
pg_stat_activitypg_stat_statements如果我激活它（但由于某些限制可能无法激活）但我似乎无法弄清楚的部分是我是否想从（例如）用户表（user_id例如）记录用户的 ID。
基本上我想创建一个只存放上个月日志的小表，我将插入其中。
我的主要问题是 user_id 部分，因为其他 3 个值已经存在于pg_stat_activity( client_addr, query_start, query)
如果没有简单的方法来实现这一点，假设我想创建一个函数，该函数将根据返回的参数以某种方式选择 user_id pg_stat_activity，但我似乎无法弄清楚我将如何做到这一点。隐藏的表参数tableoid, cmin, cmax, xmin, xmax, ctid在这里对我没有帮助，至少我看不到如何，也许我错过了什么？
select
user_id,
count(id) as unread_count
from
notifications
where
is_read = false
and user_id in(select(unnest('{200 user IDs}' :: bigint[])))
group by
user_id;
问题是，这个查询运行了 1 分钟，有时甚至比这稍长。该表有 32gb 大，并且 user_id 字段上已经有一个索引。
这是一个执行计划
HashAggregate (cost=123354.81..123629.64 rows=27483 width=16) (actual time=90823.880..90823.972 rows=188 loops=1)
Group Key: user_id
-> Nested Loop (cost=2.32..123217.40 rows=27483 width=16) (actual time=0.184..90752.136 rows=48571 loops=1)
-> HashAggregate (cost=1.76..2.76 rows=100 width=8) (actual time=0.146..0.577 rows=200 loops=1)
Group Key: unnest(200 user IDs)
-> Result (cost=0.00..0.51 rows=100 width=8) (actual time=0.021..0.073 rows=200 loops=1)
-> Index Scan using ix_notification_user_id on notification (cost=0.56..1229.40 rows=275 width=16) (actual time=119.659..453.533 rows=243 loops=200)
Index Cond: (200 user IDs)
Filter: (NOT is_read)
Rows Removed by Filter: 368
Planning time: 0.189 ms
Execution time: 90824.196 ms
我尝试了一种使用临时表的解决方案，将 unnest 值插入到临时表中，然后进行比较。但是性能根本没有提高。
我已经运行此查询以查看索引统计信息：
schemaname,
tablename,
reltuples::bigint,
relpages::bigint,
otta,
round(case when otta = 0 then 0.0 else sml.relpages / otta::numeric end, 1) as tbloat,
relpages::bigint - otta as wastedpages,
bs*(sml.relpages-otta)::bigint as wastedbytes,
pg_size_pretty((bs*(relpages-otta))::bigint) as wastedsize,
iname,
ituples::bigint,
ipages::bigint,
iotta,
round(case when iotta = 0 or ipages = 0 then 0.0 else ipages / iotta::numeric end, 1) as ibloat,
case
when ipages < iotta then 0
else ipages::bigint - iotta
end as wastedipages,
case
when ipages < iotta then 0
else bs*(ipages-iotta)
end as wastedibytes
--CASE WHEN ipages < iotta THEN pg_size_pretty(0) ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize
from (
select
schemaname,
tablename,
cc.reltuples,
cc.relpages,
bs,
ceil((cc.reltuples*((datahdr + ma- (case when datahdr % ma = 0 then ma else datahdr % ma end))+ nullhdr2 + 4))/(bs-20::float)) as otta,
coalesce(c2.relname, '?') as iname,
coalesce(c2.reltuples, 0) as ituples,
coalesce(c2.relpages, 0) as ipages,
coalesce(ceil((c2.reltuples*(datahdr-12))/(bs-20::float)), 0) as iotta
-- very rough approximation, assumes all cols
from (
select
ma,
bs,
schemaname,
tablename,
(datawidth +(hdr + ma-
(
case
when hdr % ma = 0 then ma
else hdr % ma
end)))::numeric as datahdr,
(maxfracsum*(nullhdr + ma-
(
case
when nullhdr % ma = 0 then ma
else nullhdr % ma
end))) as nullhdr2
from
(
select
schemaname,
tablename,
hdr,
ma,
bs,
sum((1-null_frac)* avg_width) as datawidth,
max(null_frac) as maxfracsum,
hdr +(
select
1 + count(*)/ 8
from
pg_stats s2
where
null_frac <> 0
and s2.schemaname = s.schemaname
and s2.tablename = s.tablename ) as nullhdr
from
pg_stats s,
(
select
(
select
current_setting('block_size')::numeric) as bs,
case
when substring(v, 12, 3) in ('8.0',
'8.1',
'8.2') then 27
else 23
end as hdr,
case
when v ~ 'mingw32' then 8
else 4
end as ma
from
(
select
version() as v) as foo ) as constants
group by
1,
2,
3,
4,
5 ) as foo ) as rs
join pg_class cc on
cc.relname = rs.tablename
join pg_namespace nn on
cc.relnamespace = nn.oid
and nn.nspname = rs.schemaname
left join pg_index i on
indrelid = cc.oid
left join pg_class c2 on
c2.oid = i.indexrelid ) as sml
where
sml.relpages - otta > 0
or ipages - iotta > 10
order by
wastedbytes desc,
wastedibytes desc;
PK 索引和 user_id 索引都超过 5gbwastedsize和超过 500k+ wastedpages。
我的问题是，对此有什么解决方案？这纯粹是一个需要的索引问题，reindex还是我缺少的其他东西？
我不允许更改表的结构，我只需要对其进行优化，以某种方式从 1 分钟以上减少到 1 秒以下
在 user_id where is_read = false 添加部分索引后，查询时间减少了大约 10-15 秒。但这显然还需要很长时间。
编辑：该表中共有 3250 万行。运行此查询：
SELECT t.user_id, COALESCE(unread_count, 0) AS unread_count
FROM unnest('{200 user_ids}'::bigint[]) t(user_id)
LEFT JOIN LATERAL (
SELECT count(*) AS unread_count
FROM notification n
WHERE n.user_id = t.user_id
AND n.is_read = false
) sub ON true
;
这个执行计划的结果（有趣的是，昨天运行了超过一分钟，今天运行了约 30 秒或更短时间）：
Nested Loop Left Join (cost=1209.05..120908.50 rows=100 width=16) (actual time=333.088..27260.557 rows=200 loops=1)
Buffers: shared hit=1981 read=20396 dirtied=7
I/O Timings: read=27023.896
-> Function Scan on unnest t (cost=0.00..1.00 rows=100 width=8) (actual time=0.022..0.360 rows=200 loops=1)
-> Aggregate (cost=1209.04..1209.05 rows=1 width=8) (actual time=136.292..136.293 rows=1 loops=200)
Buffers: shared hit=1981 read=20396 dirtied=7
I/O Timings: read=27023.896
-> Index Only Scan using ix_test on notification n (cost=0.44..1208.29 rows=300 width=0) (actual time=2.153..136.170 rows=105 loops=200)
Index Cond: (user_id = t.user_id)
Heap Fetches: 21088
Buffers: shared hit=1981 read=20396 dirtied=7
I/O Timings: read=27023.896
Planning time: 0.135 ms
Execution time: 27260.745 ms
我正在尝试为过去 30 天创建一个分区。然而，当我运行这段代码时：
create table Test_Older30day partition of Test
for values from (current_timestamp + interval'-30 day') to (current_timestamp);
我收到此错误：
syntax error at or near "current_timestamp"
我很困惑，我在这里做错了什么？
该表如下所示：
create table if not exists Test(
source_name varchar,
event_name varchar,
service_data json,
stamp timestamptz
)partition by range(stamp);
数据类型匹配，我在日期字段上使用了按范围分区。
我已经知道我可以手动插入它们
create table Test_Older30day partition of Test
for values from ('2019-08-11') to ('2019-09-11')
像那样。我试图尝试的是一种更自动化的方式。基本上，这个想法是避免每个月或每周输入一次。只是为了运行查询，它将使用当前日期。
例如，假设我们有表 A：
create table if not exists T(
column1 int,
column2 varchar,
column3 date
);
和归档表 TArchive：
create table if not exists TArchive(
column1 int,
column2 varchar,
column3 date
);
将早于x日期的数据插入 TArchive 而不在生产中锁定表 T 的最佳方法是什么？假设表 T 有大量的行。
我已经研究了好几个小时了。在 SQL Server 中，您有不同的方法，例如：https://www.brentozar.com/archive/2018/04/how-to-delete-just-some-rows-from-a-really-big-table/ 但在 PostgreSQL我几乎找不到任何东西。
您是否应该直接从 T 表中获取数据并将其导入 TArchive？
您是否应该先将数据导入临时表，然后再将其导入存档表？如果是这样，当您为相同的数据执行 2 倍的插入时，为什么这种方法会更好。
你应该做多少个功能？一个功能来统治他们？还是一个归档功能和另一个删除旧数据的功能？
还有其他方法吗？
我一直在学习 Postgres（来自 SQL Server），这个错误真的让我很困惑。
这是带有一些示例数据的代码：
create table T (
ID serial primary key,
A varchar(1),
B varchar(1),
C varchar(1)
)
↑ 测试表。
insert into T (A, B, C)
values('A', 'B', 'C'), ('A', 'B', 'C')
↑ 插入重复项
delete from T
where ID in (
select t.ID
from ( select ID, row_number() over (partition by A,B,C order by A,B,C) as rn
from T) as t
where t.rn < (select max(t.rn) from t)
)
↑ 删除重复项，保留最后一个条目。
问题出在(select max(t.rn) from t)我假设这是一个新手错误，与在使用别名引用列时不知道 postgres 语法有关？
目前正在学习一些关于查询优化的东西，我一直在尝试不同的查询并偶然发现了这个“问题”。
我正在使用 AdventureWorks2014 数据库，我运行了这个简单的查询：
表结构（取自https://www.sqldatadictionary.com/AdventureWorks2014.pdf）：
SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 100
返回 19,720 行
Sales.Customer 中的总行数 = 19,820
在检查以确保 CustomerID 实际上不仅是表的 PK，而且上面还有一个聚集索引（但它使用非聚集索引），确实是这样：
EXEC SP_HELPINDEX 'Sales.Customer'
这是执行计划↓
https://www.brentozar.com/pastetheplan/?id=B1g1SihGr
我读过，当面对大量数据和/或当它返回超过 50% 的数据集时，查询优化器将支持索引扫描。但是整个表几乎没有 20,000 行（准确地说是 19,820 行），无论如何它都不是一张大表。
当我运行此查询时：
SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 30000
返回 118 行
https://www.brentozar.com/pastetheplan/?id=Byyux32MS
我得到了一个索引搜索，所以我认为这是由于“超过 50% 的情况”，但是，我也运行了这个查询：
SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 20000
返回 10,118 行
https://www.brentozar.com/pastetheplan/?id=HJ9oV33zr
它还使用了索引查找，即使它返回了超过 50% 的数据集。
那么这里发生了什么？
编辑：
打开 IO Statistics 后，>100 查询返回：
Table 'Customer'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
当> 20,000返回时：
Table 'Customer'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
因此WITH(FORCESCAN)，在 >20,000 的选项中添加了一个选项，看看会发生什么：
Table 'Customer'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
因此，即使查询优化器选择为这个特定查询运行 Index Seek，它最终也会通过索引扫描（更少的逻辑读取）运行得更好。
所以我创建了一个计算字符串数组的平均值和中位数的程序：
CREATE PROCEDURE dbo.spAvg_Median
@input nvarchar(20)
AS
BEGIN
declare @split table(id int primary key identity (1,1), number int)
INSERT INTO @split SELECT value FROM string_split(@input, ',')
SELECT Average FROM (
SELECT SUM(number)/COUNT(number) as [Average]
FROM @split
) AS T1
UNION
SELECT Median FROM (
SELECT o.number AS [Median], rn=ROW_NUMBER() OVER (ORDER BY o.number), c.c
FROM @split AS o
CROSS APPLY (SELECT c=COUNT(*) FROM @split b) AS c
) AS x
WHERE rn IN ((c + 1)/2, (c + 2)/2)
END
RETURN 0
GO
但是，当您运行此查询时，您会得到以下结果：
EXEC dbo.spAvg_Median'1,5,9,8,7'
我希望它输出的是一列中的平均值和旁边另一列中的中值。
UNION 命令在那里，所以它不会像这样将它作为两个不同的查询输出，因为这不是我想要的：
我正在处理这个确切的堆栈交换部分的数据库转储。在我处理它时，我遇到了一个我目前无法解决的问题。
在 XML 文件 Posts.xml 中，内容如下所示
当然有多行，但这就是一个看起来的样子。转储中已经给出了一个 Tags.xml 文件，这使得该图片中的“Tags”属性实际上应该是其单独的表（多对多）更加明显。
所以现在我正试图找出一种如何提取标签的方法。这是我试图做的：
CREATE TABLE #TestingIdea (
Id int PRIMARY KEY IDENTITY (1,1),
PostId int NULL,
Tag nvarchar (MAX) NULL
)
GO
↑ 我为测试我的代码而创建的表。我已经用标签和 PostIds 填充了它
SELECT T1.PostId,
S.SplitTag
FROM (
SELECT T.PostId,
cast('<X>'+ REPLACE(T.Tag,'>','</X><X>') + '</X>' as XML) AS NewTag
FROM #TestingIdea AS T
) AS T1
CROSS APPLY (
SELECT tData.value('.','nvarchar(30)') SplitTag
FROM T1.NewTag.nodes('X') AS T(tData)
) AS S
GO
然而此代码返回此错误
XML parsing: line 1, character 37, illegal qualified name character
在谷歌搜索这个错误（包括这里）之后，无论人们有什么（比如额外的 " 标记或不同的 CHAR 集）我都没有。所以我有点卡住了。也许我错过了从以前的答案中非常明显的东西我发现 T_T 无论如何感谢任何有关如何解决此问题的帮助和建议。这是我尚未标准化的最后一张表。
XML 文件中的小样本数据 https://pastebin.com/AW0Z8Be2 对于任何对我用来查看 XML 文件的程序感兴趣的人（所以它更容易阅读，如上图所示）。它被称为 FOXE XML Reader（免费 XML 编辑器 - 第一个对象）