3 ms·
If you're using postgres, couldn't you just create an index on the field inside the JSONB column directly? What advantage are you getting from extracting it to
by craftkiller 10mo ago
If you're using postgres, couldn't you just create an index on the field inside the JSONB column directly? What advantage are you getting from extracting it to a separate column?
CREATE INDEX idx_status_gin
ON my_table
USING gin ((data->'status'));
ref: https://www.crunchydata.com/blog/indexing-jsonb-in-postgres https://www.crunchydata.com/blog/indexing-jsonb-in-postgres
- jelder 10mo agoThat works for lookups but not for foreign key constraints.
- craftkiller 10mo agoAh, makes sense. Thanks!
- cies 10mo ago..and it does not make "certain queries easier" (quote from the article).
- morshu9001 10mo agoYou only need gin if you want to index the entire jsonb. For a specific attribute, you can use the default (btree) which I'm guessing is faster.
- a-priori 10mo agoYes, as far as indices go, GIN indices are very expensive especially on modification. They're worthwhile in cases where you want to do arbitrary querying on JSON data, but you definitely don't want to overuse them. If you can get away with a regular index on either a generated column or an expression, then you absolutely should.