In this blog post let's compare Oracle and Postgres optimizer transformation features.
For this test case I created two small tables and see how Oracle and Postgres is able to prune partitions at run time.
Postgres Version: 17.5
nodpr=# CREATE TABLE T1 ( event_name VARCHAR , partition_id INTEGER);
CREATE TABLE
nodpr=# CREATE TABLE t2 (
nodpr(# partition_id INTEGER,
nodpr(# partition_name VARCHAR(254)
nodpr(# )
nodpr-# PARTITION BY HASH (partition_id);
CREATE TABLE
nodpr=# CREATE TABLE t2_p1 PARTITION OF t2 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE
nodpr=# CREATE TABLE t2_p2 PARTITION OF t2 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE
nodpr=# CREATE TABLE t2_p3 PARTITION OF t2 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE
nodpr=#
nodpr=# CREATE TABLE t2_p4 PARTITION OF t2 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
CREATE TABLET
-- Insert some rows
nodpr=# insert into T1 values('str1',4);
INSERT 0 1
nodpr=#
nodpr=# insert into T2 values(4,'ABC');
INSERT 0 1
nodpr=# insert into T2 values(5,'ABC');
INSERT 0 1
nodpr=# insert into T2 values(6,'ABC');
INSERT 0 1
nodpr=# insert into T2 values(7,'ABC');
INSERT 0 1
nodpr=# explain analyze
nodpr-# SELECT *
nodpr-# FROM t1
nodpr-# JOIN t2 ON t1.partition_id = t2.partition_id
nodpr-# WHERE t1.event_name LIKE 'str%';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Hash Join (cost=25.95..76.62 rows=17 width=556) (actual time=0.158..0.164 rows=1 loops=1)
Hash Cond: (t2.partition_id = t1.partition_id)
-> Append (cost=0.00..48.40 rows=560 width=520) (actual time=0.037..0.054 rows=4 loops=1)
-> Seq Scan on t2_p1 t2_1 (cost=0.00..11.40 rows=140 width=520) (actual time=0.020..0.020 rows=0 loops=1)
-> Seq Scan on t2_p2 t2_2 (cost=0.00..11.40 rows=140 width=520) (actual time=0.015..0.015 rows=1 loops=1)
-> Seq Scan on t2_p3 t2_3 (cost=0.00..11.40 rows=140 width=520) (actual time=0.006..0.006 rows=0 loops=1)
-> Seq Scan on t2_p4 t2_4 (cost=0.00..11.40 rows=140 width=520) (actual time=0.007..0.008 rows=3 loops=1)
-> Hash (cost=25.88..25.88 rows=6 width=36) (actual time=0.091..0.091 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on t1 (cost=0.00..25.88 rows=6 width=36) (actual time=0.051..0.053 rows=1 loops=1)
Filter: ((event_name)::text ~~ 'str%'::text)
Planning Time: 0.295 ms
Execution Time: 0.218 ms
(13 rows)
Value of 4 in T2 resides in t2_p4. But this SELECT query scans through all the partitions of T2. As you can see, Postgres could not do a partition pruning in this case.
Now lets do this same test case on Oracle 19.22 and see the execution plan
EXPLAIN PLAN FOR
SELECT /*+ USE_HASH(T2) */ * FROM T1,T2 where
T1.event_name like 'str%'
and T1.partition_id=T2.partition_id
Plan hash value: 2384829105
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 976K| 11158 (1)| 00:03:33 | | |
|* 1 | HASH JOIN | | 5000 | 976K| 11158 (1)| 00:03:33 | | |
| 2 | JOIN FILTER CREATE | :BF0001 | 1 | 100 | 4 (0)| 00:00:01 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 1 | 100 | 4 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS STORAGE FULL| T1 | 1 | 100 | 4 (0)| 00:00:01 | | |
| 5 | JOIN FILTER USE | :BF0001 | 100K| 9765K| 11154 (1)| 00:03:32 | | |
| 6 | PARTITION HASH JOIN-FILTER| | 100K| 9765K| 11154 (1)| 00:03:32 |:BF0000|:BF0000|
|* 7 | TABLE ACCESS STORAGE FULL| T2 | 100K| 9765K| 11154 (1)| 00:03:32 |:BF0000|:BF0000|
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."PARTITION_ID"="T2"."PARTITION_ID")
4 - storage("T1"."EVENT_NAME" LIKE 'str%')
filter("T1"."EVENT_NAME" LIKE 'str%')
7 - storage(SYS_OP_BLOOM_FILTER(:BF0001,"T2"."PARTITION_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0001,"T2"."PARTITION_ID"))
You can see SYS_OP_BLOOM_FILTER being applied on the predicate.
Here a bloom filter is created at O/P 3. And at O/P 7, Pstart and Pstop shows :BF0000.
Based on this bloom filter partition pruning occurs on T2 and therefore, only the partitions containing the relevant data are scanned. This is called Bloom Pruning. And if you have large datasets with thousands of partitions, this feature could massively improve the performance of the complex analytical SQLs.
