Обсуждение: BUG #6112: heuristic for empty parent tables that are members of inheritance trees

The following bug has been logged online:

Bug reference:      6112
Logged by:          Anish Kejariwal
Email address:      anishkej@gmail.com
PostgreSQL version: 9.0.3
Operating system:   Linux
Description:        heuristic for empty parent tables that are members of
inheritance trees
Details:

Hello,

I'm filing this issue as a bug, and it seems like Tom Lane agrees that there
is a bug, and said: " maybe we should reconsider the heuristic for tables
that are members of inheritance trees --- particularly parents of
inheritance trees."

All information is in:
http://archives.postgresql.org/pgsql-performance/2011-07/msg00063.php

Scenario:
-empty parent table
-all data is in child/partitioned tables

Bug:
optimizer considers the parent table to be empty.

Result: The execution plan is incorrect.  My particularly query took 25
seconds with the wrong execution plan, but 0.3 seconds with the correct
execution plan.

Work around:
Tom Lane suggested the following workaround to "defeat the empty-table
heuristic:
update pg_class set relpages = 1 where relname = 'icecream';

I'm using this work around for now, but I don't think this is acceptable.
If someone were to accidentally run vacuum analyze on the parent table, then
the relpages will be set back to zero, and the query will run slowly.

Please let me know if you have any questions.

Anish

On Mon, Jul 11, 2011 at 12:51 PM, Anish Kejariwal <anishkej@gmail.com> wrot=
e:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A06112
> Logged by: =A0 =A0 =A0 =A0 =A0Anish Kejariwal
> Email address: =A0 =A0 =A0anishkej@gmail.com
> PostgreSQL version: 9.0.3
> Operating system: =A0 Linux
> Description: =A0 =A0 =A0 =A0heuristic for empty parent tables that are me=
mbers of
> inheritance trees
> Details:
>
> Hello,
>
> I'm filing this issue as a bug, and it seems like Tom Lane agrees that th=
ere
> is a bug, and said: " maybe we should reconsider the heuristic for tables
> that are members of inheritance trees --- particularly parents of
> inheritance trees."
>
> All information is in:
> http://archives.postgresql.org/pgsql-performance/2011-07/msg00063.php
>
> Scenario:
> -empty parent table
> -all data is in child/partitioned tables
>
> Bug:
> optimizer considers the parent table to be empty.
>
> Result: The execution plan is incorrect. =A0My particularly query took 25
> seconds with the wrong execution plan, but 0.3 seconds with the correct
> execution plan.
>
> Work around:
> Tom Lane suggested the following workaround to "defeat the empty-table
> heuristic:
> update pg_class set relpages =3D 1 where relname =3D 'icecream';
>
> I'm using this work around for now, but I don't think this is acceptable.
> If someone were to accidentally run vacuum analyze on the parent table, t=
hen
> the relpages will be set back to zero, and the query will run slowly.
>
> Please let me know if you have any questions.

Tom fixed this in commit f3ff0433ab32fdc69da3c8f8e691ef6b4366559c on July 1=
4th.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company