Skip site navigation (1) Skip section navigation (2)

BUG #6112: heuristic for empty parent tables that are members of inheritance trees

From: "Anish Kejariwal" <anishkej(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6112: heuristic for empty parent tables that are members of inheritance trees
Date: 2011-07-11 16:51:47
Message-ID: 201107111651.p6BGpluQ047958@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      6112
Logged by:          Anish Kejariwal
Email address:      anishkej(at)gmail(dot)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

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-07-11 17:10:58
Subject: Re: BUG #6111: ftell mismatch error
Previous:From: glumDate: 2011-07-11 16:18:52
Subject: BUG #6111: ftell mismatch error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group