Re: Get more from indices.

Tom Lane wrote:
> "Etsuro Fujita" <fujita.etsuro@lab.ntt.co.jp> writes:
> > [ pathkey_and_uniqueindx_v7_20131203.patch ]

> I started to look at this patch.  I don't understand the reason for the
> foreach loop in index_pathkeys_are_extensible (and the complete lack of
> comments in the patch isn't helping).  Isn't it sufficient to check that
> the index is unique/immediate/allnotnull and its ordering is a prefix of
> query_pathkeys?  If not, what's the rationale for the specific tests being
> made on the pathkeys --- this code doesn't make much sense to me.

Thank you for taking time to look at this patch.  I think it's not
sufficient to check those things.  Let me explain the reason why this patch
has that code.  The patch has that code in order to prevent
build_join_pathkeys() from building incorrect join pathkeys', where the
pathkeys for a join relation constructed by mergejoin or nestloop join are
built normally just by using the outer path's pathkeys.  Without that code,
the patch would produce an incorrect result for such a case.  An example
will be shown below.  A simple approach to avoid this problem would be to
apply this idea only when each pathkey in query_pathkeys references the
indexed relation in addition to that the index is
unique/immediate/allnotnull and its ordering is a prefix of query_pathkeys.
That's the reason.

[Data]
CREATE TABLE t (a int not null, b int not null, c int, d text);
CREATE UNIQUE INDEX i_t_ab ON t (a, b);
INSERT INTO t (SELECT a / 5, 4 - (a % 5), a, 't' FROM
generate_series(000000, 099999) a);
ANALYZE t;
CREATE TABLE t2 (e text, f int);
INSERT INTO t2 VALUES ('t', 2);
INSERT INTO t2 VALUES ('t', 1);
ANALYZE t2;

[Query]
EXPLAIN SELECT * FROM t, t2 WHERE t.a < 10 AND t.d = t2.e ORDER BY t.a, t.b,
t.c, t.d, t2.f LIMIT 4;                                  QUERY PLAN
----------------------------------------------------------------------------
----Limit  (cost=0.29..3.96 rows=4 width=20)  ->  Nested Loop  (cost=0.29..110.17 rows=120 width=20)        Join
Filter:(t.d = t2.e)        ->  Index Scan using i_t_ab on t  (cost=0.29..107.34 rows=60
 
width=14)              Index Cond: (a < 10)        ->  Materialize  (cost=0.00..1.03 rows=2 width=6)              ->
SeqScan on t2  (cost=0.00..1.02 rows=2 width=6)
 
(7 rows)

SELECT * FROM t, t2 WHERE t.a < 10 AND t.d = t2.e ORDER BY t.a, t.b, t.c,
t.d, t2.f LIMIT 4;a | b | c | d | e | f
---+---+---+---+---+---0 | 0 | 4 | t | t | 20 | 0 | 4 | t | t | 10 | 1 | 3 | t | t | 20 | 1 | 3 | t | t | 1
(4 rows)

(Note the column f is sorted in the descending order.)

Sorry for the delay.

Best regards,
Etsuro Fujita