Re: BUG #15184: Planner overestimates number of rows in empty table - Mailing list pgsql-bugs

From Alexey Ermakov
Subject Re: BUG #15184: Planner overestimates number of rows in empty table
Date
Msg-id 5AEC4D7F.2060608@dataegret.com
Whole thread Raw
In response to Re: BUG #15184: Planner overestimates number of rows in empty table  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
List pgsql-bugs
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


pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #15186: how get data from db files
Next
From: PG Bug reporting form
Date:
Subject: BUG #15187: When use huge page,there may be a lot of hanged connections with status startup orauthentication