Simple query with Planner underestimating rows.

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:
[image: image.png]

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:

[image: image.png]

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(at)%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"
}
]