Subject: Slow query - SELECTing one row from 'big' table.
From: Mario Splivalo <mario.splivalo@mobart.hr>
Reply-To: mario.splivalo@mobart.hr
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Organization: Mob-Art
Date: Thu, 03 Nov 2005 14:59:01 +0100
Message-Id: <1131026341.18615.20.camel@ekim>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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
'
DECLARE
	aFrom 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.01 rows=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."