Re: Query about index usage

Jayadevan,

PostgreSQL must go to the table to determine if the row you are requesting is visible to your transaction. This is an artifact of the MVCC implementation. Oracle can fetch the data from the index, since it doesn't keep multiple representations of the rows, but it may need to check the undo logs to determine the state that applies to your transaction. Its just two different ways to accomplish the same thing.

Bob Lunney

--- On Fri, 6/11/10, Jayadevan M <Jayadevan(dot)Maymala(at)ibsplc(dot)com> wrote:

> From: Jayadevan M <Jayadevan(dot)Maymala(at)ibsplc(dot)com>
> Subject: [PERFORM] Query about index usage
> To: pgsql-performance(at)postgresql(dot)org
> Date: Friday, June 11, 2010, 5:56 AM
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>