Re: WIP: Covering + unique indexes. - Mailing list pgsql-hackers

Hi,

As promised I've done some testing on this, and I've found something which is not quite right:

create table ab (a int,b int);

insert into ab select x,y from generate_series(1,20) x(x), generate_series(10,1,-1) y(y);

create index on ab (a) including (b);

explain select * from ab order by a,b;

                        QUERY PLAN                        

----------------------------------------------------------

 Sort  (cost=10.64..11.14 rows=200 width=8)

   Sort Key: a, b

   ->  Seq Scan on ab  (cost=0.00..3.00 rows=200 width=8)

(3 rows)

This is what I'd expect

truncate table ab;

insert into ab select x,y from generate_series(1,20) x(x), generate_series(10,1,-1) y(y);

explain select * from ab order by a,b;

                                  QUERY PLAN                                  

------------------------------------------------------------------------------

 Index Only Scan using ab_a_b_idx on ab  (cost=0.15..66.87 rows=2260 width=8)

(1 row)

This index, as we've defined it should not be able to satisfy the query's order by, although it does give correct results, that's because the index seems to be built wrongly in cases where the rows are added after the index exists.

If we then do:

It looks normal again.

--