Hello,
First some specifics:
OS Linux FC1 1 Gig of Ram IDE RAID 1 array AMD 2200 XP
Running both 8.0rc2 and 7.4.6 for testing. The function is a plphp function. The code for the function is below:
CREATE OR REPLACE FUNCTION get_users(bigint[]) RETURNS SETOF resolved_users_type AS '
$userData = array();
$temp = array();
foreach ($args[0] as $value) { $temp[] = $value."::bigint"; }
$res = spi_exec_query("SELECT id, realname, email FROM users WHERE id IN (".implode(",", $temp).")");
if ($res[''processed''] > 0) {
while ($row = spi_fetch_row($res)) {
$userData[] = $row;
}
}
return ($userData) ? $userData : NULL;
' LANGUAGE 'plphp';
Basically we take an array of values and look up a list of users based on the elements in the array and return the results. There is a static
cast for 7.4.6 sake which may not be required for 8.
Here is a sample dataset for the users table:
id | realname | email
------+--------------------+------------------------------
2 | Jonathan Daugherty | [EMAIL PROTECTED]
4 | Steven1 Klassen | [EMAIL PROTECTED]
8 | Steven1 Klassen | [EMAIL PROTECTED]
9 | Steven1 Klassen | [EMAIL PROTECTED]
12 | Steven1 Klassen | [EMAIL PROTECTED]
56 | Jonathan Daugherty | [EMAIL PROTECTED]
2000 | Steven Klassen | [EMAIL PROTECTED]
23 | Steven1 Klassen | [EMAIL PROTECTED]
690 | Steven Klassen | [EMAIL PROTECTED]
4000 | Steven Klassen | [EMAIL PROTECTED]
1243 | Steven Klassen | [EMAIL PROTECTED]
5 | Steven1 Klassen | [EMAIL PROTECTED]
6 | Steven1 Klassen | [EMAIL PROTECTED]
230 | Steven Klassen | [EMAIL PROTECTED]
330 | Steven Klassen | [EMAIL PROTECTED]
430 | Steven Klassen | [EMAIL PROTECTED]
440 | Steven Klassen | [EMAIL PROTECTED]
550 | Steven Klassen | [EMAIL PROTECTED]
660 | Steven Klassen | [EMAIL PROTECTED]
770 | Steven Klassen | [EMAIL PROTECTED]
Here is the functional_query we are running:
SELECT * FROM get_users('{2,4,8,9,12,56,2000,23,690,4000,1243,5,6,230,330,430,440,550,660,770}');
Statistics is set to 50 on the id column (which is way to high). It has about 51000 rows.
8.0 first run: 109.664 ms 8.0 second run: 2.939 ms
7.4.6 first run: 121.416ms 7.4.6 second run: 8.665 ms
So this shows a significant increase in functions performance for second + runs and a little difference for first runs. If I enable preload libraries the numbers are the following:
8.0 first run: 107.689ms 8.0 second run: 2.915 ms
7.4.6 first run: 119.400 ms 7.4.6 second run: 8.629 ms
It doesn't look like preload libraries helps as much as I would like but every little bit helps. The really good news of course is the amazing
increase in performance from 7.4.6 to 8.0. I assume this is because
we are now keeping statistics for functions.
The changes in 8.0 should help projects like TSearch2 quite a bit.
One thing I did note that is very odd is:
sklassen=# explain analyze select * from get_users('{2,4,8,9,12,56,2000,23,690,4000,1243,5,6,230,330,430,440,550,660,770}');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Function Scan on get_users (cost=0.00..12.50 rows=1000 width=72) (actual time=2.464..2.488 rows=20 loops=1)
Total runtime: 2.520 ms
SELECT id, realname, email FROM users WHERE id IN (2::bigint,4::bigint,8::bigint,9::bigint,12::bigint,56::bigint,2000::bigint,23::bigint,690::bigint,4000::bigint,1243::bigint,5::bigint,6::bigint,230::bigint,330::bigint,430::bigint,440::bigint,550::bigint,660::bigint,770::bigint)"
LOG: duration: 2.937 ms
Notice the two durations, they are different but for the exact same run on the query. Is the duration being calculated between psql and the backend?
Sincerely,
Joshua D. Drake
-- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
