Support for json/jsonb data types

[leo-ferlin-sutton]

Hi!

First of all, thanks a lot for this extension, it's a really cool :)

Describe the bug

index_advisor function does not suggest indexes for queries where the WHERE close uses the ->> operator.

To Reproduce
Steps to reproduce the behavior:

1. Install HypopG
2. Install index_advisor

bla=> \d+ products
                                                                 Table "public.products"
   Column   |          Type          | Collation | Nullable |               Default                | Storage  | Compression | Stats target | Description
------------+------------------------+-----------+----------+--------------------------------------+----------+-------------+--------------+-------------
 id         | integer                |           | not null | nextval('products_id_seq'::regclass) | plain    |             |              |
 name       | character varying(255) |           | not null |                                      | extended |             |              |
 properties | jsonb                  |           |          |                                      | extended |             |              |
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)
Access method: heap

bla=> SELECT
  id,
  name,
  properties
FROM
  products
WHERE
  properties ->> 'color' IN ('white');
 id |        name        |                    properties
----+--------------------+---------------------------------------------------
  3 | Ink Fusion T-Shirt | {"size": ["S", "M", "L", "XL"], "color": "white"}
(1 row)

bla=> select * from index_advisor('select id,name,properties FROM products WHERE properties ->> ''color'' IN (''white'')');
 startup_cost_before | startup_cost_after | total_cost_before | total_cost_after | index_statements | errors
---------------------+--------------------+-------------------+------------------+------------------+--------
 0.00                | 0.00               | 12.10             | 12.10            | {}               | {}
(1 row)

-- it works fine with a varchar column
bla=> select * from index_advisor('select id,name,properties FROM products WHERE name IN (''white'')');
 startup_cost_before | startup_cost_after | total_cost_before | total_cost_after |                    index_statements                    | errors
---------------------+--------------------+-------------------+------------------+--------------------------------------------------------+--------
 0.00                | 0.02               | 11.75             | 8.04             | {"CREATE INDEX ON public.products USING btree (name)"} | {}
(1 row)

Expected behavior

I expected the index_statements to have suggestion of an index.

This index seems like it could have been suggested:

bla=> CREATE INDEX IF NOT EXISTS idx_properties ON products ((properties->>'color'));
CREATE INDEX

bla=> select * from index_advisor('select id,name,properties FROM products WHERE properties ->> ''color'' IN (''white'')');
 startup_cost_before | startup_cost_after | total_cost_before | total_cost_after | index_statements | errors
---------------------+--------------------+-------------------+------------------+------------------+--------
 0.00                | 0.00               | 1.04              | 1.04             | {}               | {}
(1 row)

Versions:

• PostgreSQL: 16 (Aurora)

Notes

This does not seem to be a hypopg limitations,

dre=> select * from hypopg_create_index('CREATE INDEX IF NOT EXISTS hypo_idx_properties ON products ((properties->>''color''))');
 indexrelid |         indexname
------------+----------------------------
      14498 | <14498>btree_products_expr
(1 row)

dre=> select * from hypopg_list_indexes;
 indexrelid |         index_name         | schema_name | table_name | am_name
------------+----------------------------+-------------+------------+---------
      14498 | <14498>btree_products_expr | public      | products   | btree
(1 row)

[leo-ferlin-sutton]

Nevermind, looks like it's intended behavior:

and pa.atttypid in (20,16,1082,1184,1114,701,23,21,700,1083,2950,1700,25,18,1042,1043)

the atttypid for jsonb is 3802.

If I remove the restriction it attempts to create an index but not a correct one for the query:

 public      | products   | properties  | select public.hypopg_create_index($i$create index on public.products(properties)$i$)

Which, I assume, is why there was a limited list of supported attypid in the first place.

[olirice]

That's right, we currently only support single column btree indexs, which are not appropraite for json/b data types.

The underlying HypoPG extension has support for

btree
brin
hash (requires PostgreSQL 10 or above)
bloom (requires the bloom extension to be installed)

so, unfortunately, there is currently no appropriate target for JSON/B columns

I'll leave this open in case other come across this limitation too