VERSION 01 / BASELINE

ILIKE substring search.

The deliberately naive baseline. Search raw text, measure what happens, inspect PostgreSQL, then improve it.

Case insensitive Substring matching No ranking No stemming

Try

Query none / Returned 0 / Strategy ILIKE substring scan

REPORT

What happened during this search?

Application measurements and PostgreSQL execution diagnostics are intentionally shown separately.

Application measurements

Measured from Python while the application performs the search.

Python

Application time -- ms

Wall-clock time measured with perf_counter().

Current Python memory -- MB

Tracked Python memory still allocated when measurement finished.

Peak Python memory -- MB

Highest tracked Python allocation observed by tracemalloc.

Important

These memory numbers describe Python allocations. They do not represent PostgreSQL server RAM usage.

PostgreSQL execution

Parsed from EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON).

PostgreSQL

HOW POSTGRESQL FOUND THE ROWS

Run a search to inspect the execution plan.

PostgreSQL will explain whether it scanned table rows sequentially or used another access strategy.

Search strategy --

Waiting for PostgreSQL.

Database execution -- ms

Total execution time reported by PostgreSQL itself.

Planning time -- ms

Time PostgreSQL spent deciding how to execute the query.

Time until first row -- ms

How long the scan node took before producing its first result.

Time until scan finished -- ms

Time reported when that scan operation completed.

Rows returned by scan --

Rows emitted by the inspected database scan node.

Rows checked but rejected --

Rows PostgreSQL inspected but discarded because they did not match the filter.

Cached database pages --

Shared buffer hits: pages PostgreSQL already had available in memory.

Database pages read --

Shared buffer reads: pages that were not already present in PostgreSQL's shared buffer cache.

PLAIN ENGLISH

What does this report mean?

Run a search and SearchLab will translate PostgreSQL's execution plan into a short explanation here.

RAW QUERY See exactly what PostgreSQL executed

Open

EXPLAIN (
    ANALYZE,
    BUFFERS,
    FORMAT JSON
)
SELECT
    id,
    name,
    description,
    brand,
    category,
    price,
    rating,
    stock
FROM products
WHERE
    name ILIKE %s
    OR description ILIKE %s
    OR brand ILIKE %s
    OR category ILIKE %s
LIMIT 10;

RAW RESPONSE See PostgreSQL's original JSON plan

Open

Run a search to inspect the raw EXPLAIN response.

Version benchmark

The same experiment will be repeated as the search architecture evolves.

SearchLab

Dataset 100,000 products

Current query --

Current version V1

ILIKE Substring baseline

App -- ms

PostgreSQL -- ms

Plan --

Full-text search Linguistic retrieval

App -- ms

PostgreSQL -- ms

Plan --

FTS + GIN Indexed full-text retrieval

App -- ms

PostgreSQL -- ms

Plan --

Fuzzy fallback Typo-tolerant retrieval

App -- ms

PostgreSQL -- ms

Plan --

Cached search Redis-assisted retrieval

App -- ms

PostgreSQL -- ms

Plan --

Benchmark rule

Compare versions using the same dataset, query, machine, database state and measurement method. One random run is not a scientific benchmark.

RESULTS

Matching products

Search results will appear here. The first 10 are shown initially.

No search yet

Run a query above to inspect both its database behavior and returned products.

UNDER THE HOOD

Why V1 behaves this way

Open any topic to review the reasoning behind the baseline implementation.

What V1 actually does The complete search architecture

V1 treats search as simple string pattern matching.

User query ↓ query.strip() ↓ %query% ↓ ILIKE across four columns ↓ LIMIT 10 ↓ Results

There is no ranking engine, stemming, fuzzy matching, full-text index or search-specific infrastructure yet.

LIKE, ILIKE, `%` and `_` PostgreSQL pattern matching

LIKE performs case-sensitive matching while PostgreSQL ILIKE performs case-insensitive matching.


                                        apple%

Starts with apple


                                        %apple

Ends with apple


                                        %apple%

Contains apple


                                        M_cBook

`_` represents exactly one character

Why multi-word search breaks Substrings are not language

Suppose the product is:

Logitech Wireless Mechanical Keyboard

and the user searches:

wireless keyboard

V1 creates:

%wireless keyboard%

That exact contiguous substring does not exist because Mechanical appears between the two words.

Why there is no stemming Characters are not linguistic meaning

To ILIKE, these are simply different strings:

run runner running runs

V1 does not know that they share a linguistic root. PostgreSQL Full-Text Search will introduce that concept in V2.

Why there is no relevance ranking Match versus no match

A product containing MacBook Air in its name should probably rank above a sleeve that merely mentions MacBook Air in its description.

V1 does not naturally calculate:

MacBook Air          0.93
Laptop Sleeve        0.61
USB-C Charger        0.42

Without explicit ranking logic, matching rows do not have a useful relevance score.

Why `%keyboard%` can be expensive Leading wildcard problem

The leading `%` means keyboard can appear anywhere inside the stored value.

keyboard...
gaming keyboard...
wireless keyboard...
case for keyboard...

PostgreSQL therefore cannot simply assume where the match begins. Depending on the query and available indexes, it may inspect many rows.

What a Sequential Scan means Reading rows and applying the filter

If PostgreSQL reports:

Seq Scan on products

it means PostgreSQL chose to scan table data and test rows against the search condition instead of locating candidates through an appropriate index access path.

The report above translates that into Sequential Scan so you do not have to memorize planner abbreviations.

What buffer hits and reads mean PostgreSQL page activity

PostgreSQL works with database pages.

Shared hit The required page was already available in PostgreSQL's shared buffer cache.

Shared read PostgreSQL needed to bring the page into shared buffers.

This is why the same query can behave differently between a cold and warm run.

Why LIMIT 10 can hide scan cost PostgreSQL may stop early

If matches are common, PostgreSQL can stop after it has produced the ten rows required by:

LIMIT 10

A sequential scan therefore does not automatically mean all 100,000 rows were inspected.

This is why V1 includes a deliberately nonexistent test query:

qzxneverexists92847

PostgreSQL cannot stop early because no tenth match ever appears.

Why Python time and PostgreSQL time differ Two different measurement boundaries

perf_counter() measures application-observed elapsed time around the database operation.

That can include:

Opening the database connection
Sending the query
PostgreSQL executing it
Transferring result data
Psycopg converting values
Fetching rows

PostgreSQL's Execution Time measures database-side execution from EXPLAIN ANALYZE.

They are different metrics and should remain separate.

Why tracemalloc is not database RAM Python process versus PostgreSQL

tracemalloc tracks Python memory allocations.

It can observe allocations associated with Python lists, dictionaries, strings and Psycopg result objects.

PostgreSQL runs separately, so the reported peak memory must not be described as PostgreSQL query memory.

Why V1 exists Establish the baseline before V2

V1 deliberately does not solve:

stemming
tokenization
relevance ranking
typo tolerance
fuzzy search
full-text indexes
autocomplete
caching

Build the simplest correct search, measure it, understand where it breaks, then evolve the architecture deliberately.

NEXT V2 — PostgreSQL Full-Text Search