Postgres vs. Oracle access paths IX – Tid Scan

By Franck Pachot

.
In the previous post we have seen how Postgres and Oracle finds the table row from the index entry. It uses the TID / ROWID. I’ll focus on this access path and I will have covered all Postgres access paths to table data.

Oracle ACCESS BY ROWID

I start with Oracle because we already have seen the TABLE ACCESS BY ROWID. I’ll decompose an index acces to the table. The first step is getting the ROWID from the index entry:

SQL> select /*+  */  rowid from demo1  where n=1000;
 
ROWID
------------------
AAASPkAAMAAABIaAAF

The ROWID contains the data object ID (to be able to identify the segment and then the tablespace), the relative file number within the tablespace, the block number within this file and the row number within the block. This can be stored in 10 bytes. When in an index entry, except if this is a global index on a partitioned table, we don’t need the object ID (because there’s a one-to-one relationship between the table and the index objects) and the only 6 bytes are stored in the index entry.

This is a simple index access and the output (projection) is the ROWID:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  32tsqy19ctmd4, child number 0
-------------------------------------
select /*+  */  rowid from demo1  where n=1000
----------------------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |     1 (100)|      1 |00:00:00.01 |       2 |
|*  1 |  INDEX UNIQUE SCAN| DEMO1_N |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("N"=1000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - ROWID[ROWID,10]

Now with the ROWID, I query a column from the table:

SQL> select /*+  */  a from demo1  where rowid='AAASPkAAMAAABIaAAF';
 
         A
----------
         1

And the plan is exactly the ‘TABLE ACCESS’ part we have seen in previous posts on index scans:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c46nq5t0sru8q, child number 0
-------------------------------------
select /*+  */  a from demo1  where rowid='AAASPkAAMAAABIaAAF'
Plan hash value: 3196731035
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |      1 |        |     1 (100)|      1 |00:00:00.01 |       1 |
|   1 |  TABLE ACCESS BY USER ROWID| DEMO1 |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "A"[NUMBER,22]

There’s no Predicate section visible here, but the access is done on the ROWID which contains the file number, block number, and row number. This is the fastest way to get one row: reading only one buffer.

Postgres Tid Scan

Same idea in Postgres where we can query the TID (Tumple ID):

select  ctid from demo1  where n=1000 ;
  ctid
---------
 (142,6)
(1 row)

Because my table is stored in a file (no tablespace with multiple data files here) the TID contains only the block number and the row number within the block.

explain (analyze,verbose,costs,buffers) select  ctid from demo1  where n=1000 ;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Index Scan using demo1_n on public.demo1  (cost=0.29..8.30 rows=1 width=6) (actual time=0.009..0.009 rows=1 loops=1)
   Output: ctid
   Index Cond: (demo1.n = 1000)
   Buffers: shared hit=3
 Planning time: 0.429 ms
 Execution time: 0.023 ms

We already have seen the cost of this operation: 116 startup operations, 2 index pages read at random_page_cost=4 and 1 result row at cpu_tuple_cost=0.01 (note that the query planner does not count the cpu_index_tuple_cost here).

Then here is the query using this TID:

explain (analyze,verbose,costs,buffers) select  a from demo1  where ctid='(142,6)' ;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Tid Scan on public.demo1  (cost=0.00..4.01 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)
   Output: a
   TID Cond: (demo1.ctid = '(142,6)'::tid)
   Buffers: shared hit=1
 Planning time: 0.351 ms
 Execution time: 0.017 ms

The cost estimation is very simple here: 1 seek()+read() at random_page_cost=4 and 1 result row at cpu_tuple_cost=0.01

Since the post on Index Only Scan, I’m working on a vacuumed table with no modifications. Now that I have the simplest access path, I’ll show the same after an update, in the next post.