Skip to main content

SecuraWall.com

Email security and continuity

Explore

Troubleshooting · Databases

Fixing PostgreSQL Row Estimates with Extended Statistics

PostgreSQL estimates row counts to choose join order, join algorithms and scan types. Single-column statistics can treat two correlated predicates as mostly independent. For city and…

Technical review:

Architecture and operating model

PostgreSQL estimates row counts to choose join order, join algorithms and scan types. Single-column statistics can treat two correlated predicates as mostly independent. For city and postal code, that assumption can multiply estimation error. `CREATE STATISTICS` collects dependencies, ndistinct and MCV information for selected column groups, improving selectivity estimates where correlation matters.

Prove the problem with `EXPLAIN (ANALYZE, BUFFERS, SETTINGS)`. Compare estimated and actual rows at every node and find the first large deviation. A high-level error may only multiply lower errors, so adding an index to the slowest node may miss the cause. Parameter distribution, prepared-statement generic plans and stale ANALYZE data can look similar.

Dependencies model how strongly one column determines another. `ndistinct` helps estimate distinct combinations for grouping. MCV stores frequent combinations and improves selectivity for common values. Creating statistics for every pair increases planning and ANALYZE cost. Select column groups that repeatedly appear together and have meaningful correlation.

Run `ANALYZE` after creating the object; otherwise it has no data. Statistics targets influence sampling and MCV detail, but higher is not always better. Compare estimate ratio, plan shape, buffers and duration on a fixed query set. Record the object's name, purpose and previous plan so it can be removed if regression appears.

  1. 1Slow query and EXPLAIN evidence
  2. 2First estimate error and column correlation
  3. 3CREATE STATISTICS and ANALYZE
  4. 4Plan regression and lifecycle
Extended statistics teach selected relationships only; locate the first estimate error, then add the matching kind.

Design parameters

Error ratio
Record max(actual,estimated)/min(actual,estimated) at the first incorrect node.
Statistics kind
Choose dependencies, ndistinct or mcv for dependency, combination or common-value error.
Column group
Limit objects to correlated columns filtered together in recurring queries.
Sampling target
Measure ANALYZE time, catalog size and estimation improvement together.
Plan stability
Include prepared plans, parameter distributions and version changes in regression tests.

Worked example

On ten million rows, single-column selectivities of 0.10 and 0.01 imply 10,000 rows under independence. If reality is 70,000, the estimate is seven times low. Dependencies or MCV can reduce the difference when the data supports it.

Estimated 120 versus actual 48,000 is a 400-fold error. After statistics, 41,000 versus 48,000 can enable a better join. Record estimate ratio, buffer reads and plan shape, not duration alone.

Example commands: replace lab values and confirm permissions and software versions before use.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT * FROM orders WHERE country='TR' AND city='Izmir';
CREATE STATISTICS st_orders_geo (dependencies, mcv)
ON country, city FROM orders;
ANALYZE orders;
SELECT statistics_name, kinds FROM pg_stats_ext WHERE tablename='orders';

Troubleshooting

ObservationLikely cause / distinctionVerification
Object exists but plan does not changeANALYZE may not have run, the query may not use it, or a generic plan may remain.Inspect `pg_statistic_ext`, analyze time and custom versus generic plans.
Estimate improves but query slowsThe new plan may fit cache, disk or parallelism poorly.Compare BUFFERS, WAL, I/O timing and repeated warm and cold runs.
ANALYZE becomes expensiveHigh targets or many wide objects can increase sampling cost.Review evidence for each object and narrow targets or columns.

Acceptance checks

  1. Find the first large estimate-to-actual deviation.
  2. Measure correlation among the relevant query columns.
  3. Create the suitable dependencies, ndistinct or mcv object.
  4. Run ANALYZE and verify catalog data.
  5. Compare plan, buffers and time with the same parameters.
  6. Record regression, ownership and removal criteria.

Related concepts

Indexes and execution plans

An index can reduce scanned data for suitable queries, but adds write, maintenance and storage costs. Evaluate column order, selectivity, filters and sorting together. Slow queries are not always caused by missing indexes: estimates, locks, storage latency or excessive data transfer may dominate. Inspect representative execution plans and actual row counts. Measure read improvement alongside write overhead, and avoid uncontrolled production maintenance.

Capacity and usable headroom

Raw capacity is not the capacity available to applications. RAID or erasure coding, filesystems, reserved space, metadata, snapshots and growth headroom are separate deductions. TB and TiB representations also change the displayed number. Write calculations with units, establish protected usable capacity, then subtract operating reserves. Track growth rate as well as current utilization. The projected exhaustion date should leave enough time to procure and deploy additional capacity.

Latency distribution

Latency is the time between starting an operation and receiving its result. An average can hide a small number of very slow operations; medians and p95/p99 percentiles answer different questions. Network RTT, storage waits, processor queues and application processing contribute to end-to-end time. State whether measurements come from the client or server. Check whether increases coincide with traffic growth, maintenance or capacity limits. Record normal and peak-hour baselines before selecting an alert threshold.

Telemetry and time correlation

Telemetry combines logs, metrics and events that explain system behavior. A log describes an event, a metric shows behavior over time, and a distributed trace follows a request across components. Clock differences can make one event appear to occur at several times. Use synchronized clocks, reliable source identifiers and consistent time-zone handling. Alarm design should consider duration and user impact alongside thresholds. Monitor gaps in collection separately: absence of logs must not be interpreted as absence of incidents.

Dependencies and restart order

Services commonly depend on identity, DNS, time, networking, databases and licensing. Record a dependency graph describing conditions for operation, rather than only a list of equipment. Recovery order follows that graph; circular dependencies may require emergency access paths. Distinguish restored infrastructure from resumed business activity. Assign an owner, validation method and alternative access path to each dependency. Test assumptions by deliberately making one component unavailable in a controlled end-to-end exercise.

Transaction boundary

A transaction groups operations that an application treats as jointly successful or failed. Database atomicity does not automatically include email delivery or changes in other services. Define the commit point and retry behavior. Evaluate idempotency to prevent processing the same request twice. Test connection loss before and after commit separately: a client-side error does not always mean the transaction failed.

Primary documentation

Prepared by the Doz Teknoloji technical team using the primary references below. Calculations and lab scenarios state their assumptions; validate the applicable product version before rollout.

Knowledge Center

Enterprise IT Product Sales, Licensing and Deployment
Enterprise IT Project & Solution Scenarios
View all related content
Text on WhatsApp
Copied!