Re: [HACKERS] User Defined Functions/AM's inherently slow?
On Jan 18, 2004, at 1:34 AM, Christopher Kings-Lynne wrote: Theory B would be that there's some huge overhead in calling non-built-in functions on your platform. We do know that looking up a "C" function is significantly more expensive than looking up a "builtin" function, but there should only be half a dozen such calls involved in this test case; it's hard to credit that that takes 200 msec. Does the time drop at all on second and subsequent repetitions in a single backend run? Yes, it drops from about .680ms to the .250ish that I posted. I suppose I should try compiling this little stub into postgres, eh? What if you try the new preload_libraries (or whatever it's called) config variable in postgresql.conf in the 7.4 release? yes, that takes care of the initial load time of the library itself (bringing the initial .680ms back to .250ish for the first run), but this is not the problem. eric ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] User Defined Functions/AM's inherently slow?
Theory B would be that there's some huge overhead in calling non-built-in functions on your platform. We do know that looking up a "C" function is significantly more expensive than looking up a "builtin" function, but there should only be half a dozen such calls involved in this test case; it's hard to credit that that takes 200 msec. Does the time drop at all on second and subsequent repetitions in a single backend run? Yes, it drops from about .680ms to the .250ish that I posted. I suppose I should try compiling this little stub into postgres, eh? What if you try the new preload_libraries (or whatever it's called) config variable in postgresql.conf in the 7.4 release? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] User Defined Functions/AM's inherently slow?
On Jan 17, 2004, at 11:33 PM, Tom Lane wrote: The difference between "total runtime" and the top plan node's runtime has to represent plan startup/shutdown time. I'm suspicious that your stubs are somehow not initializing something, though on first glance I do not see what. I can't see anything either... which is why I brought it up. I'm still a noob with this stuff, and thought maybe I was just missing something. Theory B would be that there's some huge overhead in calling non-built-in functions on your platform. We do know that looking up a "C" function is significantly more expensive than looking up a "builtin" function, but there should only be half a dozen such calls involved in this test case; it's hard to credit that that takes 200 msec. Does the time drop at all on second and subsequent repetitions in a single backend run? Yes, it drops from about .680ms to the .250ish that I posted. I suppose I should try compiling this little stub into postgres, eh? eric ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User Defined Functions/AM's inherently slow?
On Jan 17, 2004, at 11:27 PM, Tom Lane wrote:
Eric Ridge <[EMAIL PROTECTED]> writes:
costestimate: {
PG_RETURN_VOID();
}
This at least needs to set some values into the output parameters ---
zeroes are okay, not setting them at all isn't. I'm surprised the
planner doesn't go nuts. It looks from your EXPLAIN results like
the values are coming up zero anyway, but seeing that cost_index()
doesn't do anything to initialize those local variables, I'd expect
fairly unpredictable behavior.
I have tried setting them all to zero, and even using the
backend/utils/adt/selfuncs.c:genericcostestimate() code (it's not
exported, it seems), but no matter what sane (or crazy!) numbers I
provide for the cost estimate, the "fact" remains, my AM, which does
"nothing" is slower than the builtin btree AM.
Could this just be related to the fact that my AM is in a .so, and
there's just some operating system/C runtime overhead in calling
functions in dynamically loaded libraries?
eric
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] User Defined Functions/AM's inherently slow?
Eric Ridge <[EMAIL PROTECTED]> writes: > I should have included the entire explain output: > stub AM: > Index Scan using idxa_stub on test2 (cost=0.00..2.68 rows=1 width=5) > (actual time=0.002..0.002 rows=0 loops=1) > Index Cond: (a ==> '1'::text) > Total runtime: 0.247 ms > builtin btree AM: > Index Scan using idxa_btree on test2 (cost=0.00..4.68 rows=1 width=5) > (actual time=0.024..0.026 rows=1 loops=1) > Index Cond: (a = '1'::text) > Total runtime: 0.060 ms > If the "actual time" numbers are really a measure of the amount of time > spent in (at least) the index, it seems the stub should report a > smaller "total runtime", but alas, it doesn't. The difference between "total runtime" and the top plan node's runtime has to represent plan startup/shutdown time. I'm suspicious that your stubs are somehow not initializing something, though on first glance I do not see what. Theory B would be that there's some huge overhead in calling non-built-in functions on your platform. We do know that looking up a "C" function is significantly more expensive than looking up a "builtin" function, but there should only be half a dozen such calls involved in this test case; it's hard to credit that that takes 200 msec. Does the time drop at all on second and subsequent repetitions in a single backend run? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] User Defined Functions/AM's inherently slow?
Eric Ridge <[EMAIL PROTECTED]> writes:
> costestimate: {
> PG_RETURN_VOID();
> }
This at least needs to set some values into the output parameters ---
zeroes are okay, not setting them at all isn't. I'm surprised the
planner doesn't go nuts. It looks from your EXPLAIN results like
the values are coming up zero anyway, but seeing that cost_index()
doesn't do anything to initialize those local variables, I'd expect
fairly unpredictable behavior.
The other stubs look fine on quick review.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] User Defined Functions/AM's inherently slow?
On Jan 17, 2004, at 10:22 PM, Tom Lane wrote: Eric Ridge <[EMAIL PROTECTED]> writes: I've created a stub AM that literally does nothing. It's not possible for an index AM to "do nothing", at least not for an indexscan. It has to return tuple pointers. What are you doing for that? I should have included the entire explain output: stub AM: Index Scan using idxa_stub on test2 (cost=0.00..2.68 rows=1 width=5) (actual time=0.002..0.002 rows=0 loops=1) Index Cond: (a ==> '1'::text) Total runtime: 0.247 ms builtin btree AM: Index Scan using idxa_btree on test2 (cost=0.00..4.68 rows=1 width=5) (actual time=0.024..0.026 rows=1 loops=1) Index Cond: (a = '1'::text) Total runtime: 0.060 ms If the "actual time" numbers are really a measure of the amount of time spent in (at least) the index, it seems the stub should report a smaller "total runtime", but alas, it doesn't. eric ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User Defined Functions/AM's inherently slow?
On Jan 17, 2004, at 10:22 PM, Tom Lane wrote:
Eric Ridge <[EMAIL PROTECTED]> writes:
I've created a stub AM that literally does nothing.
It's not possible for an index AM to "do nothing", at least not for an
indexscan. It has to return tuple pointers. What are you doing for
that?
costestimate: {
PG_RETURN_VOID();
}
beginscan: {
Relation index = (Relation) PG_GETARG_POINTER(0);
int keysz = PG_GETARG_INT32(1);
ScanKey scankey = (ScanKey) PG_GETARG_POINTER(2);
IndexScanDesc scan = RelationGetIndexScan(index, keysz, scankey);
PG_RETURN_POINTER(scan);
}
rescan: {
PG_RETURN_VOID();
}
gettuple: {
PG_RETURN_BOOL(false);
}
endscan: {
PG_RETURN_VOID();
}
I think the above is about as close to "nothing" as one can get.
In trying to track down some performance issues with my real AM, I
decided to make this stub AM just to see what the overhead is... then I
started seeing these crazy results.
eric
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] User Defined Functions/AM's inherently slow?
Eric Ridge <[EMAIL PROTECTED]> writes: > I've created a stub AM that literally does nothing. It's not possible for an index AM to "do nothing", at least not for an indexscan. It has to return tuple pointers. What are you doing for that? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] User Defined Functions/AM's inherently slow?
I've created a stub AM that literally does nothing. It indexes nothing. It scans for nothing. Nadda. It does just enough work to specify return values that prevent PG from dumping core. What I've found is that this stub AM, compiled with the same options as postgres itself (-O2 -fno-strict-aliasing), is roughly 4 times slower than the built in btree AM that actually does something useful! The test table contains 1 column, and 1 row: My stub AM: explain analyze select * from test where a ==> '1'; Total runtime: 0.254 ms builtin btree AM: explain analyze select * from test where a = '1'; Total runtime: 0.058 ms (I ran each one a number times, with basically the same results). What gives? *scratches head* I know btree's are efficient, but geez, can they really be more efficient than O(zero)? :) Looking at the backtrace from the beginscan function of each AM, PG doesn't appear to do anything different for user-provided AM's. My platform is OS X 10.3.2, using PG 7.4, gcc version 3.3 (Apple's build #1495). Any insight would be greatly appreciated. Thanks! eric ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] BUG #1053: Configuration should be in /etc/postgres
[EMAIL PROTECTED] ("PostgreSQL Bugs List") writes:
> Description:Configuration should be in /etc/postgres
>
> Details:
>
> It would be better if postgres kept the configurations files in
> standard /etc location. Like under /etc/postgres
This "fix" would prevent people from having multiple database
instances, which can be quite a useful thing.
Nothing prevents you from doing what Debian does, which is to
establish a symbolic link between the /etc location and where
configuration "really" is.
--
let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
Incremental Backup (Was: [HACKERS] And ppl complain about *our* beta cycles ...)
Mensaje citado por David Garamond <[EMAIL PROTECTED]>: > Marc G. Fournier wrote: > >>From the Firebird FAQ: > > > > "The first beta was released on January 29, 2003. We are hoping to be > > close to a full release some time around Easter 2003." > > > > They are at RC8 right now ... running a *wee* bit behind scheduale :) > > Yes, they're pretty late. Last time I read, the only major issues > preventing their final release is around the installer. The 1.5 codebase > itself has been stabilized for quite a while. Practically all work is > now done to the 2.0 branch/HEAD. They have several goodies in store for > the 2.0 release (e.g.: incremental backup). Anyone working on incremental backups for PostgreSQL? I kind of miss them from my Informix times (hey, I think it's the only thing I really miss :-) ). Maybe when PITR is ready, making incremental backups will be trivial. -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués | Programador, DBA Centro de Telemática| Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
