Slow query - SELECTing one row from 'big' table. - Mailing list pgsql-sql

From Mario Splivalo
Subject Slow query - SELECTing one row from 'big' table.
Date
Msg-id 1131026341.18615.20.camel@ekim
Whole thread Raw
Responses Re: Slow query - SELECTing one row from 'big' table.
List pgsql-sql
I have a procedure that goes something like this:
-- Function: create_message(varchar, varchar, varchar, int4)
-- DROP FUNCTION create_message("varchar", "varchar", "varchar", int4);

CREATE OR REPLACE FUNCTION create_message("varchar", "varchar",
"varchar", int4) RETURNS SETOF messages AS
'
DECLAREaFrom ALIAS FOR $1;aTo ALIAS FOR $2;aRawText ALIAS FOR $3;aDestinationId ALIAS FOR $4;rec messages%ROWTYPE;

BEGIN   INSERT INTO messages ("from", "to", "raw_text", "destination_id")          VALUES (aFrom, aTo, aRawText,
aDestinationId);
    FOR rec IN        SELECT *        FROM messages        WHERE (id = currval(''public.message_id_seq''::text))
LOOP       RETURN NEXT rec;    END LOOP;
 
    RETURN;
END

The table messages is like this:
CREATE TABLE messages
( id int4 NOT NULL DEFAULT nextval('public.message_id_seq'::text), "from" varchar(15) NOT NULL, "to" varchar(10) NOT
NULL,receiving_time timestamptz(0) NOT NULL DEFAULT now(), raw_text varchar NOT NULL, keyword varchar, destination_id
int4,vpn_id int4, service_id int4, status int4 NOT NULL DEFAULT 2, reply varchar, CONSTRAINT pk_messages PRIMARY KEY
(id),CONSTRAINT fk_messages_destination_id FOREIGN KEY (destination_id)
 
REFERENCES destinations (id) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_messages_service_id FOREIGN KEY
(service_id)REFERENCES
 
services (id) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_messages_vpn_id FOREIGN KEY (vpn_id) REFERENCES
vpns
(id) ON UPDATE NO ACTION ON DELETE NO ACTION
) 

And extra indices on table messages are like this:

CREATE INDEX idx_messages_receiving_time ON messages USING btree (receiving_time);
CREATE INDEX idx_messages_vpn_id ON messages USING btree (vpn_id);
CREATE INDEX idx_service_id ON messages USING btree (service_id);

Now, the table messsages has well above 700k rows. When I call the
function, it takes almost 5 seconds to run on a busy server, on my
shitty I/O capabilities laptop it takes as long as 30 seconds (no users
but me). Now, when I run the SELECT * FROM messages WHERE id = 12317 (or
any other random integer), the response time is much less.

Here is the query plan:
test_db=# explain select * from messages where id = 742001;                                 QUERY PLAN
------------------------------------------------------------------------------Index Scan using pk_messages on messages
(cost=0.00..6.01rows=2
 
width=197) (actual time=0.030..0.034 rows=1 loops=1)  Index Cond: (id = 742001)Total runtime: 0.086 ms
(3 rows)

Now I'm wondering why the SELECT inside the procedure takes that long?
I've tried first to extract the return form currval function to the
local variable, and then supply that variable to the WHERE, but nothing
changed. It seems that SELECT itself is slow, not the currval.

Am I doing something wrong? Or that's just the way it is? 

I have a workaround now, I declared local variable of type messages%ROW,
and I fill that variable with the parametars received from the caller of
the function, and then i do RETURN NEXT, RETURN. But still, why is that
SELECT so slow inside the function?
Mike
-- 
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr

"I can do it quick, I can do it cheap, I can do it well. Pick any two."




pgsql-sql by date:

Previous
From: Axel Rau
Date:
Subject: Re: Poor performance in inet << cidr join (Resolved)
Next
From: Achilleus Mantzios
Date:
Subject: Re: Welcome to the pgsql-sql list!