logoalt Hacker News

craftkillerlast Friday at 4:18 PM2 repliesview on HN

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

Replies

jelderlast Friday at 4:23 PM

That works for lookups but not for foreign key constraints.

show 2 replies
morshu9001last Friday at 6:47 PM

You 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.

show 1 reply