Re: bug with expression index on partition

On 2018/06/21 16:19, Amit Langote wrote:
> I updated the patch so that even DefineIndex will check if any whole-row
> Vars were encountered during conversion and error out if so.

I first thought of starting a new thread for this, but thought I'd just
reply here because the affected code is nearby.

I was wondering if it wouldn't hurt to allow whole-row vars to be present
in the expressions of inherited indexes.  If we did allow it, the query
shown in the example below is able to use the indexes on partitions.

create table p (a int) partition by hash (a);
create table p1 partition of p for values with (modulus 3, remainder 0);
create table p2 partition of p for values with (modulus 3, remainder 1);
create table p3 partition of p for values with (modulus 3, remainder 2);
create index on p ((p));

explain (costs off) select p from p order by p;
              QUERY PLAN
---------------------------------------
 Merge Append
   Sort Key: ((p1.*)::p)
   ->  Index Scan using p1_p_idx on p1
   ->  Index Scan using p2_p_idx on p2
   ->  Index Scan using p3_p_idx on p3
(5 rows)

After applying the patch in my last email, each of
generateClonedIndexStmt, CompareIndexInfo, and DefineIndex reject
inheriting an index if its expressions are found to contain whole-row
vars.  Now, one can create those indexes on partitions individually, but
they cannot be matched to an ORDER BY clause of a query accessing those
partitions via the parent table.

drop index p_p_idx;
create index on p1 ((p1));
create index on p2 ((p2));
create index on p3 ((p3));

explain (costs off) select p from p order by p;
         QUERY PLAN
----------------------------
 Sort
   Sort Key: ((p1.*)::p)
   ->  Append
         ->  Seq Scan on p1
         ->  Seq Scan on p2
         ->  Seq Scan on p3
(6 rows)

It is of course usable if partition's accessed directly.

explain (costs off) select p1 from p1 order by p1;
            QUERY PLAN
----------------------------------
 Index Scan using p1_p1_idx on p1
(1 row)

OTOH, an inherited index with whole-row vars (if we decide to start
allowing them as I'm proposing) cannot be used if partition's accessed
directly.

drop index p1_p1_idx;
create index on p ((p));
explain (costs off) select p1 from p1 order by p1;
      QUERY PLAN
----------------------
 Sort
   Sort Key: p1.*
   ->  Seq Scan on p1
(3 rows)

but maybe that's tolerable.

Thoughts?

Thanks,
Amit