Re: refresh materialized view concurrently

Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> wrote:

> After a couple of more attempts trying to break it, I mark this
> as ready to go.

Thanks.

> One small question:  why do we use multiple unique indexes if
> exist?  

Two reasons.

(1)  By only matching up rows which test as equal on all columns
used in primary keys, we can use UPDATE for the matches rather than
DELETE and INSERT without fear of hitting a transient duplicate key
error on one of the indexes.  Since any update to an indexed column
prevents a HOT update, and results in the equivalent of a DELETE
and an INSERT anyway, there's no performance loss from letting them
not match if *any* column which is part of *any* unique index
doesn't match.

(2)  The planner can use one of the unique indexes to optimize the
diff phase.  How would we pick the one which is fastest?  By
comparing for equality on all the columns used in all unique
indexes, we let the planner decide.  I see no reason to try to
second-guess it; just let it do it's thing.

> One index isn't enough?

It would be enough for correctness, yes.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company