Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 56ABF632731 for ; Fri, 11 Jun 2010 15:50:21 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 84996-10 for ; Fri, 11 Jun 2010 18:50:12 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from web39706.mail.mud.yahoo.com (web39706.mail.mud.yahoo.com [209.191.106.52]) by mail.postgresql.org (Postfix) with SMTP id 092866326E4 for ; Fri, 11 Jun 2010 15:50:12 -0300 (ADT) Received: (qmail 82560 invoked by uid 60001); 11 Jun 2010 18:50:10 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1276282210; bh=ima8tuvEhsk78E/MPG7zvz9yf/OlcD6AzS5rHIiQoqw=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=GRlwMYvL1UkAZCRctw2pUaaG+MJ9dFRvad9OyKY40bz1w9rdB46GhrehQK8jwFOWwfhycVQIgaSCRpn6XDx1eM3A5W3qldcBkZtBFaJr9Tz9mMOiyKC6meZg1vkT7LTwVjzHDkFAbk+WYAFdKg9N2iah0iwW1OxNsNUcMFVm/rY= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=P0trFWlIMYBf81z1cYoMSQsI11WvZF6KO3w3unPauKKsQBTJbXhVfCo0oaih8TfsiQGBTWaJZLZFBmN32YWZ86ZTKP88gzAyKds+WPlkd+Rx0Ug0CKUNWbWye49S2umyfiiJQbhRbN774zjbqqCqNG2hq3Q4vd/xU2irxpYTTUc=; Message-ID: <605907.80868.qm@web39706.mail.mud.yahoo.com> X-YMail-OSG: EK.3SsUVM1mYLCHdYsY11uKxpZyHaa2jAxPL5_qtUZ7obgs vyplxjtii8BtybrTME.0GPnmMTEFzEJ1BLqZCMOgV6c9Q4TeA9TmhJpPrR93 hy.F8M5RH_zSL5wJ8NkFVIvifkkKVwNcMaUfDH6b1DfIoip.kxzkj7J_.ub. ix9sP7HpIePlRg2O9A0xMUXA4ajXaQmv0jVkJ9SeLBDozlT2K1BNrDlE3Pt8 89mSYbsv2TfjExRfVjDq4ZmVXVVD3LYt1zSa5m1GIXJ1A68ylLrUmiuPJ6Zk HoDfQFdALn6TOqu8y4.a10BSPpyDJNu209sncNMvpb9nTwdmguilqd6rZ.mo P Received: from [64.126.29.122] by web39706.mail.mud.yahoo.com via HTTP; Fri, 11 Jun 2010 11:50:10 PDT X-Mailer: YahooMailClassic/11.1.3 YahooMailWebService/0.8.103.269680 Date: Fri, 11 Jun 2010 11:50:10 -0700 (PDT) From: Bob Lunney Subject: Re: Query about index usage To: pgsql-performance@postgresql.org, Jayadevan M In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.79 tagged_above=-10 required=5 tests=BAYES_50=0.8, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201006/216 X-Sequence-Number: 39072 Jayadevan,=0A=0APostgreSQL 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 d= oesn'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.=0A=0ABob Lunney=0A= =0A--- On Fri, 6/11/10, Jayadevan M wrote:= =0A=0A> From: Jayadevan M =0A> Subject: [PERF= ORM] Query about index usage=0A> To: pgsql-performance@postgresql.org=0A> D= ate: Friday, June 11, 2010, 5:56 AM=0A> Hello all,=0A> =0A> One query about= PostgreSQL's index usage. If I select just=0A> one column on =0A> which th= ere is an index (or select only columns on which=0A> there is an =0A> index= ), and the index is used by PostgreSQL, does=0A> PostgreSQL avoid table =0A= > access if possible?=A0 I am trying to understand the=0A> differences betw= een =0A> Oracle's data access patterns and PostgreSQL's. =0A> Here is how i= t works in Oracle.=0A> =0A> Case 1 - SELECT column which is not there in th= e index =0A> =0A> SQL> select name from myt where id =3D 13890;=0A> =0A> NA= ME=0A> --------------------------------------------------------------------= -------------------------------=0A> AAAA=0A> =0A> =0A> Execution Plan=0A> -= ---------------------------------------------------------=0A> Plan hash val= ue: 2609414407=0A> =0A> ---------------------------------------------------= ----------------------------------=0A> | Id=A0 | Operation=A0 =A0 =A0 =A0 = =A0=0A> =A0 =A0 =A0 =A0=A0=A0| Name=A0 |=0A> Rows=A0 | Bytes | Cost (%CPU)|= =0A> Time=A0 =A0 |=0A> ---------------------------------------------------= ----------------------------------=0A> |=A0=A0=A00 | SELECT STATEMENT=A0 = =A0 =A0=0A> =A0 =A0 =A0 |=A0 =A0=0A> =A0=A0=A0|=A0 =A0=A0=A01 |=A0 =A0=0A> = 65 |=A0 =A0=A0=A02=A0=A0=A0(0)| =0A> 00:00:01 |=0A> |=A0=A0=A01 |=A0 TABLE = ACCESS BY INDEX ROWID|=0A> MYT=A0=A0=A0|=A0 =A0=A0=A01 |=A0=0A> =A0 65 |=A0= =A0=A0=A02=A0=A0=A0(0)|=0A> =0A> 00:00:01 |=0A> |*=A0 2 |=A0=A0=A0INDEX RA= NGE SCAN=A0 =A0=0A> =A0 =A0 =A0 | MYIDX |=A0 =A0=A0=A01=0A> |=A0 =A0 =A0=A0= =A0|=A0=0A> =A0=A0=A01=A0=A0=A0(0)| =0A> 00:00:01 |=0A> -------------------= ------------------------------------------------------------------=0A> =0A>= Predicate Information (identified by operation id):=0A> ------------------= ---------------------------------=0A> =0A> =A0=A0=A02 - access("ID"=3D13890= )=0A> =0A> Note=0A> -----=0A> =A0=A0=A0- dynamic sampling used for this=0A>= statement=0A> =0A> =0A> Statistics=0A> -----------------------------------= -----------------------=0A> =A0 =A0 =A0 =A0 =A0 0=A0 recursive calls=0A> = =A0 =A0 =A0 =A0 =A0 0=A0 db block gets=0A> =A0 =A0 =A0 =A0 =A0 4=A0 consist= ent gets=0A> =A0 =A0 =A0 =A0 =A0 0=A0 physical reads=0A> =A0 =A0 =A0 =A0 = =A0 0=A0 redo size=0A> =A0 =A0 =A0 =A0 409=A0 bytes sent via=0A> SQL*Net to= client=0A> =A0 =A0 =A0 =A0 384=A0 bytes received via=0A> SQL*Net from clie= nt=0A> =A0 =A0 =A0 =A0 =A0 2=A0 SQL*Net=0A> roundtrips to/from client=0A> = =A0 =A0 =A0 =A0 =A0 0=A0 sorts (memory)=0A> =A0 =A0 =A0 =A0 =A0 0=A0 sorts = (disk)=0A> =A0 =A0 =A0 =A0 =A0 1=A0 rows processed=0A> =0A> =0A> =0A> Cas= e 1 - SELECT column which is there in the index =0A> =0A> SQL> select id fr= om myt where id =3D 13890;=0A> =0A> =A0 =A0 =A0 =A0 ID=0A> ----------=0A> = =A0 =A0=A0=A013890=0A> =0A> =0A> Execution Plan=0A> -----------------------= -----------------------------------=0A> Plan hash value: 2555454399=0A> =0A= > -------------------------------------------------------------------------= -=0A> | Id=A0 | Operation=A0 =A0 =A0 =A0 |=0A> Name=A0 | Rows=A0 | Bytes | = Cost (%CPU)| Time=A0=0A> =A0=A0=A0|=0A> -----------------------------------= ---------------------------------------=0A> |=A0=A0=A00 | SELECT STATEMENT = |=A0 =A0=0A> =A0=A0=A0|=A0 =A0=A0=A01 |=A0 =A0=0A> 13 |=A0 =A0=A0=A01=A0=A0= =A0(0)|=0A> 00:00:01 |=0A> |*=A0 1 |=A0 INDEX RANGE SCAN| MYIDX |=A0=0A> = =A0=A0=A01 |=A0 =A0 13 |=A0=0A> =A0=A0=A01=A0=A0=A0(0)| 00:00:01 |=0A> ----= ----------------------------------------------------------------------=0A> = =0A> Predicate Information (identified by operation id):=0A> --------------= -------------------------------------=0A> =0A> =A0=A0=A01 - access("ID"=3D1= 3890)=0A> =0A> Note=0A> -----=0A> =A0=A0=A0- dynamic sampling used for this= =0A> statement=0A> =0A> =0A> Statistics=0A> -------------------------------= ---------------------------=0A> =A0 =A0 =A0 =A0 =A0 0=A0 recursive calls=0A= > =A0 =A0 =A0 =A0 =A0 0=A0 db block gets=0A> =A0 =A0 =A0 =A0 =A0 3=A0 consi= stent gets=0A> =A0 =A0 =A0 =A0 =A0 0=A0 physical reads=0A> =A0 =A0 =A0 =A0 = =A0 0=A0 redo size=0A> =A0 =A0 =A0 =A0 407=A0 bytes sent via=0A> SQL*Net to= client=0A> =A0 =A0 =A0 =A0 384=A0 bytes received via=0A> SQL*Net from clie= nt=0A> =A0 =A0 =A0 =A0 =A0 2=A0 SQL*Net=0A> roundtrips to/from client=0A> = =A0 =A0 =A0 =A0 =A0 0=A0 sorts (memory)=0A> =A0 =A0 =A0 =A0 =A0 0=A0 sorts = (disk)=0A> =A0 =A0 =A0 =A0 =A0 1=A0 rows processed=0A> =0A> In the second q= uery where id was selected, the table was=0A> not used at all. =0A> In Posg= reSQL, explain gives me similar output in both=0A> cases.=0A> Table structu= re - =0A> =0A> postgres=3D# \d myt=0A> =A0 =A0 =A0 =A0 =A0 =A0=A0=A0Table= =0A> "public.myt"=0A> Column |=A0 =A0 =A0 =A0=A0=A0Type=A0=0A> =A0 =A0 =A0= =A0 | Modifiers=0A> --------+-----------------------+-----------=0A> id= =A0 =A0=A0=A0| integer=A0 =A0 =A0=0A> =A0 =A0 =A0 =A0=A0=A0|=0A> name=A0= =A0=A0| character varying(20) |=0A> Indexes:=0A> =A0 =A0 "myidx" btree (id)= =0A> =0A> =0A> Regards,=0A> Jayadevan=0A> =0A> =0A> =0A> =0A> =0A> DISCLAIM= ER: =0A> =0A> "The information in this e-mail and any attachment is=0A> int= ended only for =0A> the person to whom it is addressed and may contain=0A> = confidential and/or =0A> privileged material. If you have received this e-m= ail in=0A> error, kindly =0A> contact the sender and destroy all copies of = the original=0A> communication. =0A> IBS makes no warranty, express or impl= ied, nor guarantees=0A> the accuracy, =0A> adequacy or completeness of the = information contained in=0A> this email or any =0A> attachment and is not l= iable for any errors, defects,=0A> omissions, viruses =0A> or for resultant= loss or damage, if any, direct or=0A> indirect."=0A> =0A> =0A> =0A> =0A> = =0A> =0A> -- =0A> Sent via pgsql-performance mailing list (pgsql-performanc= e@postgresql.org)=0A> To make changes to your subscription:=0A> http://www.= postgresql.org/mailpref/pgsql-performance=0A> =0A=0A=0A