Subject: Simple query with Planner underestimating rows.

From: Felipe López Montes <[email-masked]>
Date: Tue, 28 Jan 2025 20:29:18 +0100

Hi all,

I am using PostgreSQL 17.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
12.4.0, 64-bit.

I have been facing this issue past weeks and I don't know what else to try,
I have a very simple query where the planner is underestimating the number
of rows. So instead of estimating the ~ actual number of rows (5120), it
estimates only 1.

For the sake of clarity and simplicity, I have disabled the nestloop join
in the session because it involved a gather clause and parallel workers and
was still underestimating rows, so the same problem happens with nestloop
strategy too. Instead now the planner goes for a merge join ( if you still
prefer me to send you the nestloop plan, I can do):

SELECT t1.participant_identifier,
       t2.participant_identifier,
       t3.participant_identifier
FROM public.table1 t1
         INNER JOIN public.table2 t2
                    ON t2.participant_identifier = t1.participant_identifier
         INNER JOIN public.table3 t3
                    ON t3.participant_identifier = t1.participant_identifier
                        AND t3.programme_identifier = t2.programme_identifier;

Plan:

Table definitions:

create table table1
(
    participant_identifier uuid not null
        constraint participant_identifier_unique_idx
            primary key
);

create table table2
(
    participant_identifier uuid not null,
    programme_identifier   uuid not null,
    constraint participant_identifier_programme_identifier_unique_idx
        primary key (participant_identifier, programme_identifier)
);

create index programme_identifier_idx
    on table2 (programme_identifier);

create index participant_identifier_idx
    on table2 (participant_identifier);

create table table3
(
    id                     varchar(18) not null
        constraint table3_pk
            primary key,
    programme_identifier   uuid,
    participant_identifier uuid
);

create index participant_identifier_programme_identifier_idx
    on table3 (participant_identifier, programme_identifier);

Pg_class info:

Additional info:

SELECT COUNT(*) FROM table3 WHERE participant_identifier IS NULL AND
programme_identifier IS NOT NULL; --295
SELECT COUNT(*) FROM table3 WHERE participant_identifier IS NOT NULL
AND programme_identifier IS NULL; --3122
SELECT COUNT(*) FROM table3 WHERE participant_identifier IS NULL AND
programme_identifier IS NULL; -- 15438
SELECT COUNT(*) FROM table3 WHERE participant_identifier IS NOT NULL
AND programme_identifier IS NOT NULL; -- 63339

SELECT COUNT(DISTINCT programme_identifier ) FROM table3; -- 61939
SELECT COUNT(DISTINCT participant_identifier ) FROM table3; -- 63364
SELECT COUNT(*)
FROM (
         SELECT DISTINCT programme_identifier, participant_identifier
         FROM table3
     ) subquery; --65057

SELECT COUNT(DISTINCT programme_identifier ) FROM table2; -- 62582
SELECT COUNT(DISTINCT participant_identifier ) FROM table2; -- 62120

- I have also tried creating a compound partial index on table3 with
participant_identifier and programme_identifier with a condition for
non-null values but same estimation problem.

- These tables do not receive regular updates nor deletes as I have put
them in a separate schema for debugging purposes, and besides values
previously shown in table3, there are not null values on other join fields
on the rest of the tables as you can see with the not null constraint.
Autovacuum is also enabled, I will attach the settings later on.

- In terms of Hardware, this queries are being ran in an AWS RDS with 2
CPU, 4GB RAM, instance class db.t3.medium

I have tried several things:
- Creating extended statistics.
- ANALYZE various times on all the tables.
- REINDEX.
- VACUUM ANALYZE.
- Increasing statistics on join fields.

Thank you for your help, I would appreciate some guidance :).

Cheers,

Felipe.

Settings:

[
  {
    "name": "allow_alter_system",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "application_name",
    "current_setting": "PhpStorm 2024.2.4",
    "source": "session"
  },
  {
    "name": "archive_library",
    "current_setting": "rds_archive",
    "source": "configuration file"
  },
  {
    "name": "archive_mode",
    "current_setting": "on",
    "source": "configuration file"
  },
  {
    "name": "archive_timeout",
    "current_setting": "5min",
    "source": "configuration file"
  },
  {
    "name": "autovacuum_analyze_scale_factor",
    "current_setting": "0.05",
    "source": "configuration file"
  },
  {
    "name": "autovacuum_max_workers",
    "current_setting": "3",
    "source": "configuration file"
  },
  {
    "name": "autovacuum_naptime",
    "current_setting": "15s",
    "source": "configuration file"
  },
  {
    "name": "autovacuum_vacuum_cost_limit",
    "current_setting": "200",
    "source": "configuration file"
  },
  {
    "name": "autovacuum_vacuum_scale_factor",
    "current_setting": "0.1",
    "source": "configuration file"
  },
  {
    "name": "autovacuum_work_mem",
    "current_setting": "117495kB",
    "source": "configuration file"
  },
  {
    "name": "checkpoint_completion_target",
    "current_setting": "0.9",
    "source": "configuration file"
  },
  {
    "name": "client_encoding",
    "current_setting": "UTF8",
    "source": "client"
  },
  {
    "name": "compute_query_id",
    "current_setting": "auto",
    "source": "configuration file"
  },
  {
    "name": "DateStyle",
    "current_setting": "ISO, MDY",
    "source": "client"
  },
  {
    "name": "debug_logical_replication_streaming",
    "current_setting": "buffered",
    "source": "configuration file"
  },
  {
    "name": "debug_parallel_query",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "default_toast_compression",
    "current_setting": "lz4",
    "source": "configuration file"
  },
  {
    "name": "effective_cache_size",
    "current_setting": "1879920kB",
    "source": "configuration file"
  },
  {
    "name": "enable_nestloop",
    "current_setting": "off",
    "source": "session"
  },
  {
    "name": "enable_presorted_aggregate",
    "current_setting": "on",
    "source": "configuration file"
  },
  {
    "name": "extra_float_digits",
    "current_setting": "3",
    "source": "session"
  },
  {
    "name": "fsync",
    "current_setting": "on",
    "source": "configuration file"
  },
  {
    "name": "full_page_writes",
    "current_setting": "on",
    "source": "configuration file"
  },
  {
    "name": "gss_accept_delegation",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "hot_standby",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "huge_pages",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "icu_validation_level",
    "current_setting": "error",
    "source": "configuration file"
  },
  {
    "name": "idle_in_transaction_session_timeout",
    "current_setting": "1d",
    "source": "configuration file"
  },
  {
    "name": "ignore_invalid_pages",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "jit",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "krb_server_keyfile",
    "current_setting": "/rdsdbdata/config/keytab",
    "source": "configuration file"
  },
  {
    "name": "listen_addresses",
    "current_setting": "*",
    "source": "command line"
  },
  {
    "name": "lo_compat_privileges",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "log_autovacuum_min_duration",
    "current_setting": "10s",
    "source": "configuration file"
  },
  {
    "name": "log_checkpoints",
    "current_setting": "on",
    "source": "configuration file"
  },
  {
    "name": "log_destination",
    "current_setting": "stderr",
    "source": "configuration file"
  },
  {
    "name": "log_directory",
    "current_setting": "/rdsdbdata/log/error",
    "source": "configuration file"
  },
  {
    "name": "log_file_mode",
    "current_setting": "0644",
    "source": "configuration file"
  },
  {
    "name": "log_filename",
    "current_setting": "postgresql.log.%Y-%m-%d-%H",
    "source": "configuration file"
  },
  {
    "name": "log_line_prefix",
    "current_setting": "%t:%r:%u@%d:[%p]:",
    "source": "configuration file"
  },
  {
    "name": "log_rotation_age",
    "current_setting": "1h",
    "source": "configuration file"
  },
  {
    "name": "log_timezone",
    "current_setting": "UTC",
    "source": "configuration file"
  },
  {
    "name": "log_truncate_on_rotation",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "logging_collector",
    "current_setting": "on",
    "source": "configuration file"
  },
  {
    "name": "maintenance_work_mem",
    "current_setting": "64MB",
    "source": "configuration file"
  },
  {
    "name": "max_connections",
    "current_setting": "403",
    "source": "configuration file"
  },
  {
    "name": "max_locks_per_transaction",
    "current_setting": "64",
    "source": "configuration file"
  },
  {
    "name": "max_parallel_apply_workers_per_subscription",
    "current_setting": "2",
    "source": "configuration file"
  },
  {
    "name": "max_parallel_workers",
    "current_setting": "8",
    "source": "configuration file"
  },
  {
    "name": "max_prepared_transactions",
    "current_setting": "0",
    "source": "configuration file"
  },
  {
    "name": "max_replication_slots",
    "current_setting": "20",
    "source": "configuration file"
  },
  {
    "name": "max_stack_depth",
    "current_setting": "6MB",
    "source": "configuration file"
  },
  {
    "name": "max_wal_senders",
    "current_setting": "35",
    "source": "configuration file"
  },
  {
    "name": "max_wal_size",
    "current_setting": "6GB",
    "source": "configuration file"
  },
  {
    "name": "max_worker_processes",
    "current_setting": "8",
    "source": "configuration file"
  },
  {
    "name": "min_wal_size",
    "current_setting": "192MB",
    "source": "configuration file"
  },
  {
    "name": "port",
    "current_setting": "5432",
    "source": "configuration file"
  },
  {
    "name": "rds.blue_green_replication_type",
    "current_setting": "physical",
    "source": "configuration file"
  },
  {
    "name": "rds.cte_materialize_mode",
    "current_setting": "default",
    "source": "configuration file"
  },
  {
    "name": "rds.delegated_extension_allow_drop_cascade",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "rds.enable_pgactive",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "rds.force_autovacuum_logging_level",
    "current_setting": "warning",
    "source": "configuration file"
  },
  {
    "name": "rds.internal_databases",
    "current_setting": "rdsadmin,template0",
    "source": "configuration file"
  },
  {
    "name": "rds.logical_replication",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "rds.rds_reserved_connections",
    "current_setting": "4",
    "source": "configuration file"
  },
  {
    "name": "recovery_init_sync_method",
    "current_setting": "syncfs",
    "source": "configuration file"
  },
  {
    "name": "recovery_prefetch",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "remove_temp_files_after_crash",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "reserved_connections",
    "current_setting": "2",
    "source": "configuration file"
  },
  {
    "name": "scram_iterations",
    "current_setting": "4096",
    "source": "configuration file"
  },
  {
    "name": "search_path",
    "current_setting": "public",
    "source": "session"
  },
  {
    "name": "send_abort_for_crash",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "send_abort_for_kill",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "session_preload_libraries",
    "current_setting": "",
    "source": "configuration file"
  },
  {
    "name": "shared_buffers",
    "current_setting": "939960kB",
    "source": "configuration file"
  },
  {
    "name": "shared_preload_libraries",
    "current_setting": "rdsutils,pg_tle,pg_stat_statements",
    "source": "configuration file"
  },
  {
    "name": "ssl",
    "current_setting": "on",
    "source": "configuration file"
  },
  {
    "name": "ssl_ca_file",
    "current_setting": "/rdsdbdata/rds-metadata/ca-cert.pem",
    "source": "configuration file"
  },
  {
    "name": "ssl_cert_file",
    "current_setting": "/rdsdbdata/rds-metadata/server-cert.pem",
    "source": "configuration file"
  },
  {
    "name": "ssl_key_file",
    "current_setting": "/rdsdbdata/rds-metadata/server-key.pem",
    "source": "configuration file"
  },
  {
    "name": "ssl_min_protocol_version",
    "current_setting": "TLSv1.2",
    "source": "configuration file"
  },
  {
    "name": "summarize_wal",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "superuser_reserved_connections",
    "current_setting": "3",
    "source": "configuration file"
  },
  {
    "name": "sync_replication_slots",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "synchronous_commit",
    "current_setting": "on",
    "source": "configuration file"
  },
  {
    "name": "TimeZone",
    "current_setting": "UTC",
    "source": "client"
  },
  {
    "name": "track_activity_query_size",
    "current_setting": "4kB",
    "source": "configuration file"
  },
  {
    "name": "track_functions",
    "current_setting": "pl",
    "source": "configuration file"
  },
  {
    "name": "track_io_timing",
    "current_setting": "on",
    "source": "session"
  },
  {
    "name": "track_wal_io_timing",
    "current_setting": "on",
    "source": "configuration file"
  },
  {
    "name": "unix_socket_directories",
    "current_setting": "/tmp",
    "source": "configuration file"
  },
  {
    "name": "unix_socket_group",
    "current_setting": "rdsdb",
    "source": "configuration file"
  },
  {
    "name": "unix_socket_permissions",
    "current_setting": "0666",
    "source": "command line"
  },
  {
    "name": "update_process_title",
    "current_setting": "on",
    "source": "configuration file"
  },
  {
    "name": "vacuum_buffer_usage_limit",
    "current_setting": "962kB",
    "source": "configuration file"
  },
  {
    "name": "vacuum_cost_page_miss",
    "current_setting": "5",
    "source": "configuration file"
  },
  {
    "name": "vacuum_failsafe_age",
    "current_setting": "1200000000",
    "source": "configuration file"
  },
  {
    "name": "vacuum_multixact_failsafe_age",
    "current_setting": "1200000000",
    "source": "configuration file"
  },
  {
    "name": "wal_compression",
    "current_setting": "zstd",
    "source": "configuration file"
  },
  {
    "name": "wal_keep_size",
    "current_setting": "2GB",
    "source": "configuration file"
  },
  {
    "name": "wal_level",
    "current_setting": "replica",
    "source": "configuration file"
  },
  {
    "name": "wal_receiver_create_temp_slot",
    "current_setting": "off",
    "source": "configuration file"
  },
  {
    "name": "wal_receiver_timeout",
    "current_setting": "30s",
    "source": "configuration file"
  },
  {
    "name": "wal_sender_timeout",
    "current_setting": "30s",
    "source": "configuration file"
  }
]

---

From: Greg Sabino Mullane <[email-masked]>
Date: Tue, 28 Jan 2025 21:12:03 -0500

> prefer me to send you the nestloop plan, I can do):

Yes, please, send the exact plan you are having problems with. Also, what
exactly is the performance issue? It seems your *second best plan* is
running in 36 milliseconds?

Cheers,
Greg

P.S. In the future, you can use this to quickly grab relevant settings:
select name, current_setting(name) from pg_settings where source <>
'default';

---

From: Andrei Lepikhov <[email-masked]>
Date: Wed, 29 Jan 2025 10:05:50 +0700

On 1/29/25 02:29, Felipe López Montes wrote:
> I have been facing this issue past weeks and I don't know what else to 
> try, I have a very simple query where the planner is underestimating the 
> number of rows. So instead of estimating the ~ actual number of rows 
> (5120), it estimates only 1.
Your query represents a typical PostgreSQL issue: Here, I see two 
'almost' unique columns: participant_identifier and 
programme_identifier. The result is that the join cardinality on a 
unique column is predicted to be close to the size of the smaller relation.
But in the second join, you have a join by two columns with low selectivity:

Postgres doesn't gather dependency statistics on two or more columns and 
just multiplies the low selectivities of these clauses, reducing the 
number of rows to a possible minimum - 1.

What you can do? Right now, maybe only pg_hint_plan may help in such a 
situation. However, if you provide some test cases, we may check the 
forgotten feature [1], which enables extended statistics in join clause 
estimations and may push development efforts in that direction.

[1] using extended statistics to improve join estimates
https://www.postgresql.org/message-id/flat/c8c0ff31-3a8a-7562-bbd3-78b2ec65f16c%40enterprisedb.com

-- 
regards, Andrei Lepikhov

---

From: Andrei Lepikhov <[email-masked]>
Date: Wed, 29 Jan 2025 15:57:15 +0700

On 1/29/25 15:32, Felipe López Montes wrote:
> Hi,
> 
> Thank you for your reply,
> 
> I have already tried creating such statistics on t3.programme_identifier 
> and t3.participant_identifier and the plan is the same, however I cannot 
> create them for the right part of the join as they are from different 
> tables (t1 and t2)
Of course, because for now, a join clause can't be estimated by extended 
statistics. It applies only to a scan filter (clause referencing only 
one relation).
I have meant that with the development patch [1] applied, you may create 
two statistics on t3(participant_identifier,programme_identifier) and 
t2(participant_identifier,programme_identifier). These statistics would 
then be used to estimate the join clause and may resolve the problem.

-- 
regards, Andrei Lepikhov