Re: Optimizer not using index on 120M row table

From: "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Optimizer not using index on 120M row table
Date: 2003-04-08 14:13:31
Message-ID: 20030408091330.R31861@flake.decibel.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Tue, Apr 08, 2003 at 02:04:30AM -0400, Neil Conway wrote:
> On Tue, 2003-04-08 at 00:35, Jim C. Nasby wrote:
> > On Tue, Apr 08, 2003 at 12:29:00AM -0400, Neil Conway wrote:
> > > Storing visibility information in index entries (in addition to heap
> > > tuples) doesn't strike me as a good idea: for one thing, an
> > > UPDATE/DELETE would require touching both heap tuples and any index
> > > entries that point to them. It would also bloat the size of indexes.
> >
> > True, though these could require touching the indexes anyway due to the
> > data changes (or at least I assume so, given how other RDBMS's work).
>
> Not in the case of DELETE (since you don't need to add a new index
> entry, and the logic to determine whether a tuple is deleted is based
> upon the visibility information stored in the heap). In the case of
> UPDATE, chances are that the index entry you need to add for the new
> version of the tuple isn't on the same page as the old one -- so the
> fact that the two pages happen to belong to the same index doesn't
> really help.
>
> > It might be a useful option to allow
>
> Indeed, I can see how some might find it useful. Perhaps you'd like to
> implement it? :-)

Sure, if it can be done strictly in SQL. :)
--
Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2003-04-08 14:25:39 Re: [GENERAL] Casting
Previous Message Jan Wieck 2003-04-08 13:52:32 Re: Backpatch FK changes to 7.3 and 7.2?