Index not being used on composite type for particular query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello,

The index on my composite type seems to be working most of the time, but there's a query I need to run where it's not working even with enable_seqscan=false.  The composite type uses int and numrange subcolumns, and is designed to operate primarily as a range type.

It will probably be easier for you to read the rest of this from my stackexchange post but I'll copy and paste the contents of it here as well. https://dba.stackexchange.com/questions/174099/postgres-composite-type-not-using-index 

The queries in this example are for testing purposes.  It's possible for me to get the index to work by using the int and numrange separately rather than creating a new matchsecond_type, but using the composite type makes things much easier further down the pipeline where I have to tie this in with an ORM.

This should include everything necessary to test it out yourself.
-----------------------------------------------

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

----------------------------------------------

Thank you

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux