I am executing the below query in PostgreSQL but it is taking more time than oracle. In PostgreSQL it is taking 1.5 second and in oracle it is taking nearly 0.009 second.
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 having 45000 record both in postgres and oracle.
Below in the index created on exch_rate table.
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;
I need to optimize, this sql query to work as fast as oracle.
QUERY PLAN
"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"
And DDL for 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
2
Answers
Your subquery seems pretty fragile. You will get an error if your subquery returns more than one row. And if it can’t return more than one row, than your GROUP BY is useless.
The use of a both a range condition and an equality condition on ex1.exch_d seems rather suspect.
The first two columns in your index should be the ones tested for simple equality, currency_id and underly_currency_id. Having exch_d come before underly_currency_id will ruin must of the latter’s indexable selectivity (in the subquery).
That Oracle is faster here doesn’t tell us anything without knowing the plan used by Oracle, or at least what indexes you have in Oracle.
put it here since it won’t fit in the comments :
analyze)you still want to adjust the partition by in this query if you ended up using this query