Postgresql hard
If two columns (e.g., `City` and `State`) are highly correlated, the standard query planner may underestimate row counts. How can an administrator resolve this?
- A Create a Multi-Column Index to force the database to scan the related keys within a single B-Tree.
- B Create a `STATISTICS` object to track functional dependencies and N-distinct values between columns.
- C Increase the `max_parallel_workers_per_gather` setting to allow more CPUs to scan the table data.
- D Manually update the `pg_class` system catalog to force a specific selectivity ratio for the join key.
Answer: B. Create a `STATISTICS` object to track functional dependencies and N-distinct values between columns.
The planner normally assumes independence (e.g., 10% city A * 10% state B = 1%). But if state B always has city A, the true selectivity is 10%. `CREATE STATISTICS` allows the planner to 'learn' this correlation for better plans.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- In a PostgreSQL environment, what functional characteristic distinguishes a `MATERIALIZED VIEW` from a standard `VIEW`?
- Which standard PostgreSQL utility is designed to extract a single database into a script or archive file for backup purposes?
- Why would PostgreSQL reject a `DELETE` operation on a record despite the user having full administrative privileges?
- The 'Heap Only Tuples' (HOT) optimization improves UPDATE performance. Which specific set of conditions must be met for a HOT update to occur?
- Which native command-line utility is used to execute SQL queries and manage PostgreSQL databases from a terminal session?
- What is the specific purpose of 'Freezing' a row (modifying its `xmin` status) during a major VACUUM operation?