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