Postgresql multi-field index on jsonb data does full tablescan when field is not found

Question

I have a table with millions of records. I need to query for the last-added record by timestamp, for a given field. Pretty simple stuff, trivial to do with SQL:

CREATE TABLE records
(
    id integer,
    "timestamp" integer,
    type text
);
CREATE INDEX idx_type_time_sql
ON records (type ASC, timestamp DESC);

Queries are fast, even searching for a type that does not exist in the table.

select * from records where type = 'KNOWN' order by timestamp desc limit 1
select * from records where type = 'UNKNOWN' order by timestamp desc limit 1

I can also almost get it working with NOSQL (aka a jsonb field that contains all object properties):

CREATE TABLE records
(
    id integer,
    json jsonb NOT NULL
)
CREATE INDEX idx_timestamp
ON records (((json->'timestamp')::bigint));

This is fast (a few ms) to find a record when type is found. However, THIS FAILS to use the index if the type is not found in the table. It does a full tablescan that takes 12 seconds or so.

-- fast:
select * from records where json->>'type' = 'KNOWN'
order by (json->'timestamp')::bigint desc limit 1;
-- slow:
select * from records where json->>'type' = 'UNKNOWN'
order by (json->'timestamp')::bigint desc limit 1;

I have tried many different types of jsonb indexes and queries with no luck, eg:

CREATE INDEX IF NOT EXISTS idx_type_timestamp ON records ( (json -> 'type'), ((json -> 'timestamp')::bigint));

Is there any way to get postgresql jsonb indexing working as well as a good old fashioned SQL index, when querying for unknown values? Or is this just a shortcoming of jsonb?

Answer 1

Paying close attention to matching the index syntax with the query syntax was all that was needed to solve this (thanks @jjanes). Once you do, the successful approach is very similar to indexing standard SQL columns.

Index:

CREATE INDEX IF NOT EXISTS idx_json_pair ON records ((json->>'type'),((json->'timestamp')::bigint));

Now, both hit and miss queries are fully indexed and fast:

select * from records where json->>'type' = 'KNOWN' order by (json->'timestamp')::bigint desc limit 1
select * from records where json->>'type' = 'UNKNOWN' order by (json->'timestamp')::bigint desc limit 1