To fetch multiple records using index for "OR" condition, Oracle scans the index once and performs filtering, and PostgreSQL scans the index multiple times and builds bitmap for each of the scan, and combines the bitmaps for final output.
-- Oracle
create table t_test (id integer generated as identity, name varchar2(20));
insert into t_test (name) select 'hans' from dual connect by level <= 2000000;
insert into t_test (name) select 'paul' from dual connect by level <= 2000000;
COMMIT;
CREATE INDEX idx_id ON t_test (id);
SQL> select segment_name,bytes from user_segments where segment_name in ('IDX_ID','T_TEST');
SEGMENT_NAME BYTES
_______________ ___________
IDX_ID 83886080
T_TEST 75497472
SQL> set autotrace trace explain
Autotrace TraceOnly
Exhibits the performance statistics with silent query output
SQL> SELECT * FROM t_test WHERE id = 30 OR id = 50;
2 rows selected.
PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID 54mc6yv5t3tpm, child number 0
-------------------------------------
SELECT * FROM t_test WHERE id = 30 OR id = 50
Plan hash value: 4102954599
----------------------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | INLIST ITERATOR | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T_TEST | 100 |
|* 3 | INDEX RANGE SCAN | IDX_ID | 19952 |
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("ID"=30 OR "ID"=50))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Statistics
-----------------------------------------------------------
4 CCursor + sql area evicted
1 CPU used when call started
1 DB time
42 Requests to/from client
42 SQL*Net roundtrips to/from client
6 buffer is not pinned count
2 buffer is pinned count
600 bytes received via SQL*Net from client
79642 bytes sent via SQL*Net to client
8 calls to get snapshot scn: kcmgss
21 calls to kcmgcs
103 consistent gets
3 consistent gets examination
3 consistent gets examination (fastpath)
103 consistent gets from cache
100 consistent gets pin
100 consistent gets pin (fastpath)
1 cursor authentications
2 enqueue releases
2 enqueue requests
3 execute count
2 index range scans
843776 logical read bytes from cache
81 no work - consistent read gets
54 non-idle wait count
3 opened cursors cumulative
1 opened cursors current
2 parse count (hard)
3 parse count (total)
11 process last non-idle time
5 recursive calls
103 session logical reads
1 sorts (memory)
2010 sorts (rows)
5 sql area evicted
2 table fetch by rowid
77 table scan blocks gotten
34620 table scan disk non-IMC rows gotten
34620 table scan rows gotten
1 table scans (short tables)
43 user calls
-- PostgreSQL
CREATE TABLE t_test (id serial, name text);
INSERT INTO t_test (name) SELECT 'hans' FROM generate_series(1, 2000000);
INSERT INTO t_test (name) SELECT 'paul' FROM generate_series(1, 2000000);
CREATE INDEX idx_id ON t_test (id);
mytest=> \dt+ t_test
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+--------+-------+----------+-------------+--------+-------------
public | t_test | table | postgres | permanent | 169 MB |
(1 row)
mytest=> \di+ idx_id
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Description
--------+--------+-------+----------+--------+-------------+-------+-------------
public | idx_id | index | postgres | t_test | permanent | 86 MB |
(1 row)
mytest=> SELECT pg_size_pretty(pg_relation_size('T_TEST')) AS "T_TEST",
pg_size_pretty(pg_relation_size('IDX_ID')) AS "IDX_ID";
T_TEST | IDX_ID
--------+--------
169 MB | 86 MB
(1 row)
mytest=> explain analyze SELECT * FROM t_test WHERE id = 30 OR id = 50;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t_test (cost=8.88..16.85 rows=2 width=9) (actual time=0.041..0.043 rows=2 loops=1)
Recheck Cond: ((id = 30) OR (id = 50))
Heap Blocks: exact=1
-> BitmapOr (cost=8.88..8.88 rows=2 width=0) (actual time=0.037..0.037 rows=0 loops=1)
-> Bitmap Index Scan on idx_id (cost=0.00..4.44 rows=1 width=0) (actual time=0.034..0.034 rows=1 loops=1)
Index Cond: (id = 30)
-> Bitmap Index Scan on idx_id (cost=0.00..4.44 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
Index Cond: (id = 50)
Planning Time: 0.056 ms
Execution Time: 0.063 ms
(10 rows)
No comments:
Post a Comment