Use of index with oid's

From: Adriaan Joubert <a(dot)joubert(at)albourne(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Use of index with oid's
Date: 1999-02-25 15:07:07
Message-ID: 36D5671B.9E0EB1D1@albourne.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Luis Bezerra 1999-02-25 16:40:23 unsubscribe
Previous Message Peter Blazso 1999-02-25 13:04:18 Q's: discussion group; pgsql authorization