主页 / user-2179

Zilk's questions

Martin Hope
Zilk
Asked: 2017-10-10 08:27:36 +0800 CST
  • 2

（缩写）表摘要：

-- Table cases:
     id                  SERIAL       PRIMARY KEY,
     application_number  VARCHAR(30)  NOT NULL,
     publication_number  VARCHAR(30)  NOT NULL,

-- Table patents:
     case_id                 INTEGER      PRIMARY KEY,  -- FK to cases(id)
     pct_application_number  VARCHAR(30)  NOT NULL,
     pct_publication_number  VARCHAR(30)  NOT NULL,

-- All character columns have working trigram indexes:
     CREATE INDEX cases_application_number_trgm_idx ON cases
         USING GIN (application_number gin_trgm_ops);
         -- (etc)

以下查询很慢（~200 毫秒），因为它不使用索引：

SELECT  c.id
  FROM  cases c
        JOIN patents p ON p.case_id = c.id
 WHERE  c.application_number ILIKE '%1234%' OR p.pct_application_number ILIKE '%1234%'

以下查询都很快（1-2 毫秒）：

-- AND instead of OR
WHERE  c.application_number ILIKE '%1234%' AND p.pct_application_number ILIKE '%1234%'

-- OR, but only table "cases"
WHERE  c.application_number ILIKE '%1234%' OR c.publication_number ILIKE '%1234%'

-- OR, but only table "patents"
WHERE  p.pct_application_number ILIKE '%1234%' OR p.pct_publication_number ILIKE '%1234%'

-- Simulating the OR with a UNION
SELECT  c.id
  FROM  cases c
        JOIN patents p ON p.case_id = c.id
 WHERE  c.application_number ILIKE '%1234%'
 UNION
SELECT  c.id
  FROM  cases c
        JOIN patents p ON p.case_id = c.id
 WHERE  p.pct_application_number ILIKE '%1234%'

这是EXPLAIN ANALYZE慢速查询的输出：

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=2329.80..10590.54 rows=7 width=4) (actual time=54.951..186.713 rows=35 loops=1)
   Hash Cond: (c.id = p.case_id)
   Join Filter: (((c.application_number)::text ~~* '%1234%'::text) OR ((p.pct_application_number)::text ~~* '%1234%'::text))
   Rows Removed by Join Filter: 68223
   ->  Seq Scan on cases c  (cost=0.00..4981.99 rows=142099 width=12) (actual time=0.011..32.875 rows=142099 loops=1)
   ->  Hash  (cost=1142.58..1142.58 rows=68258 width=11) (actual time=31.105..31.105 rows=68258 loops=1)
         Buckets: 131072  Batches: 2  Memory Usage: 2473kB
         ->  Seq Scan on patents p  (cost=0.00..1142.58 rows=68258 width=11) (actual time=0.019..11.995 rows=68258 loops=1)
 Planning time: 1.875 ms
 Execution time: 186.780 ms
(10 rows)

此处发布的查询已大大减少以说明问题。实际查询更复杂，涉及在五个（或更多）表的六个（或更多）列中进行文本搜索，大约有 10 个输出列。我想我可以将所有这些重写为一系列查询并将它们连接成一个巨大的UNION......有没有更好的方法来处理这个问题？

添加enable_seqscan禁用的查询计划（根据要求）：

                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=0.71..18767.92 rows=7 width=4) (actual time=4.809..150.368 rows=35 loops=1)
   Merge Cond: (c.id = p.case_id)
   Join Filter: (((c.application_number)::text ~~* '%1234%'::text) OR ((p.pct_application_number)::text ~~* '%1234%'::text))
   Rows Removed by Join Filter: 68223
   ->  Index Scan using cases_pkey on cases c  (cost=0.42..14942.96 rows=142099 width=12) (actual time=0.004..32.695 rows=142097 loops=1)
   ->  Index Scan using patents_pkey on patents p  (cost=0.29..2275.63 rows=68258 width=11) (actual time=0.003..11.942 rows=68258 loops=1)
 Planning time: 1.007 ms
 Execution time: 150.399 ms
(8 rows)
postgresql index
Martin Hope
Zilk
Asked: 2016-12-13 10:54:10 +0800 CST
  • 3

DB结构总结：

  • 主表是cases（大约 136k 行）
  • 每个案例可以有 0 - n 引用表中的行case_contacts
  • 每个案例联系人引用表中的主要联系人contacts
  • 案例联系人也可以引用辅助子联系人，也在表格中contacts
  • 联系人的姓名在 中contacts.v_fullname，使用三元组索引进行索引

目标是查找联系人或子联系人的名称包含字符串“test”的情况：

SELECT  c.id,
        c.number
  FROM  cases c
        JOIN case_contacts caco ON caco.case_id = c.id
        JOIN contacts con_main ON con_main.id = caco.contact_id
        LEFT JOIN contacts con_sub ON con_sub.id = caco.subcontact_id
 WHERE  con_main.v_fullname ILIKE '%test%'
        OR con_sub.v_fullname ILIKE '%test%'

此查询（查询计划）返回正确的结果，但不使用三元索引。大约需要 330 毫秒。

删除任何一个匹配条件（查询计划），或者让它们指向同一个表（查询计划），都可以解决性能问题。这两个都使用三元索引并在 1ms 内执行，但不解决给定的任务。

如何让 PostgreSQL 使用我的索引？

我已将此示例简化为演示效果所需的最低限度。实际的查询要复杂得多（并且部分是自动生成的），因此如果可能的话，使用两个查询的 UNION 并且每个查询只有一个文本匹配会非常困难。

我正在使用 PostgreSQL 9.5.5。
模式仍然可以修改（在某种程度上）。

根据要求，有关索引的更多信息：

dbname=# \di+ *contacts*
                                         List of relations
 Schema |               Name               | Type  | Owner |     Table     |  Size
--------+----------------------------------+-------+-------+---------------+---------
 public | case_contacts_case_id_idx        | index | x     | case_contacts | 4544 kB
 public | case_contacts_contact_id_idx     | index | x     | case_contacts | 4544 kB
 public | case_contacts_id_case_id_idx     | index | x     | case_contacts | 4544 kB
 public | case_contacts_idx                | index | x     | case_contacts | 9608 kB
 public | case_contacts_pkey               | index | x     | case_contacts | 4544 kB
 public | case_contacts_reference_trgm_idx | index | x     | case_contacts | 4960 kB
 public | case_contacts_subcontact_id_idx  | index | x     | case_contacts | 4544 kB
 public | case_contacts_type_idx           | index | x     | case_contacts | 6208 kB
 public | case_contacts_unique_types_idx   | index | x     | case_contacts | 5464 kB
 public | contacts_parent_id_id_idx        | index | x     | contacts      | 456 kB
 public | contacts_parent_id_idx           | index | x     | contacts      | 360 kB
 public | contacts_pkey                    | index | x     | contacts      | 360 kB
 public | contacts_v_fullname_trgm_idx     | index | x     | contacts      | 1560 kB
(13 rows)

这就是在 contacts.v_fullname 上创建索引的方式：

CREATE INDEX contacts_v_fullname_trgm_idx ON contacts USING GIN (v_fullname gin_trgm_ops);
postgresql index
Martin Hope
Zilk
Asked: 2016-10-27 07:57:17 +0800 CST
  • 14

这是一个简单的表，其中记录可以引用同一表中的父记录：

CREATE TABLE foo (
    id         SERIAL  PRIMARY KEY,
    parent_id  INT     NULL,
    num        INT     NOT NULL,
    txt        TEXT    NULL,
    FOREIGN KEY (parent_id) REFERENCES foo(id)
);

加上其他字段值之一（num）在父记录和子记录之间必须相同的附加要求，我认为复合外键应该可以解决问题。我将最后一行更改为

    FOREIGN KEY (parent_id, num) REFERENCES foo(id, num)

并得到ERROR: there is no unique constraint matching given keys for referenced table "foo"。

我可以很容易地添加这个约束，但我不明白为什么它是必要的，当引用的列之一 ( id) 已经保证是唯一的时？在我看来，新的约束将是多余的。

postgresql foreign-key
Martin Hope
Zilk
Asked: 2015-12-19 09:18:01 +0800 CST
  • 3

简而言之：

  • 开发笔记本电脑坏了
  • 旧硬盘仍然可读
  • 新笔记本电脑有 PostgreSQL 9.4 而不是 9.1
  • 两台笔记本电脑都使用 Ubuntu Linux 的风格

问题 1：我的理解是否正确，为了恢复旧数据，我需要一台与写入旧数据目录的服务器具有相同主版本和次版本的服务器？由于我的发行版没有可用的 9.1 包，我必须从源代码编译一个 v9.1.x 服务器，复制旧数据，启动服务器并执行正常的 pg_dump，然后可以将其恢复到新集群？

问题2：旧笔记本上大概有10-15个数据库，但因为是开发机，理论上除了一些本地实验外，所有的数据都应该是可以替换的。我正在考虑只是废弃旧数据，但我不能 100% 确定那些数据库是什么。有没有办法在不运行 9.1 服务器的情况下从旧数据目录中提取一些基本信息（例如数据库名称，甚至大小或时间戳）？

postgresql linux
Martin Hope
Zilk
Asked: 2011-06-21 14:53:22 +0800 CST
  • 60

首先，我是一名开发人员，而不是 DBA 或系统管理员；请温柔:)

我正在开发一个应用程序工作流程，其中单个用户操作将触发数据库中的复杂更改 - 在某些表中创建数百条记录，在其他表中更新数百条记录等。总而言之，大约 12 个表（约 100 ) 被这个动作感动。由于复杂性，我很难在运行另一个测试之前手动还原所有更改。在我的大部分开发时间里，我可以简单地在工作流的末尾插入一个“ROLLBACK”语句，但是当我接近提交我的更改时，我需要测试真实的东西。

我有一个生产数据库的本地副本可以使用。就我而言，在测试之间转储和恢复比编写脚本来撤消所有更改要快。它更快，但它仍然让我慢了很多（在我老化的笔记本电脑上恢复大约需要 20 分钟）。有什么办法可以保存数据库当前状态的快照，然后快速恢复它？

我保证是系统上唯一的用户，并且我有 root 访问权限。tar'ed 和 gzip'ed 后，数据库转储约为 100MB。PostgreSQL 版本是 8.3。

提前感谢您提供任何有用的想法。

postgresql restore