Test Your PostgreSQL Cardinality Knowledge with this Quiz

Progress

True / False

In PostgreSQL, cardinality refers to the number of rows in a table.

True False

Cardinality is a term used to describe the number of rows in a table.

True / False

In PostgreSQL, high cardinality means that a column has many unique values.

True False

High cardinality refers to a column containing a large number of unique values, such as a primary key or a unique identifier.

True / False

In PostgreSQL, low cardinality means that a column has few unique values.

True False

Low cardinality refers to a column with a small number of unique values, such as a boolean or a column with repeated categorical data.

True / False

In PostgreSQL, indexes on high cardinality columns are generally more effective than on low cardinality columns.

True False

Indexes are typically more effective on high cardinality columns because the uniqueness of the values makes it easier to quickly locate rows.

True / False

PostgreSQL uses cardinality information in the query planner to optimize query execution.

True False

The PostgreSQL query planner uses statistics, including cardinality, to choose the most efficient way to execute a query.

True / False

PostgreSQL’s ANALYZE command updates the statistics about the cardinality of columns in a table.

True False

The ANALYZE command collects statistics about the distribution of values in each column, which includes cardinality, and updates the query planner.

True / False

In PostgreSQL, high cardinality columns should always be indexed to improve query performance.

True False

While high cardinality columns often benefit from indexing, there are cases where indexing may not be beneficial or necessary depending on the query patterns and workload.

True / False

In PostgreSQL, composite indexes can be useful for queries involving multiple high cardinality columns.

True False

Composite indexes can improve performance for queries that filter or join on multiple high cardinality columns by reducing the search space.

True / False

PostgreSQL automatically adjusts query execution plans based on changes in cardinality without requiring manual intervention.

True False

PostgreSQL’s query planner dynamically adjusts execution plans based on updated statistics, including cardinality, to optimize performance.

Q10

True / False

PostgreSQL allows the use of expression indexes to handle cases where cardinality needs to be optimized for complex queries.

True False

PostgreSQL supports expression indexes, which allow indexing on expressions or functions of columns, providing flexibility in optimizing queries based on cardinality and other factors.

Q11

Single Choice

What does the term "cardinality" refer to in PostgreSQL?

The number of rows in a table The number of columns in a table The type of data in a columnThe indexing method used in a table

Cardinality in PostgreSQL refers to the number of rows in a table, which is an important aspect of understanding the structure and performance characteristics of a database.

Q12

Single Choice

In PostgreSQL, how can you determine the cardinality of a table?

SELECT COUNT(*) FROM table_name; SELECT * FROM table_name; SELECT DISTINCT * FROM table_name;SELECT COLUMN_NAME FROM table_name;

The COUNT(*) function in PostgreSQL is used to count the total number of rows in a table, effectively providing the table's cardinality.

Q13

Single Choice

Cardinality is crucial for which of the following PostgreSQL operations?

Index selection and optimization Creating views Assigning user privilegesRenaming tables

Cardinality is important in PostgreSQL for index selection and optimization, as it helps determine the most efficient way to retrieve data from large datasets.

Q14

Single Choice

Given the following PostgreSQL query, what does the COUNT(DISTINCT column_name) function return?

SQL Code

SELECT COUNT(DISTINCT column_name) FROM table_name;

The number of unique rows in the table The number of unique values in column_name The total number of rows in the tableThe sum of values in column_name

The COUNT(DISTINCT column_name) function in PostgreSQL returns the number of unique values in the specified column, which helps in understanding the column's cardinality.

Q15

Single Choice

How does high cardinality of a column impact PostgreSQL index performance?

It generally improves index performance It generally decreases index performance It has no impact on index performanceIt depends on the type of index used

High cardinality, where a column has a large number of unique values, generally improves index performance in PostgreSQL because the index can more effectively differentiate between rows.

Q16

Single Choice

In PostgreSQL, which SQL statement would you use to analyze the cardinality of a table's data distribution?

ANALYZE table_name; EXPLAIN SELECT * FROM table_name; VACUUM table_name;REINDEX table_name;

The ANALYZE command in PostgreSQL collects statistics about the distribution of data in tables, which includes cardinality. This helps the query planner make better decisions.

Q17

Single Choice

In PostgreSQL, what impact does cardinality have on the query planner's choice between a sequential scan and an index scan?

Higher cardinality columns are more likely to use an index scan Higher cardinality columns are more likely to use a sequential scan Cardinality has no impact on the query planner's decisionThe query planner always chooses a sequential scan for high cardinality columns

PostgreSQL's query planner uses cardinality to decide between a sequential scan and an index scan. High cardinality columns, with many unique values, are more likely to benefit from an index scan.

Q18

Single Choice

Which PostgreSQL index type is most beneficial for a column with low cardinality?

B-Tree Hash GiSTBRIN

BRIN (Block Range INdexes) in PostgreSQL are particularly efficient for low cardinality columns, where the data is not very unique, because they store summaries of blocks of data rather than indexing each individual row.

Q19

Single Choice

Consider the following PostgreSQL query:

SQL Code

SELECT COUNT(*) FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE region = 'North');
How does cardinality affect the performance of this query?

Higher cardinality of customer_id improves performance Lower cardinality of customer_id improves performance Cardinality has no effect on performanceLower cardinality of region improves performance

High cardinality in the customer_id column means there are many unique customers, which makes the IN subquery more selective and potentially improves the overall performance.

Q20

Single Choice

In a highly normalized PostgreSQL database, how can cardinality influence the decision to denormalize tables?

High cardinality often justifies denormalization for performance reasons Low cardinality requires denormalization to save space Cardinality is irrelevant in denormalization decisionsDenormalization is only considered for columns with medium cardinality

High cardinality in PostgreSQL may lead to denormalization to reduce the number of joins required in queries, thereby improving performance in certain situations.

Q21

Multiple Choice

What does cardinality refer to in the context of PostgreSQL databases?

SQL Code

Given two tables, 'students' and 'courses', with a many-to-many relationship, create an association table 'enrollments' to track the relationship.

CREATE TABLE students (
 student_id SERIAL PRIMARY KEY,
 student_name VARCHAR(100)
);

CREATE TABLE courses (
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR(100)
);

CREATE TABLE enrollments (
 student_id INT REFERENCES students(student_id),
 course_id INT REFERENCES courses(course_id),
 PRIMARY KEY (student_id, course_id)
);

The number of unique values in a column. The maximum number of rows in a table. The relationship between tables in terms of the number of occurrences.The data type of a column.

Cardinality in databases refers to the uniqueness of data values contained in a particular column (attribute) of a table. It often refers to the relationship between tables and the number of occurrences of one entity for a single occurrence of the related entity.

Q22

Multiple Choice

How would you describe high cardinality in a PostgreSQL database table?

SQL Code

Create a table 'orders' with a 'customer_id' that has high cardinality, meaning each 'customer_id' is unique.

CREATE TABLE customers (
 customer_id SERIAL PRIMARY KEY,
 customer_name VARCHAR(100)
);

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 order_date DATE,
 customer_id INT REFERENCES customers(customer_id)
);

INSERT INTO customers (customer_name) VALUES ('John Doe');
INSERT INTO orders (order_date, customer_id) VALUES ('2024-01-01', 1);

A column with a large number of unique values. A column with a small number of unique values. A column with a large number of NULL values.A column that is part of the primary key.

High cardinality refers to a column that contains a large number of unique values. For example, a 'customer_id' column where each customer has a unique ID represents high cardinality.

Q23

Multiple Choice

What impact does cardinality have on database performance in PostgreSQL?

SQL Code

Create an index on a column 'email' in a 'users' table, considering the impact of cardinality on performance.

CREATE TABLE users (
 user_id SERIAL PRIMARY KEY,
 email VARCHAR(100) UNIQUE,
 username VARCHAR(50)
);

CREATE INDEX idx_email ON users(email);

High cardinality indexes can improve query performance. Low cardinality indexes always improve performance. Cardinality does not impact database performance.Cardinality only affects storage, not performance.

Cardinality impacts the performance of database queries. High cardinality indexes are typically more efficient for searches because they reduce the number of rows the database must scan, whereas low cardinality indexes may not be as beneficial.

Q24

Multiple Choice

How do you define cardinality in a many-to-many relationship in PostgreSQL?

SQL Code

Create a many-to-many relationship between 'authors' and 'books' using an association table 'author_books'.

CREATE TABLE authors (
 author_id SERIAL PRIMARY KEY,
 author_name VARCHAR(100)
);

CREATE TABLE books (
 book_id SERIAL PRIMARY KEY,
 book_title VARCHAR(100)
);

CREATE TABLE author_books (
 author_id INT REFERENCES authors(author_id),
 book_id INT REFERENCES books(book_id),
 PRIMARY KEY (author_id, book_id)
);

By creating a junction table that references the primary keys of both tables. By adding a foreign key in one of the tables. By ensuring that both tables have a unique constraint.By creating a composite key in both tables.

In PostgreSQL, a many-to-many relationship between two tables is defined by creating a third table (often called an association or junction table) that references the primary keys of the two related tables. This third table defines the cardinality between the tables.

Q25

Multiple Choice

What is the difference between one-to-one and one-to-many cardinality in PostgreSQL?

SQL Code

Create two tables, 'employees' and 'employee_details', where each employee has one corresponding record in 'employee_details' (one-to-one). Then create another table 'departments' with a one-to-many relationship with 'employees'.

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 employee_name VARCHAR(100)
);

CREATE TABLE employee_details (
 detail_id SERIAL PRIMARY KEY,
 employee_id INT REFERENCES employees(employee_id) UNIQUE,
 address VARCHAR(200)
);

CREATE TABLE departments (
 department_id SERIAL PRIMARY KEY,
 department_name VARCHAR(100)
);

ALTER TABLE employees ADD department_id INT REFERENCES departments(department_id);

One-to-one links one row in Table A to exactly one row in Table B. One-to-many links one row in Table A to multiple rows in Table B. One-to-one requires both tables to have the same primary key.One-to-many requires a unique constraint in the referencing table.

One-to-one cardinality means that a row in one table is linked to exactly one row in another table. One-to-many cardinality means that a row in one table can be linked to multiple rows in another table.

Q26

Multiple Choice

How do you ensure referential integrity in a one-to-many relationship in PostgreSQL?

SQL Code

Create a 'teams' table and a 'players' table where each team can have multiple players (one-to-many), ensuring referential integrity.

CREATE TABLE teams (
 team_id SERIAL PRIMARY KEY,
 team_name VARCHAR(100)
);

CREATE TABLE players (
 player_id SERIAL PRIMARY KEY,
 player_name VARCHAR(100),
 team_id INT REFERENCES teams(team_id)
);

By using a foreign key constraint. By creating a unique constraint on the foreign key column. By indexing the foreign key column.By ensuring the foreign key column cannot be NULL.

Referential integrity in a one-to-many relationship is ensured by using a foreign key constraint in the table representing the 'many' side of the relationship, referencing the primary key of the table on the 'one' side.

Q27

Multiple Choice

What role does cardinality play in optimizing SQL queries in PostgreSQL?

SQL Code

Analyze a query that joins two tables with different cardinalities and suggest an index to improve performance.

CREATE TABLE customers (
 customer_id SERIAL PRIMARY KEY,
 customer_name VARCHAR(100)
);

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 order_date DATE,
 customer_id INT REFERENCES customers(customer_id)
);

-- Query to optimize
SELECT customer_name, order_date
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;

Suggestion: Create an index on orders.customer_id to optimize the join
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

High cardinality columns are better suited for indexing. Low cardinality columns always improve performance when indexed. Cardinality does not affect query optimization.Indexes are unnecessary if the cardinality is high.

Cardinality influences query optimization by determining how selective an index should be. High cardinality columns are generally better candidates for indexing, which can significantly improve query performance.

Q28

Multiple Choice

How do you model a many-to-many relationship with cardinality in PostgreSQL?

SQL Code

Given 'students' and 'courses', create a many-to-many relationship with a cardinality constraint using an association table.

CREATE TABLE students (
 student_id SERIAL PRIMARY KEY,
 student_name VARCHAR(100)
);

CREATE TABLE courses (
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR(100)
);

CREATE TABLE enrollments (
 student_id INT REFERENCES students(student_id),
 course_id INT REFERENCES courses(course_id),
 PRIMARY KEY (student_id, course_id)
);

By using an association table with foreign keys. By creating a composite key in both related tables. By creating separate tables for each relationship.By using a UNIQUE constraint on both foreign keys.

A many-to-many relationship is modeled using an association table that includes foreign keys referencing the primary keys of the related tables. The association table defines the cardinality of the relationship.

Q29

Multiple Choice

What is the impact of low cardinality on database indexes in PostgreSQL?

SQL Code

Create an index on a column with low cardinality and analyze its effect on query performance.

CREATE TABLE products (
 product_id SERIAL PRIMARY KEY,
 product_name VARCHAR(100),
 category VARCHAR(50)
);

-- Low cardinality index
CREATE INDEX idx_category ON products(category);

Query to analyze
SELECT product_name FROM products WHERE category = 'Electronics';

Indexes on low cardinality columns may not significantly improve performance. Low cardinality indexes always improve query performance. Low cardinality columns are not suitable for indexing.Indexes on low cardinality columns can lead to performance degradation.

Low cardinality columns, when indexed, may not provide significant performance benefits since the index does not reduce the search space much, and the database might scan many rows that match the indexed value.

Q30

Multiple Choice

How can you handle high cardinality in a large PostgreSQL table to optimize query performance?

SQL Code

Consider a large 'transactions' table. Create a partitioned table to handle high cardinality and improve query performance.

CREATE TABLE transactions (
 transaction_id SERIAL PRIMARY KEY,
 transaction_date DATE,
 customer_id INT
) PARTITION BY RANGE (transaction_date);

CREATE TABLE transactions_2024 PARTITION OF transactions
FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

CREATE TABLE transactions_2025 PARTITION OF transactions
FOR VALUES FROM ('2025-01-01') TO ('2025-12-31');

Partitioning the table based on high cardinality columns. Creating multiple indexes on high cardinality columns. Avoiding indexes on high cardinality columns.Using table partitioning for better query performance.

Handling high cardinality in large tables can involve techniques like table partitioning, where the table is divided into smaller, more manageable pieces, based on criteria like date ranges. This can lead to improved query performance as the database only scans relevant partitions.

PostgreSQL Database Quiz Questions