[Bug] graphman sometimes creates the wrong index

[avandras]

Bug report

The following graphman command resulted in an unexpected index:

graphman index create sgd647572 pool_hour_data pool
Index creation started. Please wait.
Index creation completed.

Listing information about the table in the DB showed the following index having been created:

=> \d sgd647572.pool_hour_data
                                          Table "sgd647572.pool_hour_data"
         Column          |   Type    | Collation | Nullable |                        Default                        
-------------------------+-----------+-----------+----------+-------------------------------------------------------
 vid                     | bigint    |           | not null | nextval('sgd647572.pool_hour_data_vid_seq'::regclass)
 block_range             | int4range |           | not null | 
 id                      | text      |           | not null | 
 period_start_unix       | integer   |           | not null | 
 pool                    | text      |           | not null | 
 [...]
Indexes:
    "pool_hour_data_pkey" PRIMARY KEY, btree (vid)
    "attr_15_0_pool_hour_data_id" btree (id)
[...]
    "attr_15_9_pool_hour_data_fee_growth_global_1x128" btree (fee_growth_global_1x128)
    "brin_pool_hour_data" brin (lower(block_range) int4_minmax_multi_ops, COALESCE(upper(block_range), 2147483647) int4_minmax_multi_ops, vid int8_minmax_multi_ops)
    "manual_pool_hour_data_pool" btree (pool, block_range)
    "pool_hour_data_block_range_closed" btree (COALESCE(upper(block_range), 2147483647)) WHERE COALESCE(upper(block_range), 2147483647) < 2147483647
    "pool_hour_data_id_block_range_excl" gist (id, block_range)

I saw this behaviour earlier on other SGs as well, in one case on a token table.

Relevant log output

No response

IPFS hash

QmZeCuoZeadgHkGwLwMeguyqUKz1WPWQYKcKyMCeQqGhsF

Subgraph name or link to explorer

No response

Some information to help us out

• [ ] Tick this box if this bug is caused by a regression found in the latest release.
• [X] Tick this box if this bug is specific to the hosted service.
• [X] I have searched the issue tracker to make sure this issue is not a duplicate.

OS information

None

[lutter]

That should have been created as a GiST index.

[incrypto32]

@lutter btree indexes are the default for graphman currently, are you suggesting to make the default to gist ? or somehow detect which index is the best if not provided explicitly by the user, if so how do i detect that?

[avandras]

@incrypto32 and @lutter Nope, it should be a btree index, but block_range is added there for no good reason (at least from a DBA point of view :) On tables where there is no block_range, one can observe block$ being added.

[lutter]

In this case, since the user asked for an index on pool, it should be a BTree index. Had they asked for an index involving block_range, we would have to use a GiST index as putting block_range into a BTree is pretty much pointless.