Use of index with oid's

Поиск
Список
Период
Сортировка
От Adriaan Joubert
Тема Use of index with oid's
Дата
Msg-id 36D5671B.9E0EB1D1@albourne.com
обсуждение исходный текст
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Peter Blazso
Дата:
Сообщение: Q's: discussion group; pgsql authorization
Следующее
От: Luis Bezerra
Дата:
Сообщение: unsubscribe