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
