This article is about the access paths in PostgreSQL, and I am trying to relate them with Oracle access paths, which helps Oracle DBA’s to understand access paths in PostgreSQL and vice versa. To be clear, I am not comparing the algorithms behind these access paths in the article.
Sequential Scan in PostgreSQL / Table Access Full in Oracle
When all the rows or maximum rows are accessed in a table optimizer choses Sequential scan in PostgreSQL, similarly in Oracle the name of this access is called “full table scan” or “table access full”
PostgreSQL execution plan
DEVL=# explain analyze select * from sample;
QUERY PLAN
—————————————————————————————————————–
Seq Scan on sample (cost=0.00..3481.36 rows=127936 width=428) (actual time=0.010..10.449 rows=127936 loops=1)
Planning Time: 0.375 ms
Execution Time: 15.537 ms
Oracle execution plan
SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,’TYPICAL’));
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID 99kss4rwnchth, child number 1
————————————-
select * from sample
Plan hash value: 1092880916
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | | | 557 (100)| |
| 1 | TABLE ACCESS FULL| SAMPLE | 127K| 13M| 557 (1)| 00:00:01 |
Index Only Scan
From any indexed column, when we select only a few rows or less percentage of rows, for instance, less than 5%, the optimizer chooses ‘Index scan’.
In the next three examples, we are selecting only the indexed column, so the optimizer will choose ‘Index only scan’ which pulls rows or tuples directly from the index without going to the table.
Example 1
PostgreSQL
DEVL=# explain analyze select sample_surrogate_id from sample where sample_surrogate_id <19;
QUERY PLAN
———————————————————————————————————————————–
Index Only Scan using surrogate_idx on sample (cost=0.42..36.47 rows=1146 width=4) (actual time=0.012..0.128 rows=1152 loops=1)
Index Cond: (sample_surrogate_id < 19)
Heap Fetches: 0
Planning Time: 0.127 ms
Execution Time: 0.189 ms
Oracle
SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,’TYPICAL’));
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID 9vwjzy7aaxqt6, child number 0
————————————-
select sample_surrogate_id from sample where sample_surrogate_id <19
Plan hash value: 2013376945
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
PLAN_TABLE_OUTPUT
——————————————————————————–
——————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)|
|* 1 | INDEX RANGE SCAN| SURROGATE_IDX | 64 | 320 | 2 (0)| 00:00:01
|
——————————————————————————–
PLAN_TABLE_OUTPUT
——————————————————————————–
—
Predicate Information (identified by operation id):
—————————————————
1 – access(“SAMPLE_SURROGATE_ID”<19)
Example 2
PostgreSQL
DEVL=# explain analyze select sample_surrogate_id from sample order by sample_surrogate_id;
QUERY PLAN
——————————————————————————————————————————————
Index Only Scan using surrogate_idx on sample (cost=0.42..3651.46 rows=127936 width=4) (actual time=0.079..14.143 rows=127936 loops=1)
Heap Fetches: 0
Planning Time: 0.103 ms
Execution Time: 19.231 ms
Oracle
SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,’TYPICAL’));
PLAN_TABLE_OUTPUT——————————————————————————–
SQL_ID 9sn2jw1zvhnmq, child number 0
————————————-
select sample_surrogate_id from sample order by sample_surrogate_id
Plan hash value: 3286072040
——————————————————————————–
—
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
PLAN_TABLE_OUTPUT
——————————————————————————–
——————————————————————————–
—
| 0 | SELECT STATEMENT | | | | 279 (100)|
|
| 1 | INDEX FULL SCAN | SURROGATE_IDX | 127K| 624K| 279 (1)| 00:00:01
|
——————————————————————————–
PLAN_TABLE_OUTPUT
——————————————————————————–
—
Example 3
PostgreSQL
DEVL=# explain analyze select sample_surrogate_id from sample where sample_surrogate_id=1;
QUERY PLAN
——————————————————————————————————————————
Index Only Scan using surrogate_idx on sample (cost=0.42..5.54 rows=64 width=4) (actual time=0.018..0.026 rows=64 loops=1)
Index Cond: (sample_surrogate_id = 1)
Heap Fetches: 0
Planning Time: 0.102 ms
Execution Time: 0.041 ms
(5 rows)
Oracle
SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,’TYPICAL’));
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID a9j5jtsh0ssck, child number 0
————————————-
select sample_surrogate_id from sample where sample_surrogate_id=1
Plan hash value: 2013376945
——————————————————————————–
—
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
PLAN_TABLE_OUTPUT
——————————————————————————–
——————————————————————————–
—
| 0 | SELECT STATEMENT | | | | 1 (100)|
|
|* 1 | INDEX RANGE SCAN| SURROGATE_IDX | 64 | 320 | 1 (0)| 00:00:01
|
——————————————————————————–
PLAN_TABLE_OUTPUT
——————————————————————————–
–
Predicate Information (identified by operation id):
—————————————————
1 – access(“SAMPLE_SURROGATE_ID”=1)
Bitmap Heap Scan/Table Access by Index Rowid(2 Step plans)
In this example, we are selecting all the columns not just indexed columns, so there will be two steps in the following plan, the first step will visit the index to find the locations of rows matching the index condition , and then the upper plan actually fetches those rows from the table.
The “bitmap” mentioned in the plan is the mechanism that does the sorting. Actually, PostgreSQL doesn’t support creating bitmap indexes manually like Oracle.
DEVL=# explain analyze select * from sample where sample_surrogate_id=1;
QUERY PLAN
————————————————————————————————————————
Bitmap Heap Scan on sample (cost=4.91..228.98 rows=64 width=428) (actual time=0.029..0.099 rows=64 loops=1)
Recheck Cond: (sample_surrogate_id = 1)
Heap Blocks: exact=64
-> Bitmap Index Scan on surrogate_idx (cost=0.00..4.90 rows=64 width=0) (actual time=0.018..0.018 rows=64 loops=1)
Index Cond: (sample_surrogate_id = 1)
Planning Time: 0.145 ms
Execution Time: 0.127 ms
SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,’TYPICAL’));
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID 19rfbsk5hh17r, child number 0
————————————-
select * from sample where sample_surrogate_id=1
Plan hash value: 210076746
——————————————————————————–
———————
| Id | Operation | Name | Rows | Bytes | Co
st (%CPU)| Time |
PLAN_TABLE_OUTPUT
——————————————————————————–
——————————————————————————–
———————
| 0 | SELECT STATEMENT | | | |
65 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SAMPLE | 64 | 6976 |
65 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SURROGATE_IDX | 64 | |
PLAN_TABLE_OUTPUT
——————————————————————————–
1 (0)| 00:00:01 |
——————————————————————————–
———————
Predicate Information (identified by operation id):
—————————————————
2 – access(“SAMPLE_SURROGATE_ID”=1)