Re: Postgres 9.0 has a bias against indexes

От: Kenneth Marshall
Тема: Re: Postgres 9.0 has a bias against indexes
Дата: ,
Msg-id: 20110127212053.GL6475@aart.is.rice.edu
(см: обсуждение, исходный текст)
Ответ на: Re: Postgres 9.0 has a bias against indexes  ("Igor Neyman")
Ответы: Re: Postgres 9.0 has a bias against indexes  (Mladen Gogala)
Список: pgsql-performance

Скрыть дерево обсуждения

Postgres 9.0 has a bias against indexes  (Mladen Gogala, )
 Re: Postgres 9.0 has a bias against indexes  (Kenneth Marshall, )
  Re: Postgres 9.0 has a bias against indexes  (Mladen Gogala, )
   Re: Postgres 9.0 has a bias against indexes  (David Wilson, )
    Re: Postgres 9.0 has a bias against indexes  (Tom Lane, )
     Re: Postgres 9.0 has a bias against indexes  (Mladen Gogala, )
      Re: Postgres 9.0 has a bias against indexes  ("Igor Neyman", )
       Re: Postgres 9.0 has a bias against indexes  (Mladen Gogala, )
        Re: Postgres 9.0 has a bias against indexes  (Scott Marlowe, )
         Re: Postgres 9.0 has a bias against indexes  (Mladen Gogala, )
          Re: Postgres 9.0 has a bias against indexes  (Scott Marlowe, )
           Re: Postgres 9.0 has a bias against indexes  ("Igor Neyman", )
            Re: Postgres 9.0 has a bias against indexes  (Scott Marlowe, )
             Re: Postgres 9.0 has a bias against indexes  ("Igor Neyman", )
              Re: Postgres 9.0 has a bias against indexes  (Scott Marlowe, )
               Re: Postgres 9.0 has a bias against indexes  ("Igor Neyman", )
                Re: Postgres 9.0 has a bias against indexes  (Scott Marlowe, )
               Re: Postgres 9.0 has a bias against indexes  (Mladen Gogala, )
            Re: Postgres 9.0 has a bias against indexes  (Kenneth Marshall, )
             Re: Postgres 9.0 has a bias against indexes  (Mladen Gogala, )
              Re: Postgres 9.0 has a bias against indexes  ("Kevin Grittner", )
 Re: Postgres 9.0 has a bias against indexes  ("Kevin Grittner", )
 Re: Postgres 9.0 has a bias against indexes  (J Sisson, )
  Re: Postgres 9.0 has a bias against indexes  (Mladen Gogala, )

On Thu, Jan 27, 2011 at 04:12:53PM -0500, Igor Neyman wrote:
>
>
> > -----Original Message-----
> > From: Scott Marlowe [mailto:]
> > Sent: Thursday, January 27, 2011 3:59 PM
> > To: Mladen Gogala
> > Cc: Igor Neyman; Tom Lane; David Wilson; Kenneth Marshall;
> > 
> > Subject: Re: [PERFORM] Postgres 9.0 has a bias against indexes
> >
> > On Thu, Jan 27, 2011 at 1:44 PM, Mladen Gogala
> > <> wrote:
> > > On 1/27/2011 3:37 PM, Scott Marlowe wrote:
> > >>
> > >> On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
> > >> <> ?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
"tablescan". 
>
> Regards,
> Igor Neyman
>
Interesting. Can you force it to use a Seqential Scan and if so, how
does that affect the timing? i.e. Is the index scan actually faster?

Cheers,
Ken


В списке pgsql-performance по дате сообщения:

От: david@lang.hm
Дата:
Сообщение: Re: How to best use 32 15k.7 300GB drives?
От: "Anne Rosset"
Дата:
Сообщение: Re: FW: Queries becoming slow under heavy load