RisingWave Community - How can I improve JSONB query performance in PostgreSQL?

I'm ingesting complex JSON documents via Kafka into PostgreSQL and using JSONB data type to store them. I've noticed some performance issues even with a few thousand documents, and I'm concerned about how it will scale to millions of documents and larger sizes (megabytes each). I'm currently writing queries directly against JSONB without parsing. Here's an example of the type of queries I'm running:

CREATE MATERIALIZED VIEW something AS
SELECT
    (raw -> 'a' ->> 'c') as foo,
    to_timestamp((raw -> 'b' -> 'd')::bigint::decimal / 1000.0) as bar,
    jsonb_array_elements_text(raw -> 'a' -> 'e') as foobar,
    (raw -> 'b' ->> 'f') as barfoo
FROM kafka;

I'm wondering if there are any optimizations or best practices I should consider to improve the performance of these queries.

To improve JSONB query performance in PostgreSQL, consider the following tips:

Indexing: Create indexes on the JSONB columns, especially using GIN (Generalized Inverted Index) indexes which are well-suited for JSONB data types.
Query Structure: Optimize your queries by fetching only the necessary fields from the JSONB column to minimize the amount of data processed.
Materialized Views: Use materialized views to cache the results of complex queries, but be aware that they need to be refreshed periodically.
Partitioning: If you have a large number of documents, consider partitioning your data to improve query performance.
Avoid Unnecessary Computations: Simplify your queries by avoiding unnecessary JSONB operations and computations.
Performance Monitoring: Use tools like EXPLAIN to analyze query plans and identify bottlenecks.
Hardware Considerations: Ensure that your database server has sufficient resources (CPU, memory, I/O) to handle the workload.
Parallel Processing: Take advantage of PostgreSQL's parallel query processing capabilities if your server has multiple CPU cores.
Update PostgreSQL: Use the latest version of PostgreSQL as it may contain improvements and optimizations for JSONB data types.
Application-Side Parsing: If possible, parse the JSON data in your application before inserting it into the database to reduce the load on PostgreSQL.

Remember that optimizing JSONB performance is often specific to your use case, so you may need to experiment with different strategies to find what works best for your situation.