# Att fixa platsen's sökningar: PostgreSQL-fulla sökningar -Textsökningar | ( och var den bryter sig Suchen är en av de egenskaper som alla underskattar. *konceptuellt* rätt men textmässigt fel. När en sökning misslyckas i dessa fall , tycker inte användaren Det är också sometigt att "'" har plagierat den här sidan sedan dess inception. [OpenSearch](/blog/textsearchingpt2), har arbetat med PostgreSQLs fulltextsökningar med det 's fina vektorgrejer men var aldrig riktigt nöjda med det **bugg** i synnerhet nu bygger jag ett sökverktyg [***klar*RAG**](https://www.lucidrag.com) ja, jag tänkte att jag skulle äntligen fixa det. Vare sig det är eller inte är en annan fråga Den här artikeln handlar inte om att bygga en sökmotor från början, det handlar om att fixa de skarpa kanten på PostgreSQL fulltextsökningen i ett verkligt produktionssystem. Jag ska gå igenom de specifika misslyckanden jag träffade, varför de hände, och de praktiska lösningarna som behövs för att få sökningen att bete sig så som användaren redan förväntar sig. **Att bygga på tidigare arbete**: Detta artikel förlänger [implementering av semantiska sökningar](/blog/semantic-search-in-action) som lagt till hybridsökningar med Reciprocal Rank Fusion (RRF). Den tidigare artikeln ombjöd grunden | - | att kombinera PostgreSQLs fulltextsökning |- | textsökning med Qdrant-vektorsökning ♫ . | Denna artikel löser de marginalfall som implementeringen misslyckats med Den semantiska sökinfrastrukturen tjänar två syften: :, den driver sökningen *och* förser dragningslayret för [Avrättare GPT](/blog/building-a-lawyer-gpt-for-your-blog-part1) RAG-systemet - en skriftassistent som utvecklar nya jobb med hjälp av hemsidan ' den existerande innehållet som kunskapsbasen [Att bygga en "Lawyer GPT" – Del 3](/blog/building-a-lawyer-gpt-for-your-blog-part3) på inbäddar och vektorsökningar. ## Problemet ### 1. Vacka resultat visade slumpmässiga artiklar När en sökning visade inga resultat, visade systemet slumpmässiga gamla artiklar istället för hjälpsamma förslag . Detta överträdde principet att minsta överraskning - användaren förväntade sig antingen relevanta resultat eller en tydlig | " inget match hittade |" meddelande med nyliga Beiträge som förslag | МSK5 Det gjorde det se ut som om sökningen hade fungerat **Fixen**: Modifierade `BlogSearchService.HybridSearchWithPagingAsync()` för att upptäcka när en sökning ger noll resultat och återvänder till att visa de senaste Beiträgena som är ordenade efter datum som faller `NoMatchFound` flagga till `BasePagingModel` så att gränssnittet kan visa ett lämpligt meddelande som "Ingen match gevind. Betydde du en av dessa? ```csharp // No match found - return recent posts as suggestions if (noMatchFound) { Log.Logger.Information("No search results for '{Query}', returning recent posts as suggestions", query); return await GetRecentPostsAsSuggestions(targetLanguage, startDate, endDate, page, pageSize); } ``` ### 2. Akronymer som "DiSEM SK2 Weren' matchar inte PostgreSQLs standardinriktade engelska textsökningskonfiguration indexerar akronymer tekniskt sett, men normalisering och utgång gör korta, ,, fall, -, betydelsefulla termer tillförlitliga i praktiken.. När du söker efter ", DiSE och ", blir det nedkalkat till ", dise och M SK8, så kan engelska ordboken slänga bort det eller påskilja det med låg vikt. **Fixen**: Läggde till akronym-detektorn | ( | terminer | ≤6 | uppskriftiga bokstäver |) | och fall | - | okänslig substring fallback med hjälp av PostgreSQL `ILIKE`. Det här kompletterar fulltextsökningen, snarare än att ersätta den. ```csharp // Detect if query looks like an acronym or short term var isAcronymLike = query.Length <= 6 && query.Any(char.IsUpper); // Add substring search for acronyms searchQuery = searchQuery.Where(x => EF.Functions.ILike(x.Title, $"%{acronym}%") || EF.Functions.ILike(x.PlainTextContent, $"%{acronym}%")); ``` ### 3. Technische termer med särskilda tecken Breke sök sökandet efter "ASPM SK1NET" eller "CMSC4 skulle misslyckas eftersom PostgreSQLs textsucher analyserar perioder och hash-symboler som begränsare. **Fixen**: skapade en `SearchQueryParser` som känner igen vanliga tekniska termer och ersätter dem med sökbara versioner: ```csharp private static readonly Dictionary TechnicalTerms = new(StringComparer.OrdinalIgnoreCase) { ["asp.net"] = "aspnet", ["c#"] = "csharp", [".net"] = "dotnet", ["f#"] = "fsharp", ["node.js"] = "nodejs", // ... more terms }; ``` ### 4. Frågor som "ASP.NET och AlpineM SK3 fungerade inte PostgreSQL behandlar " och " som ett stoppord och tar bort dem från sökningar. **Fixen**: Implementerade en Google- -query-parser som intelligent hanterar stop words och stödjer avancerade sökoperatörer ## Lösningen ### Google-Style sökoperatörer Jag implementerade `SearchQueryParser` klass som analyserar frågor med stöd för: 1. **Citta fraser**: `"exact match"` - söker efter den exakta frasen 2. **uteslutna villkor**: `-unwanted` - utelämnar resultat som innehåller det här begreppet 3. **Wildkort**: `ASP*` matchar 4. **Teknika villkor**: Automatiskt hanterar Parsaren använder ett kompilerat regex-mönster för att tokenisera queryn: ```csharp [GeneratedRegex(@"""([^""]+)""|(-)?(\S+)", RegexOptions.Compiled)] private static partial Regex QueryTokenRegex(); public ParsedQuery Parse(string query) { var matches = QueryTokenRegex().Matches(processedQuery); foreach (Match match in matches) { // Quoted phrase if (match.Groups[1].Success) { var phrase = match.Groups[1].Value.Trim(); result.Phrases.Add(phrase); continue; } // Excluded term (starts with -) if (match.Groups[2].Success) { var term = match.Groups[3].Value.Trim(); result.ExcludeTerms.Add(term.ToLowerInvariant()); continue; } // Regular term or wildcard var token = match.Groups[3].Value.Trim(); if (token.Contains('*')) { result.WildcardTerms.Add(token.Replace("*", "")); } else if (!StopWords.Contains(token)) { result.IncludeTerms.Add(token.ToLowerInvariant()); } } } ``` ### Att bygga PostgreSQL tsquery Det parser ut strukturerade data som konverteras sedan till PostgreSQL `to_tsquery` eftersom vi genererar strukturerad syntax - inte `websearch_to_tsquery` eftersom vi- -have redan analyserat själva operatorerna- -som ger oss större kontroll över hur villkoren kombineras. ```csharp public string BuildTsQuery(ParsedQuery parsed) { var queryParts = new List(); // Add include terms with AND foreach (var term in parsed.IncludeTerms) { queryParts.Add(term); } // Add wildcard terms with :* suffix foreach (var term in parsed.WildcardTerms) { queryParts.Add($"{term}:*"); } return queryParts.Count > 0 ? string.Join(" & ", queryParts) : string.Empty; } ``` > **Varför inte bara använda `websearch_to_tsquery`?** > Eftersom det fortfarande misslyckas med tekniska termer, akronymer , och domain- specifik syntax M SK3 och inte erbjuder några anhängningar för hybrid ranking eller fallbacksMska4 Genom att analysera oss självaMske5 kan vi hantera dessa marginalfall innan de når PostgreSQL ### Förstärkt BuildSearchQuery Den `BuildSearchQuery` metoden var helt omskriven för att använda den analyserade frågeställningsstrukturen `baseQuery` already filters by language, datumslängd , och synlighet - viM SK3tillhandahåller sökningarMska4 specifika villkor på toppenM Ska5 ```csharp private IOrderedQueryable BuildSearchQuery( string query, string language, DateTime? startDate, DateTime? endDate, string order) { var parsed = _queryParser.Parse(query); IQueryable searchQuery = baseQuery; // Handle phrases (exact substring matching) foreach (var phrase in parsed.Phrases) { searchQuery = searchQuery.Where(x => EF.Functions.ILike(x.Title, $"%{phrase}%") || EF.Functions.ILike(x.PlainTextContent, $"%{phrase}%") || x.Categories.Any(c => EF.Functions.ILike(c.Name, $"%{phrase}%"))); } // Build tsquery for include terms and wildcards var tsQuery = _queryParser.BuildTsQuery(parsed); // Apply full-text search if we have terms if (!string.IsNullOrWhiteSpace(tsQuery)) { searchQuery = searchQuery.Where(x => x.SearchVector.Matches(EF.Functions.ToTsQuery("english", tsQuery)) || x.Categories.Any(c => EF.Functions.ToTsVector("english", c.Name) .Matches(EF.Functions.ToTsQuery("english", tsQuery)))); } // Handle acronyms with case-insensitive substring search // This supplements full-text search (additive OR), not replaces it var acronymTerms = parsed.IncludeTerms .Concat(parsed.WildcardTerms) .Where(t => _queryParser.IsAcronymLike(t)) .ToList(); foreach (var acronym in acronymTerms) { searchQuery = searchQuery.Where(x => EF.Functions.ILike(x.Title, $"%{acronym}%") || EF.Functions.ILike(x.PlainTextContent, $"%{acronym}%")); } // Handle excluded terms (must NOT contain these) foreach (var excludeTerm in parsed.ExcludeTerms) { searchQuery = searchQuery.Where(x => !EF.Functions.ILike(x.Title, $"%{excludeTerm}%") && !EF.Functions.ILike(x.PlainTextContent, $"%{excludeTerm}%") && !x.Categories.Any(c => EF.Functions.ILike(c.Name, $"%{excludeTerm}%"))); } return orderedQuery; } ``` ## sök exempel Här är några exempel på den förbättrade sökfunktionen ### Det grundläggande sökandet ``` DiSE ``` ✅ Matchar nu artiklar med "DiSE" i titeln eller innehållet ### Teknika villkor ``` ASP.NET ``` ✅ Hittar artiklar om ASP.NET ( omvandlas automatiskt till SSK3aspnet ### Phrasesökning ``` "semantic search" ``` ✅ hittar exakta fraser "semantiskt sökandeM SK2 i artiklar ### uteslutna villkor ``` ASP.NET -Core ``` ✅ hittar ASP-, .- och NET-artiklar men utelämnar de som nämner "-, Core- och "-artiklar ### Wildkort ``` ASP* ``` ✅ Matchar "ASP", "A SPNETM SK4 | | ### komplexa frågor ``` "full text search" PostgreSQL -MySQL ``` ✅ Hittar artiklar med exakta fraser " full text search " Och nämningar om PostgreSQL M SK3 men utom artiklar som nämner MySQL ### För och efter exempel **Fråga**: `ASP.NET and Alpine` **Innan** (brokenM SK1 - "ASP - " och " togs bort som slutord - Resultat: Löser bara efter "AlpineM SK2 **Efter** (fixed - "ASP - " och" intelligent bevarade som en del av användarbeteendet - "AlpineM SK1 söktes normalt - Resultat: Hittar artiklar om både ASP .NET och Alpine ## RRF-Rankingintegrering sökandet integreras med Reciprocal Rank Fusion (RRF) som implementerat i [tidigare semantisk sökartikel](/blog/semantic-search-in-action#hybrid-search-with-reciprocal-rank-fusion). RRF används här för *Rangering*, inte återhämta ♫ ♫ - ♫ det kombinerar redan ♫ ```csharp // Fuse using RRF with category/freshness boosts var fusedDtos = _ranker.FuseResults(bm25Results, vectorResults, query); ``` RRF-algoritmen använder `1/(k+rank)` där k=60 för att kombinera resultat från flera källor , sätter sedan upphöjningar på - **Kategorismatch**: +2.0 - **Titelmatch**: +1.0 - **Frihet**: Exponentiell nedgång över 1 år (+1.5 max För den kompletta RRF-implementationen och hur hybridsökningar fungerar, se [Semantiska sökningar i handling](/blog/semantic-search-in-action). För djupare dykar in i inbäddar och vektors likhet [Att bygga en "Lawyer GPT" - Del 3](/blog/building-a-lawyer-gpt-for-your-blog-part3). Samma infrastruktur förstärker både användaren - att göra sökningar och att få fram RAG för AI ## Teknik arkitektur ### beroende injection De nya komponenterna är registrerade som tjänster: ```csharp services.AddSingleton(); services.AddSingleton(); services.AddScoped(); ``` `SearchQueryParser` och `SearchRanker` är singletoner eftersom de' är statlösa , trådar M SK2 säkra , och kan användas om och om igen i alla applikationer `BlogSearchService` är skalad eftersom den går in i databasens kontext. ### Frågaflödet 1. **Användaren inser en sökfråga** → sökkontroll 2. **Försöksanalys** → SearchQueryParser bryter ner sig i strukturerade komponenter 3. **Semantiska sökningar ( om aktiverat)** → Qdrant-vektordatabas med ONNX-bäddar 4. **PostgreSQL full -textsökning (ganär tillgängliga)** → BM25-baserade nyckelmatchning 5. **RRF fusion** → Kombinerar båda resultatuppsättningarna med kategorinM SK1frödhetsförhöjningar 6. **Inga resultat** → Ge tillbaka de senaste Beiträgen som förslag Samma semantiska sökkomponenter används också av [Avrättare GPT](/blog/building-a-lawyer-gpt-for-your-blog-part1) system för att hämta relevanta tidigare bloggposter för AI- hjälpskrivande. Se [Del 4](/blog/building-a-lawyer-gpt-for-your-blog-part4) för detaljer om intagningssledning. ### Databaseringsschema sökandet är beroende av ett förutberäknat `SearchVector` i kolumnen `BlogPosts` Tabella: ```sql ALTER TABLE mostlylucid."BlogPosts" ADD COLUMN "SearchVector" tsvector GENERATED ALWAYS AS ( to_tsvector('english', coalesce("Title", '') || ' ' || coalesce("PlainTextContent", '') ) ) STORED; CREATE INDEX idx_blog_posts_search_vector ON mostlylucid."BlogPosts" USING GIN ("SearchVector"); ``` GIN (Allgemeeniserad omvänd index) ger en snabb fulltextsökning över stora textkorpor ## Tillvägagångspunkter ### Varför ILIKE för akronymer? Medan `ILIKE` (caseM SK1insensitiv LIKE) är långsammare än fullaMska3textsökningenM Ska4 det Mska5 är nödvändigt för akronymer eftersom 1. Full- normaliserar textsökningen / standardiserar termer , bryter korta överhuvudtaget strängar 2. Akronymer är vanligtvis korta, (≤6 tecken ), som begränsar effekten 3. Bedingelsen är bara tilldanad när den behövs (identifiserade akronymer) Den här metoden skulle inte skalas till en godtycklig substringssökning över miljoner rader - men för riktade akronymfallbackar så är den ' den lämpliga ### PostgreSQL-beläggningsdensitetsklassificering (`ts_rank_cd`) PostgreSQL erbjuder två klassificeringsfunktioner för fulla sökningar - `ts_rank`: Grundbegreppsfrekvens ( hur många gånger uppträder begreppen - `ts_rank_cd`: Ranging för ytdensitet ( hur nära termerna ser ut tillsammans Vi använder `ts_rank_cd` eftersom det förser **relevans som BM25-** genom att räkna ut termisk närhet: ```csharp // Order by cover density ranking - rewards term proximity orderedQuery = searchQuery.OrderByDescending(x => x.SearchVector.RankCoverDensity(EF.Functions.ToTsQuery("english", tsQuery))); ``` **Varför? `ts_rank_cd` är bättre:** | Metrik | `ts_rank` | `ts_rank_cd` | |--------|-----------|--------------| | **Algoritm** | Tydlig frekvens | Omfångsdensitet | ( | Ungefär |) | | **Multi-ord sökningar** | räknar villkoren separat | Belöningskapsklauseln som uppträder tillsammans | **exempel: "dockerbehållare "** | Artikel med | " | docker | " | МSK3 | gånger hög poäng || | Artikel med | **Förmågor** | Straft | Marginellt långsammare | , | lever fortfarande GIN-index || | **Vanor** | Enkel räknaning | Ungefär BMM SK2 | Det här är en **snabb vinnande optimering** - bättre relevans-Ranking med noll tillämpning -nivåberäkningar . Allt ranking sker i PostgreSQL med hjälp av det existerande GIN-indexet på `SearchVector`. **Referenser:** - [PostgreSQL ts_rank_cd dokumentation](https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-RANKING) - [EF Core RankCoverDensity](https://learn.microsoft.com/en-us/ef/core/providers/postgres/misc#full-text-search) ### Aditionella Performanceoptimeringar Förutom att fixa sökfunktionaliteten, flera viktiga optimeringar förbättrar prestationen : #### 1. Language Caching Möjligheterna till språk förändras sällan men söktes i varje sökansökning.. Cachérats nu med dubbelt. ```csharp private static readonly TimeSpan LanguageCacheDuration = TimeSpan.FromHours(1); private static List? _cachedLanguages; private static DateTime _languageCacheExpiry = DateTime.MinValue; private static readonly SemaphoreSlim _cacheLock = new(1, 1); ``` **Intryck**: Löser bort 1 database-frågan per sökanfrage #### 2. Batched ILIKE queries Den ursprungliga koden som användes `foreach` loops som skapar flera varelseklagor. Nu knutna till ensamma uttryck ```csharp // BEFORE: Multiple WHERE clauses foreach (var acronym in acronymTerms) { searchQuery = searchQuery.Where(x => EF.Functions.ILike(x.Title, $"%{acronym}%")); } // AFTER: Single batched WHERE if (acronymTerms.Count > 0) { searchQuery = searchQuery.Where(x => acronymTerms.Any(acronym => EF.Functions.ILike(x.Title, $"%{acronym}%"))); } ``` **Intryck**: renare SQL, ~5-10% snabbare för flera sökningar #### 3. Omfattningsindex för vanliga frågor Hinsatt delindex med INCLUDE- kolumner för vanliga tillgångsmönster: ```sql CREATE INDEX idx_blog_posts_search_covering ON mostlylucid."BlogPosts" ("LanguageId", "IsHidden", "ScheduledPublishDate") INCLUDE ("Id", "Slug", "Title", "PublishedDate") WHERE "IsHidden" = false; ``` **Intryck**: Aktiverar [index- endast skanner](https://www.postgresql.org/docs/current/indexes-index-only-scans.html) - PostgreSQL behöver inte accessera tabellhalvan #### 4. Removed Unnecessary Includes EF Core's `Include()` ladda fulla navigeringsenheter. Removed where only navigation properties used in WHERE clauses: ```csharp // BEFORE: Loads full LanguageEntity into memory .Include(x => x.LanguageEntity) .Where(x => x.LanguageEntity.Name == "en") // AFTER: EF translates navigation property without loading entity .Where(x => x.LanguageEntity.Name == "en") ``` **Intryck**: ~5-10% minnesförsämring, mindre data överförd från databasenM SK3 **Referenser:** - [PostgreSQL-index](https://www.postgresql.org/docs/current/indexes.html) - [EF: s grundresultat](https://learn.microsoft.com/en-us/ef/core/performance/) ### En frågaoptimeringsstrategi sökningar bygger var clauser stegvis används [EF Core's uttryckträdskomposition](https://learn.microsoft.com/en-us/ef/core/querying/): ```csharp IQueryable searchQuery = baseQuery; // Each filter added conditionally - PostgreSQL optimizes the final query if (parsed.Phrases.Count > 0) { searchQuery = searchQuery.Where(...); } if (!string.IsNullOrWhiteSpace(tsQuery)) { searchQuery = searchQuery.Where(...); } if (acronymTerms.Count > 0) { searchQuery = searchQuery.Where(...); } ``` Detta genererar en **en enda optimerad SQL-fråga** snarare än flera omloppsresor. PostgreSQL's sökplanerare kan använda statistik och index effektivt när de ser den kompletta "Var" -klaxen ## Executive Summary Cumulativ påverkan av alla optimeringar: | Optimering ♫ ♫ | ♫ Latency Impact ♫ |-------------|----------------|----------------| | Sprachens kassering | Minimalt ♫ ♫ | ♫ | Ta bort Inbegripa() | | | -5-10% \ | | Mindre dataöverföring | Batch ILIKE | Omslagsindex | -20-30% | | | Index | - | endast skanningar || | tsM SK1rank_cd | Samma | bättre relevans | Cachefix (route params) | NM SK4A **Total förväntad förbättring**: **30-50% snabbare sök** med signifikant minskad datamängd. ## Lektioner som lärts 1. **Don' inte anta att fulla-textsökningen hanterar allt**: Edge cases like acronyms and special characters need special handling 2. **Kombinera flera angrepp**: FullM SK1textsökning (BM25) ♫ ♫ + semantisksökning ♫ 3. **Google-formade användares förväntningar**: Att stödja citera fraser 4. **Ge hjälpsamma bakslag**: När sökandet misslyckas , gör inget ' inte visa någonting M SK3 visar de senaste Beiträgen och gör det tydligt att inga matchar hittades 5. **Parse , don't hack**: En korrekt fråga-parser är renare och mer underhållningsbar än en rad strängmanipulationer 6. **Leverage PostgreSQL**: Använd `ts_rank_cd` istället för `ts_rank` för BM25- liknande relevans 7. **Profil före optimering**: De bottlenecks som fanns i ", ", FTS-testning och ), var inte det verkliga problemet. 8. **Batchdatabas-operationer**: Flera `foreach` loops som skapar separata var clauser genererar suboptimal SQL. Använd `Any()` eller `All()` att byta till ett enda uttryck. ## Framtidens förbättringar Förhoppningsbara förbättringar för framtiden **Uppfinning för förbättringar**: - **Fuzzy matchning**: Levenshtein avstånd för typotolerans - **Synonymexpansion**: "bloggpost **Förstärkningar för att analysera**: - **Kategorisfiltering**: `category:ASP.NET` -operatör - **Datumslängd**: `after:2025-01-01` -operatör **Rangeringsbestämningar**: - **Result highlighting**: Vertoon matchade textdelar i resultat - **Click- genom att spåra**: Lär dig av användarbeteenden för att förbättra rankingen **Observerbarhet**: - **sökanalys**: Lyssna efter vanliga frågor och misslyckade sökningar för att identifiera luckor ## Slutsatsen Byggnaderproduktionen-sortssökning kräver fixande kanter **och** optimering av prestationer. Den här artikeln omfattade både: Fixering of PostgreSQL fullM SK2 text search edge cases | ( | acronyms |, | tekniska termer | , | Google |- | stiloperatörer ♫ ) | och implementering of key performance optimizations `ts_rank_cd`). Implementering uppnådde **30-50% snabbare sök** medan datamängden minskar genom: - Densitetsgradering (`ts_rank_cd`) för bättre relevans - Språkkassering som eliminerar upprepade insökningar - Batched ILIKE-expressioner för renare SQL - Att täcka index som gör det möjligt att scanna index - Avlägsna onödiga EF Core inkluderar Den viktigaste insikten: ingen enskild metod hanterar alla fall . PostgreSQL FTS är duktig på att matcha nyckelordar , semantiskt sökande hanterar konceptuella frågor M SK3 och riktade fallbacks fångar randprov i fallet, . Den rena paringslayern knyter ihop det,, hanterar operatörer och tekniska termer innan de når sökmotorerna, Den semantiska sökinfrastrukturen tjänar två ändamål: - **Användare- riktar sig mot sökandet**: Kombinerar nyckel och semantisk matchning för bättre resultat - **RAG-avsökning**: Förstärker [Avrättare GPT](/blog/building-a-lawyer-gpt-for-your-blog-part1) skrivassistent **Related articles:** - [Semantiska sökningar i handling](/blog/semantic-search-in-action) - Foundation : hybridsökning med RRF - [Att bygga en "Lawyer GPT" - Del 3](/blog/building-a-lawyer-gpt-for-your-blog-part3) - Embeddings & vektordatabaser - [Att bygga en "Lawyer GPT" - Del 4](/blog/building-a-lawyer-gpt-for-your-blog-part4) - Ingestion-torn **officiella dokumentation:** - [PostgreSQL Full-Textsökning](https://www.postgresql.org/docs/current/textsearch.html) - [PostgreSQL-index](https://www.postgresql.org/docs/current/indexes.html) - [EF: s grundresultat](https://learn.microsoft.com/en-us/ef/core/performance/) - [EF Core Npgsql-leverantör](https://www.npgsql.org/efcore/index.html) Alla koder tillgängliga i [blog's GitHub-repository](https://github.com/scottgal/mostlylucidweb).