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