Why filtering behavior matters in PostgreSQL - Why It Works This Way

Overview - Why filtering behavior matters

What is it?

Filtering in databases means selecting only the rows that meet certain conditions from a table. It helps you focus on the data you really need instead of looking at everything. This makes queries faster and results clearer. Filtering is done using commands like WHERE in SQL.

Why it matters

Without filtering, you would get all the data every time, which can be slow and confusing, especially with large databases. Filtering helps save time and resources by showing only relevant information. It also helps avoid mistakes by excluding unwanted data from analysis or reports.

Where it fits

Before learning filtering, you should understand basic SQL queries and how tables store data. After mastering filtering, you can learn about sorting, joining tables, and advanced query optimization.

Mental Model

Core Idea

Filtering is like using a sieve to pick only the grains you want from a big pile of mixed seeds.

Think of it like...

Imagine you have a basket full of different fruits. Filtering is like picking only the apples out of the basket to make an apple pie.

Table: Fruits Basket
┌─────────┬───────────┐
│ Fruit   │ Color     │
├─────────┼───────────┤
│ Apple   │ Red       │
│ Banana  │ Yellow    │
│ Apple   │ Green     │
│ Orange  │ Orange    │
└─────────┴───────────┘

Filter: Fruit = 'Apple'
Result:
┌─────────┬───────────┐
│ Fruit   │ Color     │
├─────────┼───────────┤
│ Apple   │ Red       │
│ Apple   │ Green     │
└─────────┴───────────┘

Build-Up - 7 Steps

1

Foundation

Basic filtering with WHERE clause

Concept: Learn how to use the WHERE clause to select rows that meet a simple condition.

In PostgreSQL, you use the WHERE keyword to filter rows. For example, to get all employees from the 'Sales' department: SELECT * FROM employees WHERE department = 'Sales';

Result

Only rows where the department is 'Sales' are returned.

Understanding the WHERE clause is the first step to controlling which data you see, making queries more useful and efficient.

2

Foundation

Filtering with multiple conditions

Concept: Combine conditions using AND and OR to filter data more precisely.

You can filter rows that meet multiple criteria. For example, to find employees in 'Sales' who earn more than 50000: SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;

Result

Only employees in Sales with salary above 50000 are shown.

Combining conditions lets you narrow down data to exactly what you need, avoiding irrelevant results.

3

Intermediate

Using comparison operators in filters

🤔Before reading on: do you think filtering with > and < works only on numbers or also on dates and text? Commit to your answer.

Concept: Learn that filtering can use operators like >, <, >=, <= on numbers, dates, and even text.

You can filter by comparing values. For example, to find orders after January 1, 2023: SELECT * FROM orders WHERE order_date > '2023-01-01'; Or to find names alphabetically after 'M': SELECT * FROM customers WHERE name > 'M';

Result

Rows with order dates after Jan 1, 2023 or names after 'M' alphabetically are returned.

Knowing that comparison operators work on different data types expands your ability to filter data effectively.

4

Intermediate

Filtering with NULL values

🤔Before reading on: do you think WHERE column = NULL works to find missing values? Commit to your answer.

Concept: Understand that NULL means unknown or missing data and requires special filtering syntax.

To find rows where a column has no value (NULL), use IS NULL or IS NOT NULL: SELECT * FROM users WHERE last_login IS NULL; This finds users who never logged in.

Result

Rows with NULL in last_login are returned.

Recognizing that NULL is not equal to anything, even NULL, prevents common filtering mistakes.

5

Intermediate

Filtering with pattern matching

Concept: Use LIKE and ILIKE to filter text by patterns, such as starting with or containing certain letters.

To find customers whose names start with 'A': SELECT * FROM customers WHERE name LIKE 'A%'; ILIKE works like LIKE but ignores case: SELECT * FROM customers WHERE name ILIKE '%smith%';

Result

Rows matching the text pattern are returned.

Pattern matching lets you filter text flexibly, useful when exact matches are too strict.

6

Advanced

Filtering with subqueries

🤔Before reading on: do you think subqueries in WHERE run once or for every row? Commit to your answer.

Concept: Use subqueries inside WHERE to filter based on results from another query.

Example: Find employees who work in departments with more than 10 people: SELECT * FROM employees WHERE department_id IN ( SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 10 );

Result

Employees in large departments are returned.

Using subqueries in filters allows complex conditions based on aggregated or related data.

7

Expert

Filtering impact on query performance

🤔Before reading on: do you think filtering early or late in query execution affects speed? Commit to your answer.

Concept: Filtering early reduces data processed later, improving query speed and resource use.

PostgreSQL uses indexes to speed up filtering. Writing filters that use indexed columns helps the database find rows faster. Poorly written filters can cause full table scans, slowing queries. Example: -- Good: uses index on 'department' SELECT * FROM employees WHERE department = 'Sales'; -- Bad: function on column disables index SELECT * FROM employees WHERE LOWER(department) = 'sales';

Result

Queries with good filters run faster and use less CPU and memory.

Understanding how filtering affects performance helps write efficient queries that scale well.

Under the Hood

When you run a query with a filter, PostgreSQL scans the table or index to check each row against the filter condition. If the condition is true, the row is included in the result. Indexes allow PostgreSQL to jump directly to matching rows without scanning the whole table. The query planner decides the best way to apply filters for speed.

Why designed this way?

Filtering was designed to let users quickly find relevant data without loading everything. Early databases scanned entire tables, which was slow. Adding filtering and indexes improved speed and usability. The design balances flexibility (any condition) with performance (using indexes).

Query Execution Flow
┌───────────────┐
│ SQL Query     │
│ with WHERE    │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Query Planner │
│ decides plan  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Index Scan or │
│ Table Scan    │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Filter rows   │
│ by condition  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Return result │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does WHERE column = NULL find rows with missing values? Commit yes or no.

Common Belief: Using WHERE column = NULL will find rows where the column is empty or missing.

Reality: WHERE column = NULL never returns rows because NULL means unknown and cannot be compared with =. You must use IS NULL to find missing values.

Why it matters: Using = NULL causes queries to return no results unexpectedly, leading to wrong data analysis or bugs.

Quick: Does filtering with LIKE '%text%' always use indexes? Commit yes or no.

Common Belief: LIKE with wildcards always uses indexes to speed up queries.

Reality: LIKE with a leading wildcard (e.g., '%text') cannot use normal indexes efficiently, causing slow full scans.

Why it matters: Assuming indexes always help can cause performance problems when filtering with patterns.

Quick: Does filtering on computed columns use indexes automatically? Commit yes or no.

Common Belief: Filtering on expressions like LOWER(column) uses indexes just like filtering on columns.

Reality: Functions on columns disable normal indexes unless special expression indexes are created.

Why it matters: Not knowing this leads to slow queries and wasted resources.

Quick: Does filtering with OR conditions always run faster than multiple queries? Commit yes or no.

Common Belief: Using OR in filters is always efficient and faster than separate queries.

Reality: OR conditions can prevent index use and slow queries; sometimes UNION of separate queries is faster.

Why it matters: Blindly using OR can degrade performance in large datasets.

Expert Zone

1

Filtering on indexed columns is fast, but combining many conditions can confuse the planner, requiring manual tuning.

2

PostgreSQL's planner uses statistics to decide filter order; outdated stats can cause poor plans.

3

Partial indexes allow filtering only a subset of data, optimizing queries that filter on common conditions.

When NOT to use

Filtering is not suitable when you need to process all data regardless of conditions, such as full data exports or bulk updates. In those cases, avoid WHERE clauses or use batch processing. Also, for complex filtering logic, consider materialized views or precomputed tables.

Production Patterns

In production, filtering is combined with indexing strategies and query tuning to handle large datasets efficiently. Developers use EXPLAIN ANALYZE to check filter performance and rewrite queries for speed. Filtering is also used in API endpoints to return only requested data, improving user experience.

Connections

Set Theory

Filtering corresponds to selecting subsets from a larger set based on conditions.

Understanding filtering as subset selection helps grasp how queries narrow down data logically.

Search Algorithms

Filtering uses search principles to find matching data quickly, similar to searching in sorted lists.

Knowing search algorithms clarifies why indexes speed up filtering by reducing search space.

Human Attention Filtering

Filtering in databases is like how humans focus on important information and ignore distractions.

Recognizing this parallel helps appreciate filtering as a way to manage information overload.

Common Pitfalls

#1 Using = NULL to find missing values.

Wrong approach:

SELECT * FROM users WHERE last_login = NULL;

Correct approach:

SELECT * FROM users WHERE last_login IS NULL;

Root cause:

Misunderstanding that NULL is not a value but a marker for unknown, so = comparison fails.

#2 Using LIKE with leading wildcard expecting fast queries.

Wrong approach:

SELECT * FROM products WHERE name LIKE '%phone';

Correct approach:

Use full-text search or trigram indexes for such patterns, or avoid leading wildcard:
SELECT * FROM products WHERE name LIKE 'phone%';

Root cause:

Not knowing that leading wildcards disable normal index use, causing slow scans.

#3 Filtering on functions without expression indexes.

Wrong approach:

SELECT * FROM employees WHERE LOWER(name) = 'alice';

Correct approach:

Create an index on LOWER(name) first:
CREATE INDEX idx_lower_name ON employees (LOWER(name));
Then run the query.

Root cause:

Assuming indexes work automatically on computed expressions.

Key Takeaways

Filtering lets you select only the data you need, making queries faster and results clearer.

The WHERE clause is the main tool for filtering, supporting simple and complex conditions.

NULL values require special handling with IS NULL or IS NOT NULL in filters.

Filtering affects query performance deeply; using indexes and writing efficient conditions is key.

Misunderstanding filtering behavior causes bugs and slow queries, so mastering it is essential for working with databases.