pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow query - SELECTing one row from 'big' table.
2+ messages / 2 participants
[nested] [flat]
* Slow query - SELECTing one row from 'big' table.
@ 2005-11-03 13:59 Mario Splivalo <mario.splivalo@mobart.hr>
2005-11-03 15:17 ` Re: Slow query - SELECTing one row from 'big' table. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Mario Splivalo @ 2005-11-03 13:59 UTC (permalink / raw)
To: 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
'
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."
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Slow query - SELECTing one row from 'big' table.
2005-11-03 13:59 Slow query - SELECTing one row from 'big' table. Mario Splivalo <mario.splivalo@mobart.hr>
@ 2005-11-03 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2005-11-03 15:17 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-sql
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> SELECT *
> FROM messages
> WHERE (id = currval(''public.message_id_seq''::text))
That cannot legally be converted into an indexscan, because currval() is
a volatile function --- the planner cannot be certain that its value
won't change during the query. (In this case we can assume it's safe
because nothing in that query would call nextval(), but the planner
isn't omniscient enough to make that conclusion.)
Fetch the currval into a local variable and use the variable in the
query.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-11-03 15:17 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-03 13:59 Slow query - SELECTing one row from 'big' table. Mario Splivalo <mario.splivalo@mobart.hr>
2005-11-03 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
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