Re: WIP: Covering + unique indexes.

On Tue, Jan 5, 2016 at 11:55 PM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> 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)

If you set enable_sort=off, then you get the index-only scan with no
sort. So it believes the index can be used for ordering (correctly, I
think), just sometimes it thinks it is not faster to do it that way.

I'm not sure why this would be a correctness problem. The covered
column does not participate in uniqueness checks, but it still usually
participates in index ordering. (That is why dummy op-classes are
needed if you want to include non-sortable-type columns as being
covered.)

>
> 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.

I think this just causes differences in planner statistics leading to
different plans. ANALYZE the table and it goes back to doing the
sort, because it thinks the sort is faster.

Cheers,

Jeff