I can do the same for a nested level, using ‘->’ to navigate into the hierarchy, as long as I finally get to a single child because regular indexes are on scalar values. In this example,the attribute value is the top level document. Index Cond: ((data -> 'name'::text) = 'Pikachu'::text) Index Scan using dex_name on dex (actual time=0.937.0.939 rows=1 loops=1) Yugabyte=# explain (costs off, analyze, dist) Because the value looked up, which is the name of a Pokemon, is a text value, it makes more sense to query on a text value and index on it. The former indexes the value as a JSONB datatype the latter as a TEXT datatype. It’s possible to index (data->’name’) rather than (data->’name’) as long as the same is used in queries.In addition to the parenthesis for the list of index columns, PostgreSQL requires additional ones when the value-to-index is not a column but an expression.
yugabyte=# create index dex_name on dex ( To efficiently query by name, we can create an index specifically designed for this purpose. Indexing a single value per row from a top-level JSON attributeĮach document includes the Pokemon name as a top-level attribute.Examples of Indexes and the Access Patterns Optimized Now, let’s explore some examples of indexes and the access patterns they optimize. Inverted indexes (GIN in both PostgreSQL and YugabyteDB) that handle cases where each row has multiple values to index, such as an array of values.Regular indexes ( B-Tree in PostgreSQL and LSM-Tree in YugabyteDB) that index values with one value per row, like a single column or attribute.There are two kinds of indexes available: However, even with remote filter pushdown, the Seq Scan still reads more rows than necessary.įast and scalable access to one or a few rows should utilize an indexed condition-in both PostgreSQL and YugabyteDB. to see the execution statistics, do not add ‘dist’ but ‘buffers’ to ‘explain analyze’. On PostgreSQL, which is not distributed and reads from the local shared buffer, this pushdown is not necessary. Due to YugabyteDB’s distributed nature, the table is distributed across three nodes, resulting in three read requests. All 151 rows are read and later filtered to retain only one row.
QUERY JSONB POSTGRES CODE
In this code example, I have disabled the expression pushdown to showcase the ‘Rows Removed by Filter’ statistics. In YugabyteDB, additional optimizations for filtering during the scan are incorporated. *+ Set ( yb_enable_expression_pushdown false ) */ Here is an example: explain (costs off, analyze, dist, summary off) However, for any other query, the entire table needs to be scanned. Please note that in this model, accessing a single object by its primary key (UUID) is fast. \copy dex(data) from program 'curl -s | jq -r ".pokemon | tostring"' Id uuid default gen_random_uuid() primary key, NOTE: The data is sourced from Gianluca Bonifazi’s Pokémon GO Pokédex, and the code assumes the ‘jq’ tool is installed to retrieve one row per Pokémon using ‘psql’. If you’re using PostgreSQL, you can replicate the same. We’ll use YugabyteDB, a distributed SQL database compatible with PostgreSQL. Each Pokémon is stored as a row in a table, identified by a UUID and containing relevant information in a JSONB column. In this example, we’ll index the JSON in PostgreSQL using the Pokémon GO Pokédex as our dataset. Demonstrate JSON Indexing in PostgreSQL: A Pokémon GO Pokédex Example
In SQL, we index the values we select, filter, or sort on, whether they are columns or expressions based on any row’s columns, including JSON attributes. Although the structure is not enforced at write time like normalized tables, we can still add indexes for our access patterns. Indexing JSON documents in PostgreSQL is similar to indexing relational table columns.
0 kommentar(er)
