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.

Practice

1. What is the main purpose of the WHERE clause in a PostgreSQL query?

easy

A. To create a new table

B. To sort the results alphabetically

C. To join two tables together

D. To filter rows based on a condition

Solution

1. Step 1: Understand the role of WHERE clause

   The WHERE clause is used to specify conditions that rows must meet to be included in the result.

2. Step 2: Compare options with WHERE purpose

   Sorting is done by ORDER BY, joining by JOIN, and creating tables by CREATE TABLE, so these are incorrect.

3. Final Answer:

   To filter rows based on a condition -> Option D

4. Quick Check:

   WHERE filters rows = A [OK]

Hint: WHERE filters rows to get only what you want [OK]

Common Mistakes:

• Confusing WHERE with ORDER BY
• Thinking WHERE creates tables
• Mixing WHERE with JOIN

2. Which of the following is the correct syntax to filter rows where the column age is greater than 30?

easy

A. SELECT * FROM users FILTER age > 30;

B. SELECT * FROM users WHERE age > 30;

C. SELECT * FROM users HAVING age > 30;

D. SELECT * FROM users ORDER BY age > 30;

Solution

1. Step 1: Identify correct filtering syntax

   The WHERE clause is used to filter rows based on conditions like age > 30.

2. Step 2: Eliminate incorrect clauses

   FILTER is not valid here, HAVING is for groups, ORDER BY sorts results, so only WHERE is correct.

3. Final Answer:

   SELECT * FROM users WHERE age > 30; -> Option B

4. Quick Check:

   WHERE filters rows correctly = A [OK]

Hint: Use WHERE for row conditions, HAVING for groups [OK]

Common Mistakes:

• Using HAVING without GROUP BY
• Using FILTER instead of WHERE
• Confusing ORDER BY with filtering

3. Given the table employees with columns id, name, and salary, what will this query return?

SELECT name FROM employees WHERE salary >= 50000;

medium

A. All employee names with salary equal or greater than 50000

B. Syntax error due to missing GROUP BY

C. All employee names regardless of salary

D. All employee names with salary less than 50000

Solution

1. Step 1: Analyze the WHERE condition

   The condition salary >= 50000 filters employees with salary 50000 or more.

2. Step 2: Understand SELECT output

   Only the name column is selected for those filtered employees.

3. Final Answer:

   All employee names with salary equal or greater than 50000 -> Option A

4. Quick Check:

   WHERE salary >= 50000 filters names = D [OK]

Hint: WHERE filters rows; SELECT chooses columns [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing >= with <
• Expecting GROUP BY needed here

4. Identify the error in this query:

SELECT * FROM orders WHERE status = 'shipped' AND;

medium

A. Incorrect use of WHERE instead of HAVING

B. Missing ORDER BY clause

C. Missing condition after AND

D. Using * instead of column names

Solution

1. Step 1: Check WHERE clause syntax

   The WHERE clause ends with AND but no condition follows, causing syntax error.

2. Step 2: Validate other parts

   HAVING is not needed here, ORDER BY is optional, and * is valid to select all columns.

3. Final Answer:

   Missing condition after AND -> Option C

4. Quick Check:

   AND must be followed by condition = B [OK]

Hint: AND must always have a condition after it [OK]

Common Mistakes:

• Leaving AND without condition
• Confusing WHERE and HAVING
• Thinking ORDER BY is mandatory

5. You want to find customers who placed orders with total amount over 100 but only if their account is active. Which query correctly filters this?

SELECT customer_id FROM orders WHERE total > 100 AND account_status = 'active';

hard

A. This query correctly filters customers with total > 100 and active accounts

B. The query should use HAVING instead of WHERE for filtering

C. The query needs GROUP BY customer_id to filter correctly

D. Filtering on account_status is invalid in WHERE clause

Solution

1. Step 1: Understand filtering conditions

   Both conditions total > 100 and account_status = 'active' filter rows correctly in WHERE clause.

2. Step 2: Check if GROUP BY or HAVING needed

   Since no aggregation is used, GROUP BY or HAVING is not required here.

3. Final Answer:

   This query correctly filters customers with total > 100 and active accounts -> Option A

4. Quick Check:

   WHERE filters multiple conditions correctly = C [OK]

Hint: Use WHERE for multiple conditions without aggregation [OK]

Common Mistakes:

• Using HAVING without aggregation
• Adding unnecessary GROUP BY
• Thinking account_status can't be filtered in WHERE