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
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 |