主页 / user-2542

voretaq7's questions

Martin Hope
voretaq7
Asked: 2013-06-08 13:07:37 +0800 CST
  • 2

我有一个 Postgres 8.4 环境，我们所有数据库的编码都设置为SQL_ASCII- 我们终于迁移到 Postgres 9.2，我想将所有内容迁移到UTF8编码。

不幸的是，这个数据库中的文本数据不干净——尝试将 pg_dump 恢复到 utf8 编码的数据库会引发有关无效字节序列的错误，即使我--encoding=UTF8在运行 pg_dump 时指定（可能是因为 Postgres 不知道该怎么做他们并只是将它们原封不动地转储？）。

我们有很多数据（超过一百万行带有文本/字符串元素），手动审核所有这些数据将非常耗时（并且容易出错），所以如果可能的话，我希望将其自动化。

有没有一种简单的方法可以在数据库中找到不符合 utf8 的字符串/文本字段，以便我们修复它们？还是我坚持进行手动审核以解决这个问题？

postgresql encoding
Martin Hope
voretaq7
Asked: 2012-04-27 14:22:15 +0800 CST
  • 4

我有一个中等大小的数据库，分布在几个表上，粗略的架构是：

  • 输入数据（数据 ID、会话 ID 和一些具有统计重要性的字段）
  • 输入文件（数据 ID 和 blob）
  • 第 1 阶段输出文件（数据 ID 和 blob）
  • 第 2 阶段输出文件（数据 ID 和 blob）
  • 第 1 类结果（数据 ID 和一些布尔值）
  • 2 类结果（数据 ID 和一些整数）
  • 第 3 类结果（数据 ID 和一些整数）

每个表有约 200,000 行。

我还有一个视图，它基本上将所有这些粘合在一起，以便我可以SELECT使用一堆 ID（通常根据会话 ID 选择它们）并在一个页面上查看所有相关数据。

视图工作正常，查询计划的索引利用率看起来很正常，但结果并不快：

> EXPLAIN ANALYZE SELECT(*) FROM overlay WHERE test_session=12345;

                 QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Right Join  (cost=7.19..74179.49 rows=10 width=305) (actual time=10680.129..10680.494 rows=4 loops=1)
   Merge Cond: (p.data_id = d.id)
   ->  Merge Join  (cost=7.19..75077.04 rows=183718 width=234) (actual time=0.192..10434.995 rows=173986 loops=1)
         Merge Cond: (p.data_id = input_file.data_id)
         ->  Merge Join  (cost=7.19..69917.74 rows=183718 width=222) (actual time=0.173..9255.653 rows=173986 loops=1)
               Merge Cond: (p.data_id = stage1_output_file.data_id)
               ->  Merge Join  (cost=5.50..62948.54 rows=183718 width=186) (actual time=0.153..8081.949 rows=173986 loops=1)
                     Merge Cond: (p.data_id = stage2_output_file.data_id)
                     ->  Merge Join  (cost=3.90..55217.36 rows=183723 width=150) (actual time=0.132..6918.814 rows=173986 loops=1)
                           Merge Cond: (p.data_id = stage3_output_file.data_id)
                           ->  Nested Loop  (cost=2.72..47004.01 rows=183723 width=114) (actual time=0.111..5753.105 rows=173986 loops=1)
                                 Join Filter: (p.impression = istr.id)
                                 ->  Merge Join  (cost=1.68..30467.90 rows=183723 width=102) (actual time=0.070..2675.733 rows=173986 loops=1)
                                       Merge Cond: (p.data_id = s.data_id)
                                       ->  Merge Join  (cost=1.68..19031.56 rows=183723 width=58) (actual time=0.049..1501.546 rows=173986 loops=1)
                                             Merge Cond: (p.data_id = t.data_id)
                                             ->  Index Scan using Category1_Results_pkey on Category1_Results p  (cost=0.00..7652.17 rows=183723 width=18) (actual time=0.025..315.531 rows=173986 loops=1)
                                             ->  Index Scan using Category3_Results_pkey on Category3_Results t  (cost=0.00..8624.43 rows=183787 width=40) (actual time=0.016..321.460 rows=173986 loops=1)
                                       ->  Index Scan using Category2_Results_pkey on Category2_Results s  (cost=0.00..8681.47 rows=183787 width=44) (actual time=0.014..320.794 rows=173986 loops=1)
                                 ->  Materialize  (cost=1.04..1.08 rows=4 width=20) (actual time=0.001..0.007 rows=4 loops=173986)
                                       ->  Seq Scan on Category1_impression_str istr  (cost=0.00..1.04 rows=4 width=20) (actual time=0.005..0.012 rows=4 loops=1)
                           ->  Index Scan using Stage3_Output_file_pkey on Stage3_Output_file stage3  (cost=0.00..8178.35 rows=183871 width=36) (actual time=0.015..317.698 rows=173986 loops=1)
                     ->  Index Scan using analysis_file_pkey on analysis_file Stage2_Output  (cost=0.00..8168.99 rows=183718 width=36) (actual time=0.014..317.776 rows=173986 loops=1)
               ->  Index Scan using Stage1_output_file_pkey on Stage1_output_file stg1  (cost=0.00..8199.07 rows=183856 width=36) (actual time=0.014..321.648 rows=173986 loops=1)
         ->  Index Scan using input_file_pkey on input_file input  (cost=0.00..8618.05 rows=183788 width=36) (actual time=0.014..328.968 rows=173986 loops=1)
   ->  Materialize  (cost=0.00..39.59 rows=10 width=75) (actual time=0.046..0.150 rows=4 loops=1)
         ->  Nested Loop Left Join  (cost=0.00..39.49 rows=10 width=75) (actual time=0.039..0.128 rows=4 loops=1)
               Join Filter: (t.id = d.input_quality)
               ->  Index Scan using input_data_exists_index on input_data d  (cost=0.00..28.59 rows=10 width=45) (actual time=0.013..0.025 rows=4 loops=1)
                     Index Cond: (test_session = 1040)
               ->  Seq Scan on quality_codes t  (cost=0.00..1.04 rows=4 width=38) (actual time=0.002..0.009 rows=4 loops=4)
 Total runtime: 10680.902 ms

其基础视图是我们的“完整结果”视图，定义为：

 SELECT p.data_id, p.x2, istr.str AS impression, input.h, p.x3, p.x3, p.x4, s.x5,
        s.x6, s.x7, s.x8, s.x9, s.x10, s.x11, s.x12, s.x13, s.x14, t.x15,
        t.x16, t.x17, t.x18, t.x19, t.x20, t.x21, t.x22, t.x23,
        input.data AS input, stage1_output_file.data AS stage1, 
        stage2_output_file.data AS stage2, stage3_output_file.data AS stage3
FROM category1_results p, category1_impression_str istr, input_file input,
     stage1_output_file, stage2_output_file, stage3_output_file, 
     category2_results s, category3_results t
 WHERE p.impression = istr.id AND p.data_id = input.data_id AND p.data_id = stage1_output_file.data_id
       AND p.data_id = stage2_output_file.data_id AND p.data_id = stage3_output_file.data_id AND p.data_id = s.data_id AND p.data_id = t.data_id;

以及生成上述查询计划的覆盖视图，定义为：

 SELECT d.data_id, d.test_session, d.a, d.b, t.c, d.d, d.e, d.f, r.*
 FROM input_data d LEFT JOIN quality_codes t ON t.id = d.input_quality
      LEFT JOIN full_results r ON r.data_id = d.data_id  
 WHERE NOT d.deleted;

我们似乎在整个链条中的大部分时间都在加入我们的整个数据集，我非常确信这是我们的性能问题——有人对优化这只猪的方法有什么建议吗？

postgresql performance