Oracle Exadata vs PostgreSQL “Storage Index”

Is it possible that a tiny PostgreSQL beats Oracle Exadata on realm of Storage Index?
Yes. Exadata took 6 seconds executing a query over a billion rows, where PostgreSQL took less than 2 seconds doing the same query.

This it means that PostgreSQL is better than Exadata?
NO absolutely not, it just means that every tool has it place to shine.
And actually the reasons why this happen was because two factors:
I compared the first execution on both DB, and Exadata Storage Index is not persistent.
And I was aiming to filtering 1440 rows, So PostgreSQL wouldn’t have network issue with it. For sure above 2 millions of filtering Exadata would win, even on first executions.

Let’s clarify that Storage Index just really exists on Oracle, because Exadata have a intermediate tier (cell servers) that avoid sending trash rows to the DBMS layer. What PostgreSQL implement is a Block Range INdex BRIN, that it’s persistent and operate in Min/Max Block like Exadata, and is so small.

PostgreSQL targets the big tables since it BRIN is very very lightweight and so small that you don’t notice that it even exists (As long it is used properly).
On the other hand Exadata implementation is all about reduce the overload on the DB Nodes, having the performance improvement as side effect.

PostgreSQL is about planned things, where you must create in advance the BRINs, where Exadata is all about the unexpectable.

Below are the script for Exadata:
CREATE TABLE BILLION_BY_MINUTE PARALLEL NOLOGGING AS
WITH W0 AS (SELECT /+MATERIALIZE/1 FROM DUAL CASCADE CONNECT BY LEVEL<31623) /*This limiting was due to lack of memory on my autonomous database, so later I autojoin it to produce the billion rows*/
select sysdate-rownum/24/60 EG_DATE,
trunc(dbms_random.value(1,100000)) EG_VALUE
from (SELECT 1 FROM W0,W0 T2 WHERE ROWNUM<=1000000000);

select sum(eg_value) from BILLION_BY_MINUTE where eg_date>sysdate – 1

Execution time: 6.975 seconds — First execution
Execution time: 0.125 seconds — Following executions

PostgreSQL script:

CREATE TABLE BILLION_BY_MINUTE AS
with w0 as (
select * from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))
as q (c1))
,w1 as (select 1 from w0,w0 as t2,w0 as t3)
,w2 as (select 1 from w1,w1 as t2,W1 AS T3)
,w3 as (select tempo – linha interval ‘1 MINUTE’ as EG_DATE,trunc(random()*1000000) EG_value
from (select localtimestamp as tempo,row_number() over() as linha
from w2) tb1)
SELECT * FROM W3

create index BILLION_BY_MINUTE_brix on BILLION_BY_MINUTE using brin(eg_date);

select sum(eg_value) from BILLION_BY_MINUTE where eg_date>now() – interval ‘1 day’;

Advertisement