[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    [GENERAL] Use of index with oid's
From:       Adriaan Joubert <a.joubert () albourne ! com>
Date:       1999-02-25 15:07:07
[Download RAW message or body]

As I cannot return a complete row of a record from a function (written
in PL), I return an oid. So the function looks like this

CREATE FUNCTION task_next (int4) RETURNS oid AS
'
DECLARE
...
END;
' LANGUAGE 'plpgsql';

This function can return null. I then select the next row with

tt> select * from tasksids where oid=task_next(0);

and this is very slow, especially if task_next(0) returns null. I thus
defined an index on the oids:

tt> create unique index tasksids_oid_idx on tasksids(oid);

But get the following


tt=> explain select * from tasksids where oid=''::oid;
NOTICE:  QUERY PLAN:

Index Scan using tasksids_oid_idx on tasksids  (cost=2.05 size=1
width=33)

EXPLAIN
tt=> explain select * from tasksids where oid=task_next(0)::oid;
NOTICE:  QUERY PLAN:

Seq Scan on tasksids  (cost=2.22 size=4 width=33)

EXPLAIN


So why doesn't the query use the index when the oid is returned from a
function? And is there a better way of getting that row (or a null
record) returned from the function? This seems kind-of clumsy.

Cheers,

Adriaan

[prev in list] [next in list] [prev in thread] [next in thread]