LZ4 ( lz4) is considerably faster, while compressing typically a bit less. Currently the only available alternative is lz4. The default is dictated by the config setting default_toast_compression, which is set to pglz by default. Since Postgres 14, you can switch the compression algorithm (if support is enabled in your version!).

Postgres has to 'de-toast' the whole large column, just to extract some small attributes. One major cost factor will be compression. I am looking to see if I can make this faster in short termĭrop the cast to json from the query. You probably can still do something in the short term. So you would have to include value itself in the index, even if just as INCLUDE column - totally spoiling the whole idea. It considers a query to be potentially executable by index-only scan only when all columns needed by the query are available from the index. However, PostgreSQL's planner is currently not very smart about such cases. Since your values are big, an expression index with just some small attributes can be picked up by Postgres in an index-only scan, even when retrieving all rows (where it otherwise would ignore indexes). Like a_horse already advised (and you mentioned yourself), the proper fix is to extract those attributes to separate columns, normalizing your design to some extent. Value::json -> 'countr圜ode' AS 'countr圜ode', It's the data that is further down in the json blob that often changes. Not sure if it helps but the underlying data that makes up this result set doesn't change often. Is there a different index I should be using? The long term vision is to re-write the application to move that data out of the json but that is something is at least 30-40 man days of work due to complexity in other parts of the application so I am looking to see if I can make this faster in short term. I tried adding an index of CREATE INDEX idx_tbl_data ON data USING gin (value) but that didn't help. The json blobs are a bit large but the data I need is all in the top level of the json nesting if that helps. It is about 300 rows but takes 12 seconds to select this data from the 5 json elements. I inherited an application that queries a PostgreSQL table called data with a field called value where value is blob of json of type jsonb. I am trying to speed up the querying of some json data stored inside a PostgreSQL database.

Postgres jsonb query performance