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."