Re: Planner can't seem to use partial function indexes with parameter from join

On Sun, 14 Apr 2019 at 21:55, Alastair McKinley
<a(dot)mckinley(at)analyticsengines(dot)com> wrote:
> I have reduced my scenario to a minimal test case with inline comments to illustrate the issue here https://gist.github.com/a-mckinley/1b0e95142789cbc09121b71a83d03f45
>
> Is there something that I am missing to allow the planner to use the underlying indexes? Or is the scenario too complex and should I stick with dynamic sql?

I'd say the biggest part of the problem is that "record" is not in
first normal form. However, it's worse than that as you're having to
perform a join to determine how to fetch the value you want. If
"record" was designed to have a "record_prefix" column and then store
the remainder of the record_text over in the column by that name, then
with an index on record (record_prefix, type_id) you could just do:

explain analyze
select r.type_id,count(*)
from record r
INNER JOIN (VALUES(1,'aa'),(2,'aab')) v(type_id, record_prefix) ON
r.type_id = v.type_id AND r.record_prefix = v.record_prefix
group by r.type_id;

If you're lucky, and there's a good chance you would be, then you'd
get a parameterised nested loop join.

As for why the partial indexes cannot be used; partial indexes can
only be used when the planner is able to match the index up to quals
that will be evaluated in the table level quals (i.e not join quals).
There's no such thing as dynamic index selection at execution time.
The closest thing we have to that is run-time partition pruning in
PG11, but that can't help you either since the partition key cannot
contain values from other tables. You'd still need to normalise the
record table. With that and a partitioned table, there might be
further advantages of partition-wise aggregation, but that might not
buy you much more than just normalising the table.

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