[PERFORM] Index not being used on composite type for particular query - Mailing list pgsql-performance

I'm using: `PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.3.0-5ubuntu1) 6.3.0 20170124, 64-bit`

And for the purposes of this testing `SET enable_seqscan=false`.

This uses the index:

    EXPLAIN ANALYZE SELECT * FROM shot 

    WHERE lower(shot.matchsecond) <@ (0, numrange(5, 10))::matchsecond_type;

    Bitmap Heap Scan on shot  (cost=471.17..790.19 rows=50 width=45) (actual time=2.601..29.555 rows=5 loops=1)

      Recheck Cond: (((matchsecond).match_id)::integer = (0)::integer)

      Filter: ((numrange(lower(((matchsecond).second)::numrange), lower(((matchsecond).second)::numrange), '[]'::text))::numrange <@ ('[5,10)'::numrange)::numrange)

      Rows Removed by Filter: 9996

      Heap Blocks: exact=94

      Buffers: shared hit=193

      ->  Bitmap Index Scan on ix_shot_matchsecond  (cost=0.00..471.16 rows=10001 width=0) (actual time=2.516..2.516 rows=10001 loops=1)

            Index Cond: (((matchsecond).match_id)::integer = (0)::integer)

            Buffers: shared hit=99

    Planning time: 0.401 ms

    Execution time: 29.623 ms

But this doesn't:

    EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM shot 

    WHERE lower(shot.matchsecond) <@ ((shot.matchsecond).match_id, numrange(5, 10))::matchsecond_type;

    Seq Scan on shot  (cost=10000000000.00..10000000319.02 rows=1 width=45) (actual time=0.091..20.003 rows=5 loops=1)

      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))

      Rows Removed by Filter: 9996

      Buffers: shared hit=94

    Planning time: 0.351 ms

    Execution time: 20.075 ms

Note the `0` in the first compared to `(shot.matchsecond).match_id` in the second on the right hand side of the `<@`.  Interestingly, if the left hand side is simply `shot.matchsecond` instead of `lower(shot.matchsecond)`, the query manages to use the index.  The index is also used when constructing the numrange with functions like `numrange(lower((shot.matchsecond).second), lower((shot.matchsecond).second + 10))`.

Here are the relevant definitions:

    CREATE DOMAIN matchsecond_match AS integer NOT NULL;

    CREATE DOMAIN matchsecond_second AS numrange NOT NULL CHECK(VALUE <> numrange(0,0));

    

    CREATE TYPE matchsecond_type AS (

        match_id matchsecond_match,

        second matchsecond_second

    );

    

    CREATE OR REPLACE FUNCTION matchsecond_contains_range(matchsecond_type, matchsecond_type)

    RETURNS BOOLEAN AS $$ SELECT $1.match_id = $2.match_id AND $1.second @> $2.second $$

    LANGUAGE SQL;

    

    CREATE OPERATOR @> (

        LEFTARG = matchsecond_type,

        RIGHTARG = matchsecond_type,

        PROCEDURE = matchsecond_contains_range,

        COMMUTATOR = <@,

        RESTRICT = eqsel,

        JOIN = eqjoinsel    

     );

    

    CREATE OR REPLACE FUNCTION matchsecond_contained_by_range(matchsecond_type, matchsecond_type)

    RETURNS BOOLEAN AS $$ SELECT $1.match_id = $2.match_id AND $1.second <@ $2.second $$

    LANGUAGE SQL;

    

    CREATE OPERATOR <@ (

        LEFTARG = matchsecond_type,

        RIGHTARG = matchsecond_type,

        PROCEDURE = matchsecond_contained_by_range,

        COMMUTATOR = @>,

        RESTRICT = eqsel,

        JOIN = eqjoinsel    

     );

    

    CREATE OR REPLACE FUNCTION lower(matchsecond_type)

    RETURNS matchsecond_type AS

    $$ SELECT ($1.match_id, numrange(lower($1.second), lower($1.second), '[]'))::matchsecond_type $$

    LANGUAGE SQL;

And a test table:

Reminder: Use `CREATE EXTENSION btree_gist;`

    CREATE TABLE shot AS(

        SELECT i AS id, (0, numrange(i, i+1))::matchsecond_type AS matchsecond

        FROM generate_series(0,10000) AS i

    );

    

    ALTER TABLE shot ADD PRIMARY KEY (id);

    CREATE INDEX ix_shot_matchsecond

        ON shot

        USING gist (((matchsecond).match_id), ((matchsecond).second));