Re: seq scan in the case of max() on the primary key column

On Jun 16, 2011, at 1:36 PM, Shaun Thomas wrote:
> /**
> * Return the Maximum INT Value for a Partitioned Table Column
> *
> * @param string Name of Schema of the base partition table.
> * @param string Name of the base partition table.
> * @param string Name of column to search.
> */
> CREATE OR REPLACE FUNCTION spc_max_part_int(VARCHAR, VARCHAR, VARCHAR)
> RETURNS INT AS
> $$
> DECLARE
> <snip>
> SELECT INTO nParent t.oid
> FROM pg_class t
> JOIN pg_namespace n ON (t.relnamespace=n.oid)
> WHERE n.nspname = sSchema
> AND t.relname = sTable;

FWIW, instead of that, I would do this:

CREATE FUNCTION ...(
p_parent_schema text
, p_parent_table text
) ...
DECLARE
c_parent_oid CONSTANT oid := (p_parent_schema || '.' || p_parent_table )::regclass;

... or ...

CREATE FUNCTION(
p_parent text
)
DECLARE
c_parent_oid CONSTANT oid := p_parent::regclass;

Advantages:

- ::regclass is search_path-aware, so you're not forced into providing a schema if you don't want to
- it will throw an error if it doesn't find a regclass entry
- you can cast the oid back to text: EXECUTE 'SELECT max(' ... 'FROM ' || c_parent_oid::regclass
- you can also query directly with the OID: SELECT relkind = 't' AS is_table FROM pg_class WHERE oid = c_parent_oid
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net