AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • 主页
  • 系统&网络
  • Ubuntu
  • Unix
  • DBA
  • Computer

Mobile menu

Close
  • 主页
  • 系统&网络
    • 最新
    • 热门
    • 标签
  • Ubuntu
    • 最新
    • 热门
    • 标签
  • Unix
    • 最新
    • 标签
  • DBA
    • 最新
    • 标签
  • Computer
    • 最新
    • 标签
主页 / dba / 问题

问题[postgresql](dba)

Martin Hope
Deekor
Asked: 2023-03-27 03:50:59 +0800 CST

查询较小的子集以扫描时间呈指数增长?

  • 6

我有一个大表,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);
postgresql
  • 1 个回答
  • 48 Views
Martin Hope
NewLearner
Asked: 2023-03-25 03:40:32 +0800 CST

Unnest 在插入数据时不保留空值或空值

  • 5

我有一个 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);

但我的桌子看起来像

ID 姓名 电话
1个 萨姆 123
2个 内存 无效的

我需要我的表格如下所示

ID 姓名 电话
1个 萨姆 无效的
2个 内存 123

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

postgresql
  • 1 个回答
  • 15 Views
Martin Hope
valex
Asked: 2023-03-24 23:46:01 +0800 CST

或条件下的序列扫描

  • 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
postgresql
  • 3 个回答
  • 36 Views
Martin Hope
Misudka
Asked: 2023-03-23 17:28:07 +0800 CST

psql 无法连接到现有数据库

  • 8

我在通过 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=#

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

postgresql
  • 2 个回答
  • 130 Views
Martin Hope
MJM
Asked: 2023-03-22 11:20:13 +0800 CST

sql连接中的总行数不匹配

  • 6

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

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

积分表:

LIC_LI_NO 描述符 属性 1 属性 2 属性 3
11112-01 04-81-09-01 xx xyz xx
11112-01 04-81-09-01 xyz X X
11119-03 04-81-09-01 xx xx xyz
11117-05 04-81-09-01 xx xyz xyz

mid_pts 表

LIC_LI_NO ORIG_LICLI 描述符
11112-01 11112-01 04-81-09-01
11112-01 11112-01 04-81-09-01
11119-03 11119-03 04-81-09-01
129517-05 11117-05 04-81-09-01
10000-01 10000-01 04-81-09-01
100232-01 100232-01 04-81-09-01
19-03 19-03 04-81-09-01
117-05 117-05 04-81-09-01
112-01 112-01 04-81-09-01

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

表连接

LIC_LI_NO ORIG_LICLI 描述符 属性 1 属性 2 属性 3
11112-01 11112-01 04-81-09-01 xx xyz xx
11112-01 11112-01 04-81-09-01 xyz X X
11119-03 11119-03 04-81-09-01 xx xx xyz
129517-05 11117-05 04-81-09-01 xx xyz xyz
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)                    
;
postgresql
  • 1 个回答
  • 52 Views
Martin Hope
DbMajor
Asked: 2023-03-18 22:12:27 +0800 CST

PL/pgSQL 中 WITH 子句的返回值

  • 6

我正在尝试从使用 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 
$$
postgresql
  • 1 个回答
  • 57 Views
Martin Hope
Zyberzero
Asked: 2023-03-18 00:58:48 +0800 CST

用于在列以提供的值开头的列中搜索的索引

  • 7

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

ID 领域 专栏 1 专栏2
1个 例子.com 价值1 值2
2个 例子.org 价值3 值4
3个 example.net/zh-CN 值5 价值6
4个 example.net/zh-CN 值7 价值8

域中永远不会重叠,因此不会有 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 可以得出一些结论并使用索引。

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

postgresql
  • 2 个回答
  • 45 Views
Martin Hope
Jeff Clough
Asked: 2023-03-17 05:45:50 +0800 CST

需要通过 oid 值删除函数(我认为)

  • 6

我在 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(或其他)。有没有最不危险的方法来做到这一点?

postgresql
  • 1 个回答
  • 23 Views
Martin Hope
Philippe Hebert
Asked: 2023-03-16 23:14:43 +0800 CST

pg_dump: 错误: 没有找到匹配的表

  • 5

给定以下 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?

谢谢!

postgresql
  • 1 个回答
  • 44 Views
Martin Hope
gkatzioura
Asked: 2023-03-16 05:05:15 +0800 CST

在现有 PostgreSQL 表上添加复合主键

  • 7

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

CONSTRAINT a_b UNIQUE (a, b)

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

如果我做一个

ALTER TABLE table_name ADD PRIMARY KEY (a, b);
postgresql
  • 1 个回答
  • 45 Views

Sidebar

Stats

  • 问题 199037
  • 回答 263511
  • 最佳答案 131755
  • 用户 66345
  • 热门
  • 回答
  • Marko Smith

    连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目

    • 12 个回答
  • Marko Smith

    如何让sqlplus的输出出现在一行中?

    • 3 个回答
  • Marko Smith

    选择具有最大日期或最晚日期的日期

    • 3 个回答
  • Marko Smith

    如何列出 PostgreSQL 中的所有模式?

    • 4 个回答
  • Marko Smith

    列出指定表的所有列

    • 5 个回答
  • Marko Smith

    如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

    • 4 个回答
  • Marko Smith

    你如何mysqldump特定的表?

    • 4 个回答
  • Marko Smith

    使用 psql 列出数据库权限

    • 10 个回答
  • Marko Smith

    如何从 PostgreSQL 中的选择查询中将值插入表中?

    • 4 个回答
  • Marko Smith

    如何使用 psql 列出所有数据库和表?

    • 7 个回答
  • Martin Hope
    Jin 连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目 2014-12-02 02:54:58 +0800 CST
  • Martin Hope
    Stéphane 如何列出 PostgreSQL 中的所有模式? 2013-04-16 11:19:16 +0800 CST
  • Martin Hope
    Mike Walsh 为什么事务日志不断增长或空间不足? 2012-12-05 18:11:22 +0800 CST
  • Martin Hope
    Stephane Rolland 列出指定表的所有列 2012-08-14 04:44:44 +0800 CST
  • Martin Hope
    haxney MySQL 能否合理地对数十亿行执行查询? 2012-07-03 11:36:13 +0800 CST
  • Martin Hope
    qazwsx 如何监控大型 .sql 文件的导入进度? 2012-05-03 08:54:41 +0800 CST
  • Martin Hope
    markdorison 你如何mysqldump特定的表? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 对 SQL 查询进行计时? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas 如何从 PostgreSQL 中的选择查询中将值插入表中? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 列出所有数据库和表? 2011-02-18 00:45:49 +0800 CST

热门标签

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • 主页
  • 问题
    • 最新
    • 热门
  • 标签
  • 帮助

Footer

AskOverflow.Dev

关于我们

  • 关于我们
  • 联系我们

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve