Re: Index not being used on composite type for particular query

Thanks for the fast reply and explanation, Tom. Overall, I have been
pleasantly surprised with the leniency of indexes on range types.

On Sat, May 20, 2017 at 5:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Zac Goldstein <goldzz(at)gmail(dot)com> writes:
> > This uses the index:
> > ...
> > But this doesn't:
>
> > EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM shot
> > WHERE lower(shot.matchsecond) <@ ((shot.matchsecond).match_id,
> > numrange(5, 10))::matchsecond_type;
>
> Well, yeah. After inlining the SQL functions, what you have is
>
> > Filter: ((((matchsecond).match_id)::integer =
> > ((matchsecond).match_id)::integer) AND
> > ((numrange(lower(((matchsecond).second)::numrange),
> > lower(((matchsecond).second)::numrange), '[]'::text))::numrange <@
> > ('[5,10)'::numrange)::numrange))
>
> and neither half of the AND has the form "indexed_value indexable_operator
> constant", which is the basic requirement for an index condition. We're a
> little bit permissive about what "constant" means, but that most certainly
> doesn't extend to expressions involving columns of the table. So the
> first clause loses because it's got variables on both sides, and the
> second loses because the LHS expression is not what the index is on.
>
> You could build an additional index on that expression, if this shape
> of query is important enough to you to justify maintaining another index.
>
> regards, tom lane
>