postgresql 在postgres中优化sql查询

jgwigjjp  于 2023-10-18  发布在  PostgreSQL
关注(0)|答案(3)|浏览(143)

我在PostgreSQL中执行下面的查询,但它比Oracle花费更多的时间。在PostgreSQL中需要1.5秒,在Oracle中需要0.009秒。

  1. select ex1.currency_id,
  2. ex1.underly_currency_id,
  3. ex1.type_id,
  4. ex1.third_id,
  5. ex1.market_third_id,
  6. ex1.exch_d,
  7. ex1.daily_dflt_f,
  8. ex1.exch_rate,
  9. ex1.external_seq_no,
  10. ex1.creation_d,
  11. ex1.creation_user_id,
  12. ex1.last_modif_d,
  13. ex1.last_user_id
  14. /* security_level_e = 0 */
  15. FROM aaamaindb.exch_rate ex1
  16. where ((ex1.currency_id = 1)
  17. and ( ex1.underly_currency_id = 2)
  18. and ( ex1.exch_d >= '2003/03/27 00:00:00')
  19. and ( ex1.exch_d <= '2006/01/15 00:00:00')
  20. and ( exch_d = ( select max(ex2.exch_d)
  21. from aaamaindb.exch_rate ex2
  22. where ex1.currency_id = ex2.currency_id
  23. and ex1.underly_currency_id = ex2.underly_currency_id
  24. and ex2.exch_d >= '2003/03/27 00:00:00' and ex2.exch_d <= '2006/01/15 00:00:00'
  25. and (ex1.type_id = ex2.type_id
  26. or (ex1.type_id is NULL and ex2.type_id is NULL)) /*PMSTA07173-EFE-081002*/
  27. and (ex1.third_id = ex2.third_id
  28. or (ex1.third_id is NULL and ex2.third_id is NULL))
  29. and (ex1.market_third_id = ex2.market_third_id
  30. or (ex1.market_third_id is NULL and ex2.market_third_id is NULL))
  31. group by type_id, third_id, market_third_id )))
  32. order by exch_d desc;

exch_rate在postgres和oracle中都有45000条记录。
下面是在exch_rate表上创建的索引。

  1. CREATE UNIQUE INDEX IF NOT EXISTS exch_rate_bk_idx
  2. ON aaamaindb.exch_rate USING btree
  3. (currency_id ASC NULLS LAST, exch_d ASC NULLS LAST, underly_currency_id ASC NULLS LAST, type_id ASC NULLS LAST, third_id ASC NULLS LAST, market_third_id ASC NULLS LAST)
  4. TABLESPACE pg_default;

我需要优化,这个SQL查询工作一样快,甲骨文。
查询计划

  1. "Sort (cost=31883.98..31883.99 rows=4 width=97) (actual time=582.557..582.559 rows=1 loops=1)"
  2. " Sort Key: ex1.exch_d DESC"
  3. " Sort Method: quicksort Memory: 25kB"
  4. " -> Bitmap Heap Scan on exch_rate ex1 (cost=54.58..31883.94 rows=4 width=97) (actual time=540.145..582.553 rows=1 loops=1)"
  5. " Recheck Cond: ((currency_id = 1) AND (exch_d >= '2003-03-27 00:00:00'::timestamp without time zone) AND (exch_d <= '2006-01-15 00:00:00'::timestamp without time zone) AND (underly_currency_id = 2))"
  6. " Filter: (exch_d = (SubPlan 1))"
  7. " Rows Removed by Filter: 1025"
  8. " Heap Blocks: exact=21"
  9. " -> Bitmap Index Scan on exch_rate_bk_idx (cost=0.00..54.57 rows=825 width=0) (actual time=0.126..0.127 rows=1026 loops=1)"
  10. " Index Cond: ((currency_id = 1) AND (exch_d >= '2003-03-27 00:00:00'::timestamp without time zone) AND (exch_d <= '2006-01-15 00:00:00'::timestamp without time zone) AND (underly_currency_id = 2))"
  11. " SubPlan 1"
  12. " -> GroupAggregate (cost=37.66..37.69 rows=1 width=32) (actual time=0.566..0.566 rows=1 loops=1026)"
  13. " Group Key: ex2.type_id, ex2.third_id, ex2.market_third_id"
  14. " -> Sort (cost=37.66..37.67 rows=1 width=32) (actual time=0.411..0.457 rows=1026 loops=1026)"
  15. " Sort Key: ex2.type_id, ex2.third_id, ex2.market_third_id"
  16. " Sort Method: quicksort Memory: 97kB"
  17. " -> Index Only Scan using exch_rate_bk_idx on exch_rate ex2 (cost=0.41..37.65 rows=1 width=32) (actual time=0.009..0.257 rows=1026 loops=1026)"
  18. " Index Cond: ((currency_id = ex1.currency_id) AND (exch_d >= '2003-03-27 00:00:00'::timestamp without time zone) AND (exch_d <= '2006-01-15 00:00:00'::timestamp without time zone) AND (underly_currency_id = ex1.underly_currency_id))"
  19. " Filter: (((ex1.type_id = type_id) OR ((ex1.type_id IS NULL) AND (type_id IS NULL))) AND ((ex1.third_id = third_id) OR ((ex1.third_id IS NULL) AND (third_id IS NULL))) AND ((ex1.market_third_id = market_third_id) OR ((ex1.market_third_id IS NULL) AND (market_third_id IS NULL))))"
  20. " Heap Fetches: 0"
  21. "Planning Time: 0.278 ms"
  22. "Execution Time: 582.691 ms"

还有table的垫子

  1. CREATE TABLE IF NOT EXISTS aaamaindb.exch_rate
  2. (
  3. currency_id bigint NOT NULL,
  4. underly_currency_id bigint NOT NULL,
  5. type_id bigint,
  6. third_id bigint,
  7. market_third_id bigint,
  8. exch_d timestamp without time zone NOT NULL,
  9. daily_dflt_f smallint NOT NULL DEFAULT 0,
  10. exch_rate numeric(23,14) NOT NULL,
  11. external_seq_no bigint,
  12. creation_d timestamp without time zone,
  13. creation_user_id bigint,
  14. last_modif_d timestamp without time zone,
  15. last_user_id bigint,
  16. CONSTRAINT fk_701001 FOREIGN KEY (currency_id)
  17. REFERENCES aaamaindb.currency (id) MATCH SIMPLE
  18. ON UPDATE NO ACTION
  19. ON DELETE CASCADE,
  20. CONSTRAINT fk_701002 FOREIGN KEY (underly_currency_id)
  21. REFERENCES aaamaindb.currency (id) MATCH SIMPLE
  22. ON UPDATE NO ACTION
  23. ON DELETE CASCADE,
  24. CONSTRAINT fk_701003 FOREIGN KEY (type_id)
  25. REFERENCES aaamaindb.type (id) MATCH SIMPLE
  26. ON UPDATE NO ACTION
  27. ON DELETE RESTRICT,
  28. CONSTRAINT fk_701004 FOREIGN KEY (third_id)
  29. REFERENCES aaamaindb.third_party (id) MATCH SIMPLE
  30. ON UPDATE NO ACTION
  31. ON DELETE RESTRICT,
  32. CONSTRAINT fk_701005 FOREIGN KEY (market_third_id)
  33. REFERENCES aaamaindb.third_party (id) MATCH SIMPLE
  34. ON UPDATE NO ACTION
  35. ON DELETE RESTRICT,
  36. CONSTRAINT exch_rate_daily_dflt_chk CHECK (daily_dflt_f = ANY (ARRAY[0, 1]))
  37. )
  38. TABLESPACE pg_default;
  39. ALTER TABLE IF EXISTS aaamaindb.exch_rate
  40. OWNER to v_gen_reg1aaa;
  41. GRANT ALL ON TABLE aaamaindb.exch_rate TO v_gen_reg1aaa;
  42. -- Index: exch_rate_bk_idx
  43. -- DROP INDEX IF EXISTS aaamaindb.exch_rate_bk_idx;
  44. CREATE UNIQUE INDEX IF NOT EXISTS exch_rate_bk_idx
  45. ON aaamaindb.exch_rate USING btree
  46. (currency_id ASC NULLS LAST, exch_d ASC NULLS LAST, underly_currency_id ASC NULLS LAST, type_id ASC NULLS LAST, third_id ASC NULLS LAST, market_third_id ASC NULLS LAST)
  47. TABLESPACE pg_default;
  48. -- Trigger: d_after_exch_rate_trg
  49. -- DROP TRIGGER IF EXISTS d_after_exch_rate_trg ON aaamaindb.exch_rate;
  50. CREATE TRIGGER d_after_exch_rate_trg
  51. AFTER DELETE
  52. ON aaamaindb.exch_rate
  53. FOR EACH STATEMENT
  54. EXECUTE FUNCTION aaamaindb.d_after_exch_rate_trg();
  55. -- Trigger: i_after_exch_rate_trg
  56. -- DROP TRIGGER IF EXISTS i_after_exch_rate_trg ON aaamaindb.exch_rate;
  57. CREATE TRIGGER i_after_exch_rate_trg
  58. AFTER INSERT
  59. ON aaamaindb.exch_rate
  60. FOR EACH STATEMENT
  61. EXECUTE FUNCTION aaamaindb.i_after_exch_rate_trg();
  62. -- Trigger: i_before_row_exch_rate_trg
  63. -- DROP TRIGGER IF EXISTS i_before_row_exch_rate_trg ON aaamaindb.exch_rate;
  64. CREATE TRIGGER i_before_row_exch_rate_trg
  65. BEFORE INSERT
  66. ON aaamaindb.exch_rate
  67. FOR EACH ROW
  68. EXECUTE FUNCTION aaamaindb.i_before_row_exch_rate_trg();
  69. -- Trigger: u_after_exch_rate_trg
  70. -- DROP TRIGGER IF EXISTS u_after_exch_rate_trg ON aaamaindb.exch_rate;
  71. CREATE TRIGGER u_after_exch_rate_trg
  72. AFTER UPDATE
  73. ON aaamaindb.exch_rate
  74. FOR EACH STATEMENT
  75. EXECUTE FUNCTION aaamaindb.u_after_exch_rate_trg();
  76. -- Trigger: u_before_row_exch_rate_trg
  77. -- DROP TRIGGER IF EXISTS u_before_row_exch_rate_trg ON aaamaindb.exch_rate;
  78. CREATE TRIGGER u_before_row_exch_rate_trg
  79. BEFORE UPDATE
  80. ON aaamaindb.exch_rate
  81. FOR EACH ROW
  82. EXECUTE FUNCTION aaamaindb.u_before_row_exch_rate_trg();
  83. Below is Oracle Explain plan
  84. OPERATION OBJECT_NAME OPTIONS CARDINALITY COST
  85. SELECT STATEMENT
  86. 1 92
  87. TABLE ACCESS
  88. AAAMAINDB.EXCH_RATE BY INDEX ROWID 1 8
  89. INDEX
  90. AAAMAINDB.EXCH_RATE_BK_IDX RANGE SCAN DESCENDING 1 7
  91. Access Predicates
  92. AND
  93. EX1.CURRENCY_ID=1
  94. EX1.EXCH_D>=TIMESTAMP' 2003-03-27 00:00:00'
  95. EX1.UNDERLY_CURRENCY_ID=2
  96. EX1.EXCH_D<=TIMESTAMP' 2006-01-15 00:00:00'
  97. Filter Predicates
  98. AND
  99. EX1.UNDERLY_CURRENCY_ID=2
  100. EXCH_D= (SELECT MAX(EX2.EXCH_D) FROM AAAMAINDB.EXCH_RATE EX2 WHERE EX2.EXCH_D<=TIMESTAMP' 2006-01-15 00:00:00' AND EX2.EXCH_D>=TIMESTAMP' 2003-03-27 00:00:00' AND EX2.CURRENCY_ID=:B1 AND EX2.UNDERLY_CURRENCY_ID=:B2 AND (:B3 IS NULL AND EX2.MARKET_THIRD_ID IS NULL OR EX2.MARKET_THIRD_ID=:B4) AND (EX2.TYPE_ID=:B5 OR EX2.TYPE_ID IS NULL AND :B6 IS NULL) AND (EX2.THIRD_ID=:B7 OR :B8 IS NULL AND EX2.THIRD_ID IS NULL) GROUP BY TYPE_ID,THIRD_ID,MARKET_THIRD_ID)
  101. HASH
  102. GROUP BY 1 4
  103. INDEX
  104. AAAMAINDB.EXCH_RATE_BK_IDX RANGE SCAN 1 3
  105. Access Predicates
  106. AND
  107. EX2.CURRENCY_ID=:B1
  108. EX2.EXCH_D>=TIMESTAMP' 2003-03-27 00:00:00'
  109. EX2.UNDERLY_CURRENCY_ID=:B2
  110. EX2.EXCH_D<=TIMESTAMP' 2006-01-15 00:00:00'
  111. Filter Predicates
  112. AND
  113. EX2.UNDERLY_CURRENCY_ID=:B1
  114. OR
  115. AND
  116. :B2 IS NULL
  117. EX2.MARKET_THIRD_ID IS NULL
  118. EX2.MARKET_THIRD_ID=:B3
  119. OR
  120. EX2.TYPE_ID=:B4
  121. AND
  122. EX2.TYPE_ID IS NULL
  123. :B5 IS NULL
  124. OR
  125. EX2.THIRD_ID=:B6
  126. AND
  127. :B7 IS NULL
  128. EX2.THIRD_ID IS NULL
  129. Other XML
  130. {info}
  131. info type="db_version"
  132. 12.2.0.1
  133. info type="parse_schema"
  134. "SYS"
  135. info type="plan_hash_full"
  136. 3996335147
  137. info type="plan_hash"
  138. 3334517769
  139. info type="plan_hash_2"
  140. 3996335147
  141. {hint}
  142. USE_HASH_AGGREGATION(@"SEL$2")
  143. INDEX(@"SEL$2" "EX2"@"SEL$2" ("EXCH_RATE"."CURRENCY_ID" "EXCH_RATE"."EXCH_D" "EXCH_RATE"."UNDERLY_CURRENCY_ID" "EXCH_RATE"."TYPE_ID" "EXCH_RATE"."THIRD_ID" "EXCH_RATE"."MARKET_THIRD_ID"))
  144. PUSH_SUBQ(@"SEL$2")
  145. INDEX_RS_DESC(@"SEL$1" "EX1"@"SEL$1" ("EXCH_RATE"."CURRENCY_ID" "EXCH_RATE"."EXCH_D" "EXCH_RATE"."UNDERLY_CURRENCY_ID" "EXCH_RATE"."TYPE_ID" "EXCH_RATE"."THIRD_ID" "EXCH_RATE"."MARKET_THIRD_ID"))
  146. OUTLINE_LEAF(@"SEL$1")
  147. OUTLINE_LEAF(@"SEL$2")
  148. ALL_ROWS
  149. DB_VERSION('12.2.0.1')
  150. OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
  151. IGNORE_OPTIM_EMBEDDED_HINTS
ghhaqwfi

ghhaqwfi1#

你的子查询看起来很脆弱。如果子查询返回多行,您将得到一个错误。如果它不能返回多行,那么GROUP BY就没用了。
在ex1.exch_d上同时使用范围条件和相等条件似乎相当可疑。
索引中的前两列应该是测试简单相等的列,currency_id和underly_currency_id。让exch_d出现在underly_currency_id之前会破坏后者的可索引选择性(在子查询中)。
如果不知道Oracle使用的计划,或者至少不知道Oracle中有什么索引,那么Oracle在这里更快并不能告诉我们任何事情。

kkbh8khc

kkbh8khc2#

把它放在这里,因为它不适合评论:
1.执行计划显示错误的估计,也许可以查看表统计信息并更新它们!(查看analyze
1.子查询中分组依据的列的索引会很有帮助(在分组依据中使用与索引中相同的顺序)
1.你可以优化你的查询,以避免子查询,通过使用窗口函数如下:

  1. with cte as (
  2. select
  3. ex1.currency_id,
  4. ex1.underly_currency_id,
  5. ex1.type_id,
  6. ex1.third_id,
  7. ex1.market_third_id,
  8. ex1.exch_d,
  9. ex1.daily_dflt_f,
  10. ex1.exch_rate,
  11. ex1.external_seq_no,
  12. ex1.creation_d,
  13. ex1.creation_user_id,
  14. ex1.last_modif_d,
  15. ex1.last_user_id,
  16. row_number() over (partition by type_id, third_id, market_third_id order by exch_d desc) rn
  17. FROM aaamaindb.exch_rate ex1
  18. where ex1.currency_id = 1
  19. and ex1.underly_currency_id = 2
  20. and ex1.exch_d >= '2003/03/27 00:00:00'
  21. and ex1.exch_d <= '2006/01/15 00:00:00'
  22. )
  23. select * from cte
  24. where rn = 1
  25. order by exch_d desc

如果你最终使用了这个查询,你仍然想在这个查询中调整分区

展开查看全部
nr9pn0ug

nr9pn0ug3#

你的PostgreSQL查询有一个明显的问题。首先执行Bitmap Index Scan on exch_rate_bk_idx
Index Cond: ((currency_id = 1) AND (exch_d >= '2003-03-27 00:00:00'::timestamp without time zone) AND (exch_d <= '2006-01-15 00:00:00'::timestamp without time zone) AND (underly_currency_id = 2))"
它实际生成1026行。所有这些行都使用SubPlan 1进行过滤,除了具有最高exch_d的行。SubPlan 1的 * 每次调用 * 再次扫描所有1026行.
对于具有不同exch_d的大量“版本”行,此查询不缩放
Oracle可以生成不同的执行计划,如下所示(在一个稍微简化的查询上)

  1. select
  2. * from tab t
  3. where t.currency_id = 1 and t.underly_currency_id = 2
  4. and t.exch_d =
  5. (select max(exch_d) from tab t2
  6. where t.currency_id = t2.currency_id and
  7. t.underly_currency_id = t2.underly_currency_id)
  8. ------------------------------------------------------------------------------------------------------------------
  9. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
  10. ------------------------------------------------------------------------------------------------------------------
  11. | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.09 | 355 | 350 |
  12. | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB | 1 | 1 | 1 |00:00:00.09 | 355 | 350 |
  13. |* 2 | INDEX RANGE SCAN | TAB_IDX | 1 | 1 | 1 |00:00:00.09 | 354 | 349 |
  14. | 3 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | 0 |
  15. | 4 | FIRST ROW | | 1 | 1 | 1 |00:00:00.01 | 3 | 0 |
  16. |* 5 | INDEX RANGE SCAN (MIN/MAX) | TAB_IDX | 1 | 1 | 1 |00:00:00.01 | 3 | 0 |
  17. ------------------------------------------------------------------------------------------------------------------
  18. Predicate Information (identified by operation id):
  19. ---------------------------------------------------
  20. 2 - access("T"."CURRENCY_ID"=1 AND "T"."UNDERLY_CURRENCY_ID"=2)
  21. filter("T"."EXCH_D"=)
  22. 5 - access("T2"."CURRENCY_ID"=:B1 AND "T2"."UNDERLY_CURRENCY_ID"=:B2)

关键是访问索引以获取exch_dmax值的操作INDEX RANGE SCAN (MIN/MAX)(第5行)。此值在第二次访问(第2行)中用作过滤器。所以基本上,你只需要访问索引两次,而不是像PostgreSQL那样访问1 + N次。
首先要做的是删除子查询中的多余的GROUP BY。设计良好的相关子查询必须只生成一行,因此不需要分组。
其次,对索引进行排序,使 *versioning属性 *(exch_d)位于最后一个位置,并使用DESC NULLS LAST定义它
基本上,您希望在子查询的执行计划中看到

  1. -> Index Only Scan using index ...

预浸

  1. -> Limit ... rows = 1 ...

您将停留在1 + N索引访问上(N是具有不同exch_d的行数),但由于limit 1N将非常有效
我不知道在PostgreSQL中是否可能有类似于Oracle INDEX RANGE SCAN (MIN/MAX)的执行计划

展开查看全部

相关问题