Message-ID: <47FA4352.5050309@presinet.com>
Date: Mon, 07 Apr 2008 08:52:50 -0700
From: Bricklen Anderson <banderson@presinet.com>
User-Agent: Thunderbird 2.0.0.12 (X11/20080227)
MIME-Version: 1.0
To: kevin kempter <kevin@kevinkempterllc.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Partitioned tables - planner wont use indexes
References: <B5B9A348-AC8B-46E2-AA64-2627B2CC069F@kevinkempterllc.com>
In-Reply-To: <B5B9A348-AC8B-46E2-AA64-2627B2CC069F@kevinkempterllc.com>
Content-Type: text/plain; charset=US-ASCII; format=flowed
Content-Transfer-Encoding: 7bit

kevin kempter wrote:
> One of the things we need to query is the min date from the master table 
> - we may explore alternatives for this particular query, however even if 
> we fix this query I think we have a fundamental issue with the use of 
> indexes (actuallt the non-use) by the planner.

We had a similar requirement, so I've been using a function that loops 
over the child tables, and queries for the min date from each. If all 
you need is the date, you can try a function call. Here is a modified 
version of what I've been using:

CREATE OR REPLACE function get_min_date() RETURNS DATE as $_$
DECLARE
     x RECORD;
     min_date DATE;
     min_date_tmp DATE;
     qry TEXT;
BEGIN
     /* can also test MIN() aggregate, rather than ORDER BY/LIMIT */
     FOR x IN EXECUTE 'select tablename from pg_tables where tablename 
like ''part_20%''' loop
         qry := 'SELECT logdate FROM '||x.tablename||' ORDER BY logdate 
LIMIT 1';
         EXECUTE qry INTO min_date_tmp;
         IF (min_date IS NULL OR (min_date_tmp IS NOT NULL AND 
min_date_tmp<min_date)) THEN
             min_date := min_date_tmp;
         END IF;
     END LOOP;
     RETURN min_date;
END;
$_$ language plpgsql immutable;