From: Mario Splivalo <mario.splivalo@mobart.hr>
To: pgsql-sql@postgresql.org
Subject: Slow query - SELECTing one row from 'big' table.
Date: Thu, 03 Nov 2005 14:59:01 +0100
Message-ID: <1131026341.18615.20.camel@ekim> (raw)
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."
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mario.splivalo@mobart.hr
Subject: Re: Slow query - SELECTing one row from 'big' table.
In-Reply-To: <1131026341.18615.20.camel@ekim>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox