PostgreSQL LIKE查询与C#本地遍历的性能对比及最佳实践问询

Let's break down your questions one by one—there's a lot of good stuff to unpack here!

1. Is filtering in C# by traversing the List better than SQL filtering? #

Short answer: Almost always, no—unless your dataset is tiny (like a few hundred records max). Here's why:

• Network overhead: If you pull all records from the database first, you're sending way more data over the network than necessary. Even if your database is on the same server, this adds unnecessary load.
• Database optimization: Databases are built for querying and filtering data efficiently. PostgreSQL's query planner can leverage indexes (when set up correctly) to avoid full table scans, which is way faster than pulling everything and filtering in C#.
• Scalability: If your Table1 grows to tens of thousands or millions of records, local filtering will become painfully slow. Database-side filtering scales much better.

2. What factors affect the performance of your current SQL query? #

Your query select * FROM Table1 WHERE concat(Table1.Name::text, Table1.Surname::text) LIKE '%ar%' has a few key performance bottlenecks, and several factors will make it faster or slower:

• Dataset size: The more rows in Table1, the slower this query will be—because it has to do a full table scan (no index can be used here by default). Every row needs to have Name and Surname concatenated, then checked against the LIKE pattern.
• Missing indexes: Since you're filtering on a concatenated expression, PostgreSQL can't use standard indexes on Name or Surname alone. Without a functional index or trigram index (more on that later), it's stuck scanning every row.
• Data diversity: If a large percentage of rows match the LIKE pattern (e.g., input "ar" returns all records), the database has to fetch and return more data—this increases network transfer time and processing. Conversely, if only a small subset matches, the scan is still full, but the result set is smaller.
• Type conversion overhead: Converting Name and Surname to text (if they're already varchar or text types) adds unnecessary processing. If those columns are already text-based, you can drop the ::text cast to save a tiny bit of overhead.

3. Which solution is more appropriate? #

It depends on your data size and long-term needs, but here's the recommended approach:

• For small datasets (≤1k rows): Either approach works—local filtering might be quicker to code, but it's not a big performance difference.
• For any dataset that could grow: Stick with database-side filtering, but optimize your query and add indexes to speed it up:
  1. Add a trigram index (using PostgreSQL's pg_trgm extension) for fast fuzzy matching. First enable the extension:

     CREATE EXTENSION IF NOT EXISTS pg_trgm;
     

     Then create an index on the concatenated full name:

     CREATE INDEX idx_table1_fullname_trgm ON Table1 USING GIN (concat(Name, Surname) gin_trgm_ops);
     

     This index will drastically speed up LIKE '%ar%' queries by avoiding full table scans.
  2. Avoid unnecessary casts: If Name and Surname are already text or varchar, remove the ::text cast to simplify the query.

4. Is using LIKE '%%' for no filtering a problem? #

Absolutely—this is a bad practice. Here's why:

• Unnecessary processing: The database still has to check every row to see if the concatenated string matches %%, which is a waste of resources.
• Full table scan: Even if you had an index, LIKE '%%' would ignore it and do a full scan anyway.
• Worse performance than a simple SELECT *: Just use SELECT * FROM Table1 instead—it's faster because the database doesn't have to evaluate the LIKE condition for every row, and it can return data directly without extra checks.

内容的提问来源于stack exchange，提问作者Kuczi