pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery is 800 times slower when running in function!
2+ messages / 2 participants
[nested] [flat]
* Query is 800 times slower when running in function!
@ 2005-12-01 01:17 Ralph Mason <ralph.mason@telogis.com>
0 siblings, 1 reply; 2+ messages in thread
From: Ralph Mason @ 2005-12-01 01:17 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a simple query that is running inside a plpgsql function.
SELECT INTO _point_id id FROM ot2.point WHERE unit_id = _unit_id AND
time > _last_status ORDER BY time LIMIT 1;
Both _unit_id and _last_status variables in the function. the table has
an index on unit_id,point
When this runs inside a function it is taking about 800ms. When I run
it stand alone it takes about .8 ms, which is a big difference.
I can find no reason for this. I have checked that time and _last_status
time are both timestamps and unit_id and _unit_id are both oids.
The explain looks perfect
explain select id from point where unit_id = 95656 and time >
'2005-11-30 23:11:00' order by time limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Limit (cost=9.94..9.95 rows=1 width=12)
-> Sort (cost=9.94..9.95 rows=2 width=12)
Sort Key: "time"
-> Index Scan using unit_point on point (cost=0.00..9.93
rows=2 width=12)
Index Cond: ((unit_id = 95656::oid) AND ("time" >
'2005-11-30 23:11:00'::timestamp without time zone))
(5 rows)
Time: 0.731 ms
A query inside the same function that runs right before this one runs at
the expected speed (about 1 ms)
SELECT INTO _last_status time FROM ot2.point WHERE unit_id = _unit_id
AND flags & 64 = 64 ORDER BY unit_id desc, time DESC LIMIT 1;
It uses the same table and indexes.
To time individual queries inside the function I am using:
tt := (timeofday()::timestamp)-startt; RAISE INFO 'Location A %' , tt;
startt := timeofday()::timestamp;
tt is an interval and startt is a timestamp.
I am out of things to try. Can anyone help?
Thanks
Ralph
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Query is 800 times slower when running in function!
@ 2005-12-01 04:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ralph Mason <ralph.mason@telogis.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2005-12-01 04:58 UTC (permalink / raw)
To: Ralph Mason <ralph.mason@telogis.com>; +Cc: pgsql-performance
Ralph Mason <ralph.mason@telogis.com> writes:
> I have a simple query that is running inside a plpgsql function.
> SELECT INTO _point_id id FROM ot2.point WHERE unit_id = _unit_id AND
> time > _last_status ORDER BY time LIMIT 1;
It would probably help significantly to make that be
"ORDER BY unit_id, time". This'd eliminate the need for the separate
sort step and encourage the planner to use the index, even when it does
not know whether the "time > x" condition is selective or not.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-12-01 04:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-01 01:17 Query is 800 times slower when running in function! Ralph Mason <ralph.mason@telogis.com>
2005-12-01 04:58 ` 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