BUG #15184: Planner overestimates number of rows in empty table

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: alexey(dot)ermakov(at)dataegret(dot)com
Subject: BUG #15184: Planner overestimates number of rows in empty table
Date: 2018-05-03 08:49:31
Message-ID: 152533737110.23585.5446055718425840639@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15184
Logged by: Alexey Ermakov
Email address: alexey(dot)ermakov(at)dataegret(dot)com
PostgreSQL version: 10.3
Operating system: Linux
Description:

Hello,

in optimizer/util/plancat.c we have following lines
(https://github.com/postgres/postgres/blob/master/src/backend/optimizer/util/plancat.c#L957)

if (curpages < 10 &&
rel->rd_rel->relpages == 0 &&
!rel->rd_rel->relhassubclass &&
rel->rd_rel->relkind != RELKIND_INDEX)
curpages = 10;

/* report estimated # pages */
*pages = curpages;
/* quick exit if rel is clearly empty */
if (curpages == 0)
{
*tuples = 0;
*allvisfrac = 0;
break;
}

if table is really empty then in first condition we set curpages = 10 and
second condition doesn't apply.
so we estimate that empty table has 10 pages and 2550 rows (for table with
one int column)
which doesn't look good. is it intended behavior? perhaps we should add
(curpages > 0) condition ?

that overestimate could mess plans for example when we use temporary tables
which might be empty.

Thanks,
Alexey Ermakov

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message postgresql_2016@163.com 2018-05-03 09:29:35 Asia/Jakarta Timezone problem
Previous Message Heikki Linnakangas 2018-05-03 08:29:29 Re: postgresql license question