postgresql 在postgres中优化sql查询

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

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

select ex1.currency_id,
        ex1.underly_currency_id,
        ex1.type_id,
        ex1.third_id,
        ex1.market_third_id,
        ex1.exch_d,
        ex1.daily_dflt_f,
        ex1.exch_rate,
        ex1.external_seq_no,
        ex1.creation_d,
        ex1.creation_user_id,
        ex1.last_modif_d,
        ex1.last_user_id
        /* security_level_e = 0 */
        FROM aaamaindb.exch_rate ex1
        where ((ex1.currency_id = 1)
         and ( ex1.underly_currency_id = 2)
         and ( ex1.exch_d >= '2003/03/27 00:00:00')
         and ( ex1.exch_d <= '2006/01/15 00:00:00')
         and ( exch_d = (  select max(ex2.exch_d)
            from aaamaindb.exch_rate ex2
            where ex1.currency_id = ex2.currency_id
            and ex1.underly_currency_id = ex2.underly_currency_id
            and ex2.exch_d >= '2003/03/27 00:00:00' and ex2.exch_d <= '2006/01/15 00:00:00'
            and (ex1.type_id = ex2.type_id
                or (ex1.type_id is NULL and ex2.type_id is NULL)) /*PMSTA07173-EFE-081002*/
            and (ex1.third_id = ex2.third_id
                or (ex1.third_id is  NULL and ex2.third_id is NULL))
            and (ex1.market_third_id = ex2.market_third_id
                or (ex1.market_third_id is NULL and ex2.market_third_id is NULL))
            group by type_id, third_id, market_third_id )))
        order by exch_d desc;

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

CREATE UNIQUE INDEX IF NOT EXISTS exch_rate_bk_idx
    ON aaamaindb.exch_rate USING btree
    (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)
    TABLESPACE pg_default;

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

"Sort  (cost=31883.98..31883.99 rows=4 width=97) (actual time=582.557..582.559 rows=1 loops=1)"
"  Sort Key: ex1.exch_d DESC"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  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)"
"        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))"
"        Filter: (exch_d = (SubPlan 1))"
"        Rows Removed by Filter: 1025"
"        Heap Blocks: exact=21"
"        ->  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)"
"              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))"
"        SubPlan 1"
"          ->  GroupAggregate  (cost=37.66..37.69 rows=1 width=32) (actual time=0.566..0.566 rows=1 loops=1026)"
"                Group Key: ex2.type_id, ex2.third_id, ex2.market_third_id"
"                ->  Sort  (cost=37.66..37.67 rows=1 width=32) (actual time=0.411..0.457 rows=1026 loops=1026)"
"                      Sort Key: ex2.type_id, ex2.third_id, ex2.market_third_id"
"                      Sort Method: quicksort  Memory: 97kB"
"                      ->  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)"
"                            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))"
"                            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))))"
"                            Heap Fetches: 0"
"Planning Time: 0.278 ms"
"Execution Time: 582.691 ms"

还有table的垫子

CREATE TABLE IF NOT EXISTS aaamaindb.exch_rate
(
    currency_id bigint NOT NULL,
    underly_currency_id bigint NOT NULL,
    type_id bigint,
    third_id bigint,
    market_third_id bigint,
    exch_d timestamp without time zone NOT NULL,
    daily_dflt_f smallint NOT NULL DEFAULT 0,
    exch_rate numeric(23,14) NOT NULL,
    external_seq_no bigint,
    creation_d timestamp without time zone,
    creation_user_id bigint,
    last_modif_d timestamp without time zone,
    last_user_id bigint,
    CONSTRAINT fk_701001 FOREIGN KEY (currency_id)
        REFERENCES aaamaindb.currency (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT fk_701002 FOREIGN KEY (underly_currency_id)
        REFERENCES aaamaindb.currency (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT fk_701003 FOREIGN KEY (type_id)
        REFERENCES aaamaindb.type (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT,
    CONSTRAINT fk_701004 FOREIGN KEY (third_id)
        REFERENCES aaamaindb.third_party (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT,
    CONSTRAINT fk_701005 FOREIGN KEY (market_third_id)
        REFERENCES aaamaindb.third_party (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT,
    CONSTRAINT exch_rate_daily_dflt_chk CHECK (daily_dflt_f = ANY (ARRAY[0, 1]))
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS aaamaindb.exch_rate
    OWNER to v_gen_reg1aaa;

GRANT ALL ON TABLE aaamaindb.exch_rate TO v_gen_reg1aaa;
-- Index: exch_rate_bk_idx

-- DROP INDEX IF EXISTS aaamaindb.exch_rate_bk_idx;

CREATE UNIQUE INDEX IF NOT EXISTS exch_rate_bk_idx
    ON aaamaindb.exch_rate USING btree
    (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)
    TABLESPACE pg_default;

-- Trigger: d_after_exch_rate_trg

-- DROP TRIGGER IF EXISTS d_after_exch_rate_trg ON aaamaindb.exch_rate;

CREATE TRIGGER d_after_exch_rate_trg
    AFTER DELETE
    ON aaamaindb.exch_rate
    FOR EACH STATEMENT
    EXECUTE FUNCTION aaamaindb.d_after_exch_rate_trg();

-- Trigger: i_after_exch_rate_trg

-- DROP TRIGGER IF EXISTS i_after_exch_rate_trg ON aaamaindb.exch_rate;

CREATE TRIGGER i_after_exch_rate_trg
    AFTER INSERT
    ON aaamaindb.exch_rate
    FOR EACH STATEMENT
    EXECUTE FUNCTION aaamaindb.i_after_exch_rate_trg();

-- Trigger: i_before_row_exch_rate_trg

-- DROP TRIGGER IF EXISTS i_before_row_exch_rate_trg ON aaamaindb.exch_rate;

CREATE TRIGGER i_before_row_exch_rate_trg
    BEFORE INSERT
    ON aaamaindb.exch_rate
    FOR EACH ROW
    EXECUTE FUNCTION aaamaindb.i_before_row_exch_rate_trg();

-- Trigger: u_after_exch_rate_trg

-- DROP TRIGGER IF EXISTS u_after_exch_rate_trg ON aaamaindb.exch_rate;

CREATE TRIGGER u_after_exch_rate_trg
    AFTER UPDATE 
    ON aaamaindb.exch_rate
    FOR EACH STATEMENT
    EXECUTE FUNCTION aaamaindb.u_after_exch_rate_trg();

-- Trigger: u_before_row_exch_rate_trg

-- DROP TRIGGER IF EXISTS u_before_row_exch_rate_trg ON aaamaindb.exch_rate;

CREATE TRIGGER u_before_row_exch_rate_trg
    BEFORE UPDATE 
    ON aaamaindb.exch_rate
    FOR EACH ROW
    EXECUTE FUNCTION aaamaindb.u_before_row_exch_rate_trg();

Below is Oracle Explain plan

 OPERATION   OBJECT_NAME     OPTIONS     CARDINALITY     COST 

 SELECT STATEMENT 
         1   92 
        
 TABLE ACCESS 
 AAAMAINDB.EXCH_RATE     BY INDEX ROWID      1   8 
                
 INDEX 
 AAAMAINDB.EXCH_RATE_BK_IDX      RANGE SCAN DESCENDING   1   7 
                        
 Access Predicates 
                                
 AND 
                                        
 EX1.CURRENCY_ID=1 
                                        
 EX1.EXCH_D>=TIMESTAMP' 2003-03-27 00:00:00' 
                                        
 EX1.UNDERLY_CURRENCY_ID=2 
                                        
 EX1.EXCH_D<=TIMESTAMP' 2006-01-15 00:00:00' 
                        
 Filter Predicates 
                                
 AND 
                                        
 EX1.UNDERLY_CURRENCY_ID=2 
                                        
 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) 
                        
 HASH 
     GROUP BY    1   4 
                                
 INDEX 
 AAAMAINDB.EXCH_RATE_BK_IDX      RANGE SCAN      1   3 
                                        
 Access Predicates 
                                                
 AND 
                                                        
 EX2.CURRENCY_ID=:B1 
                                                        
 EX2.EXCH_D>=TIMESTAMP' 2003-03-27 00:00:00' 
                                                        
 EX2.UNDERLY_CURRENCY_ID=:B2 
                                                        
 EX2.EXCH_D<=TIMESTAMP' 2006-01-15 00:00:00' 
                                        
 Filter Predicates 
                                                
 AND 
                                                        
 EX2.UNDERLY_CURRENCY_ID=:B1 
                                                        
 OR 
                                                                
 AND 
                                                                        
 :B2 IS NULL 
                                                                        
 EX2.MARKET_THIRD_ID IS NULL 
                                                                
 EX2.MARKET_THIRD_ID=:B3 
                                                        
 OR 
                                                                
 EX2.TYPE_ID=:B4 
                                                                
 AND 
                                                                        
 EX2.TYPE_ID IS NULL 
                                                                        
 :B5 IS NULL 
                                                        
 OR 
                                                                
 EX2.THIRD_ID=:B6 
                                                                
 AND 
                                                                        
 :B7 IS NULL 
                                                                        
 EX2.THIRD_ID IS NULL 
        
 Other XML 
                
 {info} 
                        
 info type="db_version" 
                                
 12.2.0.1 
                        
 info type="parse_schema" 
                        
 "SYS" 
                        
 info type="plan_hash_full" 
                                
 3996335147 
                        
 info type="plan_hash" 
                                
 3334517769 
                        
 info type="plan_hash_2" 
                                
 3996335147 
                        
 {hint} 
                                
 USE_HASH_AGGREGATION(@"SEL$2") 
                                
 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")) 
                                
 PUSH_SUBQ(@"SEL$2") 
                                
 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")) 
                                
 OUTLINE_LEAF(@"SEL$1") 
                                
 OUTLINE_LEAF(@"SEL$2") 
                                
 ALL_ROWS 
                                
 DB_VERSION('12.2.0.1') 
                                
 OPTIMIZER_FEATURES_ENABLE('12.2.0.1') 
                                
 IGNORE_OPTIM_EMBEDDED_HINTS
postgresql

3条答案

按热度按时间
ghhaqwfi

ghhaqwfi1#

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

赞(0）分享  回复(0）举报  2023-10-18
kkbh8khc

kkbh8khc2#

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

with cte as (
    select
    ex1.currency_id,
    ex1.underly_currency_id,
    ex1.type_id,
    ex1.third_id,
    ex1.market_third_id,
    ex1.exch_d,
    ex1.daily_dflt_f,
    ex1.exch_rate,
    ex1.external_seq_no,
    ex1.creation_d,
    ex1.creation_user_id,
    ex1.last_modif_d,
    ex1.last_user_id, 
    row_number() over (partition by type_id, third_id, market_third_id order by exch_d desc) rn
FROM  aaamaindb.exch_rate ex1
where   ex1.currency_id = 1
        and ex1.underly_currency_id = 2
        and ex1.exch_d >= '2003/03/27 00:00:00'
        and ex1.exch_d <= '2006/01/15 00:00:00'
) 
select * from cte
where rn = 1
order by exch_d desc

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

赞(0）分享  回复(0）举报  2023-10-18
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可以生成不同的执行计划，如下所示（在一个稍微简化的查询上）

select  
* from tab t 
where t.currency_id = 1 and t.underly_currency_id = 2
and t.exch_d = 
 (select max(exch_d) from tab t2
  where t.currency_id = t2.currency_id and 
  t.underly_currency_id = t2.underly_currency_id)

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |      1 |        |      1 |00:00:00.09 |     355 |    350 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB     |      1 |      1 |      1 |00:00:00.09 |     355 |    350 |
|*  2 |   INDEX RANGE SCAN                  | TAB_IDX |      1 |      1 |      1 |00:00:00.09 |     354 |    349 |
|   3 |    SORT AGGREGATE                   |         |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |
|   4 |     FIRST ROW                       |         |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)     | TAB_IDX |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |
------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("T"."CURRENCY_ID"=1 AND "T"."UNDERLY_CURRENCY_ID"=2)
       filter("T"."EXCH_D"=)
   5 - access("T2"."CURRENCY_ID"=:B1 AND "T2"."UNDERLY_CURRENCY_ID"=:B2)

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

->  Index Only Scan using index ...

预浸

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

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

赞(0）分享  回复(0）举报  2023-10-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com