DEV Community

Muhammad Farooq
Muhammad Farooq

Posted on

Search By Inequality and range in PostgreSQL B-Tree Indexes

Read This Blog for B-Tree indexes in PostgreSQL and search by Equality in B-Tree:

B-Tree indexes in PostgreSQL and Search by Equality in It

In this blog Search by Inequality and Search by range in PostgreSQL B-Tree Indexes will be explained.

Search by inequality

When searching by the condition "indexed-field <=, <, >, or >= expression". we first find a value (if any) in the index by the equality condition "indexed-field = expression" as explained in previous post (the link to that post is given above you can refer to that if you need) and then walk through leaf pages in the appropriate direction to the end.

The figure illustrates this process for n ≤ 35:

Searching with inequality example for n <= 35

The "greater" and "less" operators are supported in a similar way, except that the value initially found must be dropped.

for "less" we move left from the value found by equality condition, and for "greater" we move right to the value we found by equality condition

Search by range

When searching by range "expression1 ≤ indexed-field ≤ expression2", we find a value by condition "indexed-field = expression1", and then keep walking through leaf pages from left to right while the condition "indexed-field ≤ expression2" is met; or vice versa: start with the second expression "expression2", and walk in an opposite direction until we reach the first expression.

The figure shows this process for condition 23 ≤ n ≤ 64:

Search by range example 23 <= n <= 64

I hope this post will help you

See More at : Indexes in PostgreSQL - (Btree)

Visit to know about Apache Age a PostgreSQL extension

Apache AGE: age.apache.org
Apache AGE (Github) : github.com/apache/age

Top comments (0)