From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Igor Neyman <ineyman(at)perceptron(dot)com> |
Cc: | Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>, 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:25:04 |
Message-ID: | AANLkTikzL01dD=ZxSF_1spJNKxarce0Q9_xFxopa3L67@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Thu, Jan 27, 2011 at 2:18 PM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:
>
>> -----Original Message-----
>> From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
>> Sent: Thursday, January 27, 2011 4:16 PM
>> To: Igor Neyman
>> Cc: Mladen Gogala; 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 2:12 PM, Igor Neyman
>> <ineyman(at)perceptron(dot)com> wrote:
>> >
>> >
>> >> -----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.
>>
>> It is about covering indexes AND it's about the difference in
>> how MVCC is implemented in both databases.
>>
>>
>
> Well, Mladen's query doesn't involve covering indexes.
On Oracle? Then how can it get the values it needs without having to
hit the data store?
From | Date | Subject | |
---|---|---|---|
Next Message | Mladen Gogala | 2011-01-27 21:31:24 | Re: Postgres 9.0 has a bias against indexes |
Previous Message | Kenneth Marshall | 2011-01-27 21:20:53 | Re: Postgres 9.0 has a bias against indexes |