chore(cnpg): enable pg_trgm #665

New issue

Closed

opened 2026-03-28 04:27:12 +00:00 by mfreeman451 · 0 comments

mfreeman451 commented

2026-03-28 04:27:12 +00:00

Owner

Imported from GitHub.

Original GitHub issue: #2047
Original author: @mfreeman451
Original URL: https://github.com/carverauto/serviceradar/issues/2047
Original created: 2025-12-03T05:18:41Z

Performance: ILIKE Usage and Indexes

File: src/query/*.rs

You use ilike (case-insensitive matching) extensively for FilterOp::Like.
code
Rust
FilterOp::Like => query.filter(col_hostname.ilike(value)),
The Risk: Standard Postgres B-Tree indexes cannot be used for ILIKE queries (especially with leading wildcards like %term). These queries will result in full table scans.
Recommendation:
Ensure you have pg_trgm (trigram) extension enabled and GIN/GiST indexes on searchable text columns.
Alternatively, if strict case-insensitivity isn't required, store a normalized lowercase column (hostname_lower) and use standard equality or LIKE 'prefix%' which can use B-Trees.

Imported from GitHub. Original GitHub issue: #2047 Original author: @mfreeman451 Original URL: https://github.com/carverauto/serviceradar/issues/2047 Original created: 2025-12-03T05:18:41Z --- Performance: ILIKE Usage and Indexes File: src/query/*.rs You use ilike (case-insensitive matching) extensively for FilterOp::Like. code Rust FilterOp::Like => query.filter(col_hostname.ilike(value)), The Risk: Standard Postgres B-Tree indexes cannot be used for ILIKE queries (especially with leading wildcards like %term). These queries will result in full table scans. Recommendation: Ensure you have pg_trgm (trigram) extension enabled and GIN/GiST indexes on searchable text columns. Alternatively, if strict case-insensitivity isn't required, store a normalized lowercase column (hostname_lower) and use standard equality or LIKE 'prefix%' which can use B-Trees.

Rows
Columns