From: Bricklen Anderson <banderson@presinet.com>
To: kevin kempter <kevin@kevinkempterllc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Partitioned tables - planner wont use indexes
Date: Mon, 07 Apr 2008 08:52:50 -0700
Message-ID: <47FA4352.5050309@presinet.com> (raw)
In-Reply-To: <B5B9A348-AC8B-46E2-AA64-2627B2CC069F@kevinkempterllc.com>
References: <B5B9A348-AC8B-46E2-AA64-2627B2CC069F@kevinkempterllc.com>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: banderson@presinet.com, kevin@kevinkempterllc.com
Subject: Re: Partitioned tables - planner wont use indexes
In-Reply-To: <47FA4352.5050309@presinet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox