Hello all,
One query about PostgreSQL's index usage. If I select just one column on
which there is an index (or select only columns on which there is an
index), and the index is used by PostgreSQL, does PostgreSQL avoid table
access if possible? I am trying to understand the differences between
Oracle's data access patterns and PostgreSQL's.
Here is how it works in Oracle.
Case 1 - SELECT column which is not there in the index
SQL> select name from myt where id = 13890;
NAME
---------------------------------------------------------------------------------------------------
AAAA
Execution Plan
----------------------------------------------------------
Plan hash value: 2609414407
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MYT | 1 | 65 | 2 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | MYIDX | 1 | | 1 (0)|
00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=13890)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
409 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Case 1 - SELECT column which is there in the index
SQL> select id from myt where id = 13890;
ID
----------
13890
Execution Plan
----------------------------------------------------------
Plan hash value: 2555454399
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| MYIDX | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=13890)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
In the second query where id was selected, the table was not used at all.
In PosgreSQL, explain gives me similar output in both cases.
Table structure -
postgres=# \d myt
Table "public.myt"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
name | character varying(20) |
Indexes:
"myidx" btree (id)
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
