Postgres 9.3 index involving JSON column

Question

I have this table structure:

CREATE TABLE user_items
(
  user_id bigint references users(id) NOT NULL,
  item_id bigint references items(id) NOT NULL,
  col1 json DEFAULT '[{"text":""}]',
  col2 json DEFAULT '[{"date":"","text":""}]',
  col3 json DEFAULT '{"text":""}',
  PRIMARY KEY (user_id, item_id)
)

I will be running queries such as this:

SELECT * FROM user_items WHERE item_id = '?' AND col1 IS NOT NULL

Do I need an index (item_id, col1) in this case ?

And if so, what's the right way to do it, because when trying it Postgres is throwing an error since col1 is a JSON type.

Erwin Brandstetter · Accepted Answer · 2014-02-18 00:55:29Z

1

I suggest to use a partial index on item_id:

CREATE INDEX foo_idx ON user_items (item_id)
WHERE col1 IS NOT NULL

The data type of col1 is irrelevant here. Be sure to include the verbatim WHERE clause in queries to allow Postgres to use this index.

answered Feb 18, 2014 at 0:55

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

haknick Over a year ago

Thanks for this. I thought the reason why the data type is relevant is because I can't just do an index on (item_id, col1). Which seems to make sense, since 'col1' is a JSON type.

Erwin Brandstetter Over a year ago

@haknick: The data type would be relevant for an index on the JSON column. I avoided the problem by making it a condition for the partial index instead. Testing for NULL works for any type.

jjanes · Accepted Answer · 2014-02-18 00:28:04Z

0

You would only need it if each item_id occurs many times and col1 is usually NULL. If either of those is not true, just make the index on (item_id). The database will have to visit the row and filter out the ones where col1 is NULL, but if NULL is rare that will be no big deal.

If NULL is common, then try a "functional" or "expression" index on (item_id, (col1 is not null))

answered Feb 18, 2014 at 0:28

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Collectives™ on Stack Overflow

Postgres 9.3 index involving JSON column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related