CODEWITHSUNDEEP
postgresql
Sarfaraz Makandar
Sarfaraz Makandar

Reputation: 6733

PostgreSQL: How to decrease time of query execution in PostgreSQL?

I have a table which contains millions of records, but when i want to retrieve some rows its taking more time to process the query.

For example:
--Table 

CREATE TABLE employee  
(  
empid int,  
empdate timestamp  
)

--Function to insert records 

CREATE OR REPLACE FUNCTION insertemployee()  
RETURNS void AS  
$BODY$  
declare  
i int :=0;  
begin  
    while(i<100000)   
    loop   
    insert into employee values(i,now());  
    i:=i+1;  
    end loop;  
end;  
$BODY$  
LANGUAGE plpgsql;

--Function execution 

select insertemployee();

Total query runtime: 891 ms.

--Function to retrieve the rows 

CREATE OR REPLACE FUNCTION retrieveemployee() RETURNS TABLE (empid int,empdate timestamp) AS  
$body$  
begin    
       return query    
       select e.empid,e.empdate from employee AS e WHERE e.empid BETWEEN 1000 and   80000;    
end;    
$body$    
LANGUAGE plpgsql;

--Function execution 

select * from retrieveemployee();

Total query runtime: 1382 ms.

Upvotes: 0

Views: 548

Answers (1)

Patrick
Patrick

Reputation: 32161

It is not entirely clear what your problem is, but a generally good idea is to have a primary key on your table and indexes on any other columns that you regularly search on (a primary key is also an index, with the limitation that it is UNIQUE).

Indentally, rather than using RETURNS TABLE with a definition, you should have your function RETURNS SETOF employee to make things easier to understand and maintain.

Upvotes: 1

Related Questions