Question on index - Mailing list pgsql-novice

From Roberto Rezende de Assis
Subject Question on index
Date
Msg-id 419B8B30.2@yahoo.com.br
Whole thread Raw
List 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 ?


pgsql-novice by date:

Previous
From: "Marc G. Fournier"
Date:
Subject: Re: subscribe for a digest fails
Next
From: sarlav kumar
Date:
Subject: Re: subscribe for a digest fails