Conversation

Franck Pachot

@FranckPachot

Jul 11

I 💗

@PostgreSQL

(and compatible ones - this is

@Yugabyte

doing the same thanks to reusing the PG code). A select min() and max() reads both ends of the index. IIIRW Oracle doesn't have this transformation.

create table t (a int);
create index i on t(a asc);
explain select min(a),max(a) from t;

QUERY PLAN
---------------------------------------------------------
Result
InitPlan 1 (returns $0)
-> Limit
-> Index Only Scan using i on t
Index Cond: (a IS NOT NULL)
InitPlan 2 (returns $1)
-> Limit
-> Index Only Scan Backward using i on t t_1
Index Cond: (a IS NOT NULL)
(9 rows)

read image description

ALT

Replying to

and

But does this really qualify as a transformation? IOW, why should the implementation need to treat the case where min() and max() appear together as a special case? I wonder if the Oracle limitation actually has something to do with its general approach to concurrency control.

6:07 PM · Jul 11, 2022Twitter Web App

Replying to

and

I see it as a transformation because one query block becomes two. Oracle could do that (and do much more complex things)