PostgreSQL Index on JSON

by

Your other two indexes won’t work simply because the ->> operator returns text, while you obviously have the jsonb gin operator classes in mind. Note that you only mention json, but you actually need jsonb for advanced indexing capabilities.

To work out the best indexing strategy, you’d have to define more closely which queries to cover. Are you only interested in cows? Or all animals / all tags? Which operators are possible? Does your JSON document also include non-animal keys? What to do with those? Do you want to include rows in the index where cows (or whatever) don’t show up in the JSON document at all?

Assuming:

  • We are only interested in cows at the first level of nesting.
  • The value is always a valid integer.
  • We are not interested in rows without cows.

I suggest a functional btree index, much like you already have, but cast the value to integer. I don’t suppose you’d want the comparison evaluated as text (where ‘2’ is greater than ‘1111’).

CREATE INDEX animal_index ON farm (((animal ->> 'cow')::int));  -- !

The extra set of parentheses is required for the cast shorthand to make the syntax for the index expression unambiguous.

Use the same expression in your queries to make Postgres realize the index is applicable:

SELECT * FROM farm WHERE (animal ->> 'cow')::int > 3;

If you need a more generic jsonb index, consider:

For a known, static, trivial number of animals (like you commented), I suggest partial indexes like:

CREATE INDEX animal_index ON farm (((animal ->> 'cow')::int))
WHERE (animal ->> 'cow') IS NOT NULL;

CREATE INDEX animal_index ON farm (((animal ->> 'chicken')::int))
WHERE (animal ->> 'chicken') IS NOT NULL;

Etc.

You may have to add the index condition to the query:

SELECT * FROM farm
WHERE (animal ->> 'cow')::int > 3
AND   (animal ->> 'cow') IS NOT NULL;

May seem redundant, but may be necessary. Test with ANALYZE!

More Related Contents:

  1. Index for finding an element in a JSON array
  2. Optimize GROUP BY query to retrieve latest row per user
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Does PostgreSQL support “accent insensitive” collations?
  5. How do I query using fields inside the new PostgreSQL JSON datatype?
  6. Create unique constraint with null columns
  7. Query for array elements inside JSON type
  8. How can I import a JSON file into PostgreSQL?
  9. Return multiple columns of the same row as JSON array of objects
  10. Custom SERIAL / autoincrement per group of values
  11. Safely rename tables using serial primary key columns
  12. How do I force Postgres to use a particular index?
  13. Serial numbers per group of rows for compound key
  14. PostgreSQL: Which Datatype should be used for Currency?
  15. Postgres unique constraint vs index
  16. List columns with indexes in PostgreSQL
  17. How do you do date math that ignores the year?
  18. What’s the proper index for querying structures in arrays in Postgres jsonb?
  19. Query for element of array in JSON column
  20. Postgres constraint for unique datetime range
  21. Select (retrieve) all records from multiple schemas using Postgres
  22. Which datatype should be used for currency?
  23. Use email address as primary key?
  24. Operator does not exist: json = json
  25. Temporal database design, with a twist (live vs draft rows)
  26. Database Design for Revisions?
  27. Finding similar strings with PostgreSQL quickly
  28. Window Functions or Common Table Expressions: count previous rows within range
  29. Referencing current row in FILTER clause of window function
  30. Primary key/foreign Key naming convention [closed]

Leave a Comment