Re: Weird indices

Ian Lance Taylor wrote:
>
> Joseph Shraibman <jks(at)selectacast(dot)net> writes:
>
> A caveat on this reply: I've been studying the Postgres internals, but
> I have not mastered them.
>
> > I understand that keeping different views for different open
> > transactions can be difficult, but after a transaction that updates a
> > row is over why isn't the row marked as 'universally visible' for all
> > new transactions until another update occurs?
>
> It is. This mark is on the tuple in the heap. When a tuple is
> current, and not locked for update, HEAP_XMAX_INVALID is set. After
> the tuple is removed, HEAP_XMAX_COMMITTED is set.

On the heap, but when is the index updated? Not until the next vacuum?
>
> > Maybe I'm not making myself understood. Another way of asking the same
> > thing:
> > Say there is a transaction that is looking at a non-current version of a
> > row. 'non-current' could be the value it was at the start of the
> > transaction (and was updated by another transaction) or was updated by
> > this transaction but not committed yet. When this transaction is over
> > is it really that hard to get rid of the refrence to the old version of
> > the row? There should be a 1 bit field 'is old value and isn't being
> > used by any transaction'. Is that really hard?
>
> There is a 1 bit field indicating that a tuple is an old value.
> Postgres can also determine whether any transaction can see the
> tuple. It does this by storing the transaction ID in the t_xmax
> field. If all current transactions are newer than that transaction
> ID, then that tuple is no longer visible to any transaction.
>
> In fact, I believe that is what the VACUUM command looks for.
>
> > Maybe this is part of the whole 'vacuum later' vs. 'update now'
> > philosophy. If the point of vacuum later is to put off the performance
> > hit until later if it is causing these performance hits on queries
> > because index scans aren't being used then doesn't that mean 'update
> > now' is more likely to pay off in the short run?
>
> I don't follow. A simple VACUUM doesn't update the statistics.
> VACUUM ANALYZE has to do more work.

I'm talking about indices. The index should be updated to only point at
valid rows. But if the index isn't used by the planner then the point
is moot.

>
> Are you suggesting that the statistics should be updated continuously?
> I guess that would be doable, but it would clearly slow down the
> database. For some applications, it would be an obviously bad idea.

No, I'm suggesting that indices should be updated continuously so the
planner can use them without having a performance hit from checking if
tuples are valid or not.

BTW is there any way to tell postgres to do an update at every commit
without waiting for a vacuum? I understand that the postgres core team
thinks it is a bad idea, but there are ways to (sort of) force using
index scans whent he planner doesn't want to, so is there something
similar to force incremental vacuuming at the end of each query?

Java has this option:
-Xincgc enable incremental garbage collection

that isn't recommended, but the java developers recognized that
sometimes a user might want it anyway for whatever reason.

--
Joseph Shraibman
jks(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com