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

Thanks for the explanation and example.

On 5/3/18 21:40, Andrew Gierth wrote:
> As the large comment immediately above explains, it is indeed intended
> behavior. The reason is that over-estimating usually doesn't cause too
> much harm, but under-estimating tends to blow things up in certain
> critical cases (such as causing foreign-key checks to do sequential
> scans on tables during a data-loading transaction).
>
> It's actually still possible to trigger those kinds of pathological
> cases, but in between the estimation hacks and the plan cache, you have
> to work a lot harder at it. Consider for example:
>
> create table tree (id integer primary key,
>                     parent_id integer references tree);
>
> insert into tree values (1, null);
> vacuum analyze tree;  -- now relpages=1 reltuples=1
> begin;
> insert into tree select i, i-1 from generate_series(2,10) i;
> insert into tree select i, i-1 from generate_series(11,100000) i;
> commit;
>
> That last insert could take maybe half an hour to run, because the FK
> check has a query plan - established as a generic plan since the middle
> insert ran it more than 5 times - with the small table size leading to a
> sequential scan.
>
> Without the vacuum analyze that I stuck in there, the code in plancat.c
> avoids this problem by treating the table as large enough to require an
> indexscan from the start.
>
> As the comment says, this does mean we don't handle the case when the
> table really is empty and stays empty. But this should be very rare
> compared to the case where the table starts out empty but then has rows
> added.
>
--
Alexey Ermakov