Question on index

From: Roberto Rezende de Assis <rezende_assis(at)yahoo(dot)com(dot)br>
To: Postgesql list <pgsql-novice(at)postgresql(dot)org>
Subject: Question on index
Date: 2004-11-17 17:32:32
Message-ID: 419B8B30.2@yahoo.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


When you have a large table (16000 rows, 56 columns in my case)
----------------------------------------------------------------
CREATE TABLE table (
table_id int,
....
....
number_of_the_document int,
name text,
mothers_name text,
);
----------------------------------------------------------------

And you put an index on the atribute "number_of_the_document", for example.
----------------------------------------------------------------
CREATE INDEX idx_number_of_the_document ON table (number_of_the_document);
----------------------------------------------------------------

Then you want make this kind of select it willl use the
'idx_number_of_the_document' index.
----------------------------------------------------------------
SELECT name,mothers_name FROM table WHERE (number_of_the_document = 999999);
----------------------------------------------------------------

But if you want to put this into a function that will return a custom type:
----------------------------------------------------------------
CREATE TYPE names AS (name text,mothers_name text);
----------------------------------------------------------------
CREATE OR REPLACE FUNCTION function(int)
RETURNS names AS '
DECLARE
document ALIAS FOR $1;
answer names%ROWTYPE;
BEGIN
SELECT INTO answer name,mothers_name FROM table WHERE
(number_of_the_document = document);
RETURN answer;
END;
' LANGUAGE plpgsql;
----------------------------------------------------------------

Will that function uses the 'idx_number_of_the_document' index ??

The use of the "EXPLAIN ANALYZE SELECT * FROM function(XXXXXX)" didn't
help.
It said something "Function Scan on f1" , but what does it mean ?

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2004-11-17 18:46:41 Re: subscribe for a digest fails
Previous Message sarlav kumar 2004-11-17 16:50:20 subscribe for a digest fails