问题[postgresql](dba)

我有一个大表，extrinsics大小将近 90GB，包含来自多个区块链的数据。

我有一个需要将近 17 分钟才能运行的查询：

select * from public.extrinsics
where chain_id = 1
ORDER BY "extrinsics"."block_number" DESC
limit 10;

我可以翻转chain_id并2运行查询，运行时间不到一秒钟。

select count(*) from "extrinsics" where chain_id = 1= ~ 38M 行
select count(*) from "extrinsics" where chain_id = 2= ~ 58M 行

我试过（没有运气）：

• 在 chain_id 和 block_number 上创建多列索引。
• 在 chain_id 和 block_number order DESC 上创建多列索引。
• 将 block_number 的统计数据增加到 10,000 并运行 ANALYZE
• 将 chain_id 的统计数据增加到 10,000 并运行 ANALYZE

起初我以为是查询计划，即使我有上面提到的其他索引，它总是求助于向后扫描我的索引，但如果2 仍然很快，block_number查询计划错误似乎不是问题。chain_id

要求解释：

链 2（快速）：

"Limit  (cost=0.57..7.76 rows=10 width=829) (actual time=1.563..2.379 rows=10 loops=1)"
"  Buffers: shared read=9"
"  I/O Timings: read=2.310"
"  ->  Index Scan Backward using index_extrinsics_on_block_number on extrinsics  (cost=0.57..41768857.19 rows=58091433 width=829) (actual time=1.561..2.375 rows=10 loops=1)"
"        Filter: (chain_id = 2)"
"        Buffers: shared read=9"
"        I/O Timings: read=2.310"
"Planning Time: 0.636 ms"
"Execution Time: 2.417 ms"

链 1（慢）：

"Limit  (cost=0.57..11.60 rows=10 width=829) (actual time=912353.888..912356.009 rows=10 loops=1)"
"  Buffers: shared hit=1872576 read=2079934"
"  I/O Timings: read=890705.882"
"  ->  Index Scan Backward using index_extrinsics_on_block_number on extrinsics  (cost=0.57..41768857.19 rows=37874906 width=829) (actual time=912353.886..912356.003 rows=10 loops=1)"
"        Filter: (chain_id = 1)"
"        Rows Removed by Filter: 10936113"
"        Buffers: shared hit=1872576 read=2079934"
"        I/O Timings: read=890705.882"
"Planning Time: 0.207 ms"
"Execution Time: 912356.134 ms"

-- Table Definition ----------------------------------------------

CREATE TABLE extrinsics (
    id BIGSERIAL PRIMARY KEY,
    block_number bigint NOT NULL,
    extrinsic_index integer,
    timestamp bigint,
    is_signed boolean DEFAULT false,
    signer character varying,
    method character varying,
    section character varying,
    args jsonb,
    extrinsic_hash character varying,
    doc character varying[],
    success boolean DEFAULT false,
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL,
    chain_id integer NOT NULL DEFAULT 1
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX extrinsics_pkey ON extrinsics(id int8_ops);
CREATE INDEX index_extrinsics_on_block_number ON extrinsics(block_number int8_ops);
CREATE INDEX index_extrinsics_on_chain_id ON extrinsics(chain_id int4_ops);
CREATE INDEX index_extrinsics_on_signer ON extrinsics(signer text_ops);
CREATE INDEX index_extrinsics_on_signer_and_chain_id ON extrinsics(signer text_ops,chain_id int4_ops);
CREATE UNIQUE INDEX uniq_extrinsics ON extrinsics(block_number int8_ops,extrinsic_index int4_ops,chain_id int4_ops);
CREATE INDEX index_extrinsics_on_chain_id_and_block_number ON extrinsics(chain_id int4_ops,block_number int8_ops);
CREATE INDEX blocks_front_page_index ON extrinsics(chain_id int4_ops,block_number int8_ops DESC);
CREATE INDEX dee_test ON extrinsics(block_number int8_ops DESC NULLS LAST);

我有一个 Table Student，我有一个 XML 作为输入

inputXML=
<Students><student><id>1</id><Name>SAM</Name></student>
<student><id>1</id><Name>RAM</Name><Phone>123</Phone></student></Students>

我试过下面的代码

Insert into Student (id,Name,Phone)
select * from unnest(
unnest('Students/student/id/text()',inputXML)::text[]::int[],
unnest('Students/student/Name/text()',inputXML)::text[],
unnest('Students/student/Phone/text()',inputXML)::text[]
) as T(id,Name,Phone);

但我的桌子看起来像

我需要我的表格如下所示

请建议保留该行中的数据我为此使用 postgres 14.6

我正在使用 PostgreSQL 11。我有一个包含 ~500к 条记录的表，其大小约为 2GB。如果我正在运行此查询：

SELECT DISTINCT point.id
FROM "buildings_point" as point
    LEFT OUTER JOIN "equipment_equipment" as equipment ON (point.equipment_id = equipment.id)
WHERE (
    equipment.id = 4
    OR point.id = 4
);

我在buildings_point表中确实有两个索引：

"buildings_point_pkey" PRIMARY KEY, btree (id)
"buildings_point_997b9956" btree (equipment_id)

对于equipment_equipment表，我确实有索引id：

"equipment_equipment_pkey" PRIMARY KEY, btree (id)

但是调度程序运行： Parallel Seq Scan on buildings_point point 大约需要 18 秒来遍历所有 500к 条记录。如果我删除任何equipment.id = 4orpoint.id = 4查询将花费大约 5 毫秒。为什么调度程序不使用两个索引？

解释分析输出：

   ->  Sort  (cost=175133.54..175133.59 rows=19 width=4) (actual time=19163.682..19181.748 rows=3 loops=1)
         Sort Key: point.id
         Sort Method: quicksort  Memory: 25kB
         ->  Gather  (cost=2307.62..175133.14 rows=19 width=4) (actual time=3958.040..19181.729 rows=3 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Parallel Hash Left Join  (cost=1307.62..174131.24 rows=8 width=4) (actual time=8205.581..19155.693 rows=1 loops=3)
                     Hash Cond: (point.equipment_id = equipment.id)
                     Filter: ((equipment.id = 4) OR (point.id = 4))
                     Rows Removed by Filter: 218243
                     ->  Parallel Seq Scan on buildings_point point  (cost=0.00..172111.33 rows=271333 width=8) (actual time=4.661..18838.772 rows=218244 loops=3)
                     ->  Parallel Hash  (cost=1116.56..1116.56 rows=15285 width=4) (actual time=52.361..52.364 rows=12229 loops=3)
                           Buckets: 65536  Batches: 1  Memory Usage: 1984kB
                           ->  Parallel Index Only Scan using buildings_equipment_pkey on equipment_equipment equipment  (cost=0.29..1116.56 rows=15285 width=4) (actual time=0.848..34.102 rows=12229 loops=3)
                                 Heap Fetches: 176
 Planning Time: 0.275 ms
 Execution Time: 19182.162 ms

我在通过 psql 连接到数据库时遇到问题。我收到此错误：

[email protected]:/var/lib/pgsql # psql -d aisgdmp2
psql: error: FATAL:  database "aisgdmp2" does not exist
DETAIL:  The database subdirectory "pg_tblspc/16385/PG_13_202007201/16386" is missing.
[email protected]:/var/lib/pgsql #

但是当我连接到 posgtres 并列出数据库时，aisgdmp2 数据库存在......

[email protected]:/var/lib/pgsql # psql
psql (13.5)

postgres=# \l
                                   DB list
   Name    | Owner    | Code     |  Collation  |    CType    |   Privileges
-----------+----------+----------+-------------+-------------+-----------------------
 aisgdmp2  | aisg     | UTF8     | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =Tc/aisg             +
           |          |          |             |             | aisg=CTc/aisg
 postgres  | postgres | UTF8     | cs_CZ.UTF-8 | cs_CZ.UTF-8 |
 template0 | postgres | UTF8     | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=#

知道问题出在哪里吗？该数据库过去一直在运行，但过去两个月一直处于停机状态。

当我将点表 (n = 34, 436) 中的条目连接到 mid_pts 表 (n=198 万) 时，table_joined 的总行数为 n = 26,016。但是，当我将 n = 9,627 的 unjoined_pts 表添加到 table_joined 时，它与原始“点”表编号不匹配，超过了 1207。

以下是我拥有的表格的示例：

积分表：

mid_pts 表

下表是我所期望的，但出于某种原因，正如 Laurenz Albe 所建议的，我的点表中的一行可能与 mid_pts 表中的多行相关。我希望 points 表中的每一行仅连接到 mid_pts 表中的一个候选行。

表连接

CREATE TABLE table_joined AS
        SELECT m.geom, m.lic_li_no, m.orig_licli, p.id, p.descriptor, ... 
        FROM mid_pts m
        JOIN point p            
            ON CASE                                                     
            WHEN p.lic_li_no = m.lic_li_no THEN 1
            WHEN p.lic_li_no = m.orig_licli THEN 1
            ELSE 0 END = 1
            AND 
            (p.descriptor = m.descriptor)
    ;

当评估有多少点没有加入时，我得到 9,627。

CREATE TABLE unjoined_pts AS 
        SELECT
        p.*
        FROM point p                                                  
        WHERE NOT EXISTS (SELECT * FROM  table_joined m 
                          WHERE m.id = p.id)                    
;

我正在尝试从使用 WITH 的 plpgsql MERGE 相关操作中返回一个整数。我要返回的值在下面的 WITH 语句中 - id。我使用以下代码收到的错误如下：

关系“post_insert”不存在”

解释代码：此代码可能不是执行以下操作的最有效方法，但我正在设置一些东西以用于我认为我将需要使用 MERGE 的其他功能的某些未来用例。所有这一切都是通过 Post_Insert 创建一个帖子，然后使用该 Post_Insert（它返回帖子的 ID），我在 Hashtags_Posts 表中创建与主标签表相关的行。

“RETURN（SELECT id FROM Post_Insert）”是导致问题的原因。我似乎无法从 Post_Insert 获取 id 并将其返回。

非常感谢有关如何访问 Post_Insert 中的 id 的任何帮助。

  create or replace function createPost(
  uid_query text, 
  post_text_query text default '', 
  text_url_query text default '', 
  photo_url_query text default '', 
  video_url_query text default '', 
  audio_url_query text default '',
  location_query text default '',
  tags_array_query text[] default '{general}',  
  photo_query boolean default false
  )
returns integer language plpgsql as $$

BEGIN

WITH 
Post_Insert AS (
  INSERT INTO "Posts" (
    author, 
    post_text, 
    text_url, 
    photo_url, 
    video_url, 
    audio_url, 
    location,
    photo
  )
  VALUES (
    uid_query, 
    post_text_query, 
    text_url_query, 
    photo_url_query, 
    video_url_query, 
    audio_url_query,
    location_query,
    photo_query
  )
  RETURNING id
)

MERGE INTO "Hashtags_Posts" HP 
USING (SELECT * 
        FROM "Hashtags" H
        WHERE  H.hashtag = ANY(tags_array_query))
        as TMT
ON HP.id = (SELECT id FROM Post_Insert) AND TMT.id = HP.hashtag_id
WHEN NOT MATCHED
  THEN  INSERT (post_id, hashtag_id)
        VALUES ((SELECT id FROM Post_Insert), TMT.id);

RETURN (SELECT id FROM Post_Insert);
END 
$$

我有一个表，其中一列包含 url 的子字符串。它总是没有方案的 url 的最左边部分。

域中永远不会重叠，因此不会有 domain 的行example.net，也不会有空的域。

我想搜索并找到给定 url 的匹配行，例如example.org/sub/sub/sub/test.html它应该返回 id 为 2 的行。

到目前为止，我已经使用SELECT id FROM table WHERE 'example.org/sub/sub/sub/test.html' LIKE (domain || '%')which 给出了我想要的，但它总是对表进行 seq 扫描，即使我在该domain列上有一个索引。我希望 Postgres 可以得出一些结论并使用索引。

我怎样才能提高这个性能？

我在 Postgres 13 (RDS) 上，需要通过其 oid 值删除一个函数。这是我正在看的：

postgres> select oid,proowner::regrole,proname||'('||pg_catalog.pg_get_function_identity_arguments(oid)||')' as func from pg_proc where proname='unnest' order by oid;
   oid   | proowner |                                           func
---------+----------+------------------------------------------------------------------------------------------
    2331 | jclough  | unnest(anyarray)
    3322 | rdsadmin | unnest(tsvector tsvector, OUT lexeme text, OUT positions smallint[], OUT weights text[])
 1030822 | jclough  | unnest(anyarray)
(3 rows)

我想删除带有 oid 1030822 的函数，然后

alter function unnest(anyarray) owner to rdsadmin;

这会很棘手，因为

postgres> set role rdsadmin;
ERROR:  permission denied to set role "rdsadmin"

但这是一场未来的战斗。

一点历史可能是有序的。我们在 Postgres 8 和 9 上使用了很长时间，我实现了自己的 unnest(anyarray) 函数，因为它只存在于 >=10 的版本中。当我们从 9 跳到 13（几年后）时，我拼凑起来的 unnest 函数出现了。所以在这里我有两个版本的函数具有相同的签名。

我还应该提到我是一名伪装成 DBA 的应用程序程序员，我很糟糕。我想我将不得不直接修改 pg_proc（或其他）。有没有最不危险的方法来做到这一点？

给定以下 Postgres 容器：

docker run -i -p 54350:5432 --name test_postgres -e POSTGRES_PASSWORD=password -e POSTGRES_DB=test -e POSTGRES_SCHEMA=dba postgres:11 "-clog_statement=all"

和以下初始化 sql 脚本：

CREATE SCHEMA IF NOT EXISTS dba;

CREATE TABLE dba.mytable (mytable_id SERIAL);

运行时我得到\dt：psql

psql -U postgres -h localhost -p 54350 -d test

test=# \dt dba.mytable
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 dba    | mytable       | table | postgres
(1 row)

test=#

但是当我尝试运行时pg_dump，我得到以下信息：

pg_dump \
    --username=postgres --host=localhost --port=54350 --dbname=test \
    --schema-only \
    --no-privileges \
    --disable-triggers \
    --no-publications \
    --no-security-labels \
    --no-subscriptions \
    --no-synchronized-snapshots \
    --quote-all-identifiers \
    -t=dba.mytable > dump.sql
Password:
pg_dump: error: no matching tables were found

我也试过-t='"dba.mytable"'和-t="dba.mytable"。

我也试过-t=test.dba.mytable,-t='"test.dba.mytable"'和-t="test.dba.mytable", 但后来我明白了

pg_dump: error: cross-database references are not implemented: test.dba

如果我添加--schema=dba我得到：

pg_dump: error: no matching schemas were found

你能帮我修复我的 pg_dump 命令以便允许我转储表吗dba.mytable？

谢谢！

我在 PostgresSQL 上有一个包含数百万行的表。该表有列 a 和 b。我已经在 a 和 b 上有一个唯一索引：

CONSTRAINT a_b UNIQUE (a, b)

我想在 a 和 b 列上添加一个复合主键。

如果我做一个

ALTER TABLE table_name ADD PRIMARY KEY (a, b);