Re: Serious performance problem

From: Hannu Krosing <hannu(at)tm(dot)ee>
To: Alex Pilosov <alex(at)pilosoft(dot)com>
Cc: Antonio Fiol Bonnín <fiol(at)w3ping(dot)com>, Brent Verner <brent(at)rcfile(dot)org>, Tille(at)taru(dot)tm(dot)ee, Andreas <TilleA(at)rki(dot)de>, Claus(at)taru(dot)tm(dot)ee, Hermann <ClausH(at)rki(dot)de>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Serious performance problem
Date: 2001-10-31 08:34:32
Message-ID: 3BDFB798.6760CD90@tm.ee
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Alex Pilosov wrote:
>
> On Tue, 30 Oct 2001, Antonio Fiol [iso-8859-1] Bonnín wrote:
>
> > > | > Seems that problem is very simple :))
> > > | > MSSql can do queries from indexes, without using actual table at all.
> > > | > Postgresql doesn't.
> > > | >
> > > | > So mssql avoids sequental scanning of big table, and simply does scan of
> > > | > index which is already in needed order and has very much less size.
> <snip>
> > > | The consequence for my problem is now: If it is technically possible
> > > | to implement index scans without table lookups please implement it. If
> The feature you are looking for is called 'index coverage'. Unfortunately,
> it is not easy to implement with Postgresql, and it is one of few
> outstanding 'nasties'. The reason you can't do it is follows: Postgres
> uses MVCC, and stores 'when' the tuple is alive inside the tuple. So, even
> if index contains all the information you need, you still need to access
> main table to check if the tuple is valid.
>
> Possible workaround: store tuple validity in index, that way, a lot more
> space is wasted (16 more bytes/tuple/index), and you will need to update
> all indices when the base table is updated, even if indexed information
> have not changed.

AFAIK you will need to update all indexes anyway as MVCC changes the
location
of the new tuple.

-------------
Hannu

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Hannu Krosing 2001-10-31 08:47:00 Re: Serious performance problem
Previous Message Hannu Krosing 2001-10-31 08:28:55 Re: Performance problems???