Re: WIP: Covering + unique indexes.

On 4 January 2016 at 21:49, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> I've not tested the patch yet. I will send another email soon with the
> results of that.
>

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:

reindex table ab;
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)

It looks normal again.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services