From: | Alexey Ermakov <alexey(dot)ermakov(at)dataegret(dot)com> |
---|---|
To: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #15184: Planner overestimates number of rows in empty table |
Date: | 2018-05-04 12:09:35 |
Message-ID: | 5AEC4D7F.2060608@dataegret.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2018-05-04 16:34:46 | BUG #15187: When use huge page, there may be a lot of hanged connections with status startup or authentication |
Previous Message | PG Bug reporting form | 2018-05-04 10:24:23 | BUG #15186: how get data from db files |