From: | "Igor Neyman" <ineyman(at)perceptron(dot)com> |
---|---|
To: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>, "Mladen Gogala" <mladen(dot)gogala(at)vmsinfo(dot)com> |
Cc: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com>, "Kenneth Marshall" <ktm(at)rice(dot)edu>, <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Postgres 9.0 has a bias against indexes |
Date: | 2011-01-27 21:12:53 |
Message-ID: | F4C27E77F7A33E4CA98C19A9DC6722A2070C25A3@EXCHANGE.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> -----Original Message-----
> From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
> Sent: Thursday, January 27, 2011 3:59 PM
> To: Mladen Gogala
> Cc: Igor Neyman; Tom Lane; David Wilson; Kenneth Marshall;
> pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Postgres 9.0 has a bias against indexes
>
> On Thu, Jan 27, 2011 at 1:44 PM, Mladen Gogala
> <mladen(dot)gogala(at)vmsinfo(dot)com> wrote:
> > On 1/27/2011 3:37 PM, Scott Marlowe wrote:
> >>
> >> On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
> >> <mladen(dot)gogala(at)vmsinfo(dot)com> wrote:
> >>>
> >>> There is INDEX UNIQUE SCAN PK_EMP. Oracle will use an index.
> >>
> >> That's because Oracle has covering indexes.
> >>
> > I am not sure what you mean by "covering indexes" but I
> hope that for
> > the larger table I have in mind, indexes will be used.
> For a small
> > table like
>
> In Oracle you can hit JUST the index to get the data you need
> (and maybe rollback logs, which are generally pretty small)
>
> In Pgsql, once you hit the index you must then hit the actual
> data store to get the right version of your tuple. So, index
> access in pg is more expensive than in Oracle. However,
> updates are cheaper.
> Always a trade off
>
>
Scott,
What you describe here isn't about "covering indexes" - it's about different ways implementing MVCC in Oracle and PG.
Mladen,
you were right.
For recursive query like yours Oracle uses index even on small table.
I made an assumption without testing it.
However some other (non-recursive) queries against the same small table that also require reading all 14 rows do "table scan".
Regards,
Igor Neyman
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2011-01-27 21:16:29 | Re: Postgres 9.0 has a bias against indexes |
Previous Message | Scott Marlowe | 2011-01-27 20:59:15 | Re: Postgres 9.0 has a bias against indexes |