Hi,
I encountered a problem while trying to improve the performance of a certain
select query I have made.
here is a simplified code for the function I am using
CREATE OR REPLACE FUNCTION test_func(STR text)
RETURNS integer AS
$BODY$
begin
insert into plcbug(val) values('begin time before perfor
ething%' or 'something%' [*], right?? do you
think the optimizer can do better by conditionally splitting the plan
according to actual value of a variable?
[*] for the sake of the discussion lets forget about '_something'.
Thanks again.
On Tue, Aug 16, 2011 at 16:40, Tom
hi,
i had a stored procedure in ms-sql server. this stored procedure gets a
parameter (account-id), dose about 20 queries, fills some temporary tables,
and finally, returns a few result-sets. this stored procedure converted to
stored function in postgresql (9.1). the result-sets are being returned
hi,
thanks a lot to all of you for your help.
(i'm sorry i did not know how to reply to a certain message)
i found that the best number of active connections is indeed 8-10. with
8-10 active connections postgresql did ~170 "account-id"s. this is still
only half of what mssql did, but it now make
hi all,
i ran vmstat during the test :
[yb@centos08 ~]$ vmstat 1 15
procs ---memory-- ---swap-- -io --system--
-cpu-
r b swpd free buff cache si sobibo in cs us sy id
wa st
0 0 0 6131400 160556 111579200 112 22 17
hi, all.
well, i wondered why there is high rate of bo (blocks out). the procedure
is practically read-only during the whole test. although it's not strictly
read-only, because in a certain condition, there might be writing to a
certain table. but that condition can not be met during this test.
s
guess what:
after reducing bo (blocks out) to ~10% by using a ramdisk (improving
overall performance by ~15-20%), i now managed to reduced it to ~3% by
removing the "analyze temp-table" statements.
it also :
reduced b (Process which are waiting for I/O) to zero
reduced wa (percentage of time
Hi, all.
this is an obligation from the past:
http://archives.postgresql.org/pgsql-performance/2012-05/msg00017.php
the same test, that did ~230 results, is now doing ~700 results. that is,
BTW even better than mssql.
the ultimate solution for that problem was to NOT to do "ON COMMIT DELETE
ROWS
Hi all,
as far as i looked around about the new feature: index-only scan, i guess
this feature will not include the option such as ms-sql INCLUDE.
well, i have a table with columns: a,b,c
i query the table like this: select a,c from table where a=x and b=y
as for now, i have unique-index on (a,b)
