1

I have a composite type where one subcolumn is an integer and the other is a numrange.

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

I tried adding this index, but it didn't change anything

CREATE INDEX ix_shot_lower_matchsecond
    ON shot (lower(matchsecond));
  • As you have no index usable on lower(shot.matchsecond), it is no wonder no index is being used in that case. Also, using the index as it is results in a plan that happens to be executed faster than the one with the bitmap index scan, so it's not a big win so far. – dezso May 20 '17 at 11:33
  • What do you mean by I have no index usable on it? The first example uses lower(shot.matchsecond) and uses an index. As for the speed, this query is just a way of testing whether the index is being used. – user3747260 May 20 '17 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.