On Friday 16 July 2010 21:12:33 Simon Riggs wrote:
> On Fri, 2010-07-16 at 20:41 +0200, Andres Freund wrote:
> > You argue above that you cant change SET [NOT] NULL to be less
> > restrictive because it might change plans - isnt that true for some of
> > the above cases as well?
> >
> > For example UNIQUE/PRIMARY might make join removal possible - which could
> > only be valid after "invalid" tuples where deleted earlier in that
> > transaction. Another case which it influences are grouping plans...
>
> This is only for adding a constraint, not removing it. Join removal
> would be possible after the ALTER finishes, but won't change plans
> already in progress. The idea is to minimise the impact, not maximise
> the benefit of the newly added constraint; I don't think we should block
> all queries just because a few might benefit.
Its not about benefit, its about correctness:
CREATE TABLE testsnap(t int);
INSERT INTO testsnap VALUES(1),(1);
T1:
test=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
Time: 0.853 ms
test=# explain analyze SELECT t1.* FROM testsnap t1 LEFT JOIN testsnap t2 USING(t);
QUERY
PLAN---------------------------------------------------------------------------------------------------------------------
MergeLeft Join (cost=337.49..781.49 rows=28800 width=4) (actual time=0.090..0.118 rows=4 loops=1) Merge Cond: (t1.t
=t2.t) -> Sort (cost=168.75..174.75 rows=2400 width=4) (actual time=0.049..0.051 rows=2 loops=1) Sort Key:
t1.t Sort Method: quicksort Memory: 25kB -> Seq Scan on testsnap t1 (cost=0.00..34.00 rows=2400
width=4)(actual time=0.018..0.023 rows=2 loops=1) -> Sort (cost=168.75..174.75 rows=2400 width=4) (actual
time=0.026..0.033rows=3 loops=1) Sort Key: t2.t Sort Method: quicksort Memory: 25kB -> Seq
Scanon testsnap t2 (cost=0.00..34.00 rows=2400 width=4) (actual time=0.005..0.009 rows=2 loops=1) Total runtime: 0.279
ms(11rows)
T2:
test=# DELETE FROM testsnap;
DELETE 2
Time: 1.184 ms
test=# ALTER TABLE testsnap ADD CONSTRAINT t unique(t);
NOTICE: 00000: ALTER TABLE / ADD UNIQUE will create implicit index "t" for table "testsnap"
LOCATION: DefineIndex, indexcmds.c:471
ALTER TABLE
Time: 45.639 ms
T1:
Time: 1.948 ms
test=# explain analyze SELECT t1.* FROM testsnap t1 LEFT JOIN testsnap t2 USING(t);
QUERY PLAN-----------------------------------------------------------------------------------------------------
SeqScan on testsnap t1 (cost=0.00..1.02 rows=2 width=4) (actual time=0.013..0.016 rows=2 loops=1) Total runtime: 0.081
ms(2rows)
Time: 2.004 ms
test=#
boom.
Andres