[PERFORM] Bad prepare performance
Hello, I'm sorry if this has been discussed before, I couldn't find a discussion about this problem. I've done the same query on a 8.2.5 database. The first one is prepared first and the other is executed directly. I understand why the there is such a great difference between the two ways of executing the query (postgres has no way of knowing that $1 will be quite big and that the result is not too big). I could just avoid using prepare statements, but this is done automatically with Perl's DBD::Pg. I know how to avoid using prepare statements (avoid having placeholders in the statement), but that is not the prettiest of work arounds. Is there any planner hints I can use or anything happened or happening with 8.3 or later that I can use? Thank you in advance and the following is the EXPLAIN ANALYZE for the queries. Best regards Martin Kjeldsen - PREPARE test_x (INT) AS SELECT * FROM v_rt_trap_detailed WHERE guid > $1 ORDER BY created LIMIT 3000; EXPLAIN ANALYZE EXECUTE test_x (116505531); QUERY PLAN -- Limit (cost=1143870.95..1143878.45 rows=3000 width=267) (actual time=83033.101..83033.111 rows=4 loops=1) -> Sort (cost=1143870.95..1148074.36 rows=1681367 width=267) (actual time=83033.099..83033.103 rows=4 loops=1) Sort Key: rt_trap.created -> Merge Left Join (cost=0.00..829618.73 rows=1681367 width=267) (actual time=83032.946..83033.051 rows=4 loops=1) Merge Cond: (rt_trap.guid = tp.trap_guid) -> Index Scan using idx_rt_trap_guid on rt_trap (cost=0.00..81738.88 rows=1681367 width=192) (actual time=0.012..0.020 rows=4 loops=1) Index Cond: (guid > $1) Filter: (deleted IS NULL) -> Index Scan using idx_rt_trap_param_trap_guid on rt_trap_param tp (cost=0.00..706147.04 rows=4992440 width=79) (actual time=6.523..78594.750 rows=5044927 loops=1) Filter: (param_oid = 'snmpTrapOID.0'::text) Total runtime: 83033.411 ms (11 rows) dmon2=# EXPLAIN ANALYZE SELECT * FROM v_rt_trap_detailed WHERE guid > 116505531 ORDER BY created LIMIT 3000; QUERY PLAN Limit (cost=9866.45..9867.71 rows=504 width=267) (actual time=0.590..0.616 rows=12 loops=1) -> Sort (cost=9866.45..9867.71 rows=504 width=267) (actual time=0.587..0.596 rows=12 loops=1) Sort Key: rt_trap.created -> Nested Loop Left Join (cost=0.00..9843.83 rows=504 width=267) (actual time=0.157..0.531 rows=12 loops=1) -> Index Scan using idx_rt_trap_guid on rt_trap (cost=0.00..26.78 rows=504 width=192) (actual time=0.022..0.034 rows=12 loops=1) Index Cond: (guid > 116505531) Filter: (deleted IS NULL) -> Index Scan using idx_rt_trap_param_trap_guid on rt_trap_param tp (cost=0.00..18.36 rows=89 width=79) (actual time=0.006..0.009 rows=1 loops=12) Index Cond: (rt_trap.guid = tp.trap_guid) Filter: (param_oid = 'snmpTrapOID.0'::text) Total runtime: 0.733 ms (11 rows) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Bad prepare performance
Le Monday 31 March 2008, Martin Kjeldsen a écrit :
> Hello,
>
> I'm sorry if this has been discussed before, I couldn't find a discussion
> about this problem.
>
> I've done the same query on a 8.2.5 database. The first one is prepared
> first and the other is executed directly.
>
> I understand why the there is such a great difference between the two ways
> of executing the query (postgres has no way of knowing that $1 will be
> quite big and that the result is not too big).
>
> I could just avoid using prepare statements, but this is done automatically
> with Perl's DBD::Pg. I know how to avoid using prepare statements (avoid
> having placeholders in the statement), but that is not the prettiest of
> work arounds.
Did you saw this option :
$sth = $dbh->prepare("SELECT id FROM mytable WHERE val = ?",
{ pg_server_prepare => 0 });
Then, *this* query will not be prepared by the server.
> Is there any planner hints I can use or anything happened or
> happening with 8.3 or later that I can use?
>
> Thank you in advance and the following is the EXPLAIN ANALYZE for the
> queries.
>
> Best regards
>
>
> Martin Kjeldsen
>
> -
>
> PREPARE test_x (INT) AS SELECT * FROM v_rt_trap_detailed WHERE guid > $1
> ORDER BY created LIMIT 3000;
>
> EXPLAIN ANALYZE EXECUTE test_x (116505531);
>
> QUERY PLAN
> ---
>
>--- Limit (cost=1143870.95..1143878.45
> rows=3000 width=267) (actual time=83033.101..83033.111 rows=4 loops=1) ->
> Sort (cost=1143870.95..1148074.36 rows=1681367 width=267) (actual
> time=83033.099..83033.103 rows=4 loops=1) Sort Key: rt_trap.created
> -> Merge Left Join (cost=0.00..829618.73 rows=1681367 width=267)
> (actual time=83032.946..83033.051 rows=4 loops=1) Merge Cond: (rt_trap.guid
> = tp.trap_guid)
>-> Index Scan using idx_rt_trap_guid on rt_trap
> (cost=0.00..81738.88 rows=1681367 width=192) (actual time=0.012..0.020
> rows=4 loops=1) Index Cond: (guid > $1)
> Filter: (deleted IS NULL)
>-> Index Scan using idx_rt_trap_param_trap_guid on
> rt_trap_param tp (cost=0.00..706147.04 rows=4992440 width=79) (actual
> time=6.523..78594.750 rows=5044927 loops=1) Filter: (param_oid =
> 'snmpTrapOID.0'::text)
> Total runtime: 83033.411 ms
> (11 rows)
>
> dmon2=# EXPLAIN ANALYZE SELECT * FROM v_rt_trap_detailed WHERE guid >
> 116505531 ORDER BY created LIMIT 3000; QUERY PLAN
> ---
>
>- Limit (cost=9866.45..9867.71 rows=504 width=267) (actual
> time=0.590..0.616 rows=12 loops=1) -> Sort (cost=9866.45..9867.71
> rows=504 width=267) (actual time=0.587..0.596 rows=12 loops=1) Sort Key:
> rt_trap.created
> -> Nested Loop Left Join (cost=0.00..9843.83 rows=504 width=267)
> (actual time=0.157..0.531 rows=12 loops=1) -> Index Scan using
> idx_rt_trap_guid on rt_trap (cost=0.00..26.78 rows=504 width=192) (actual
> time=0.022..0.034 rows=12 loops=1) Index Cond: (guid > 116505531)
> Filter: (deleted IS NULL)
>-> Index Scan using idx_rt_trap_param_trap_guid on
> rt_trap_param tp (cost=0.00..18.36 rows=89 width=79) (actual
> time=0.006..0.009 rows=1 loops=12) Index Cond: (rt_trap.guid =
> tp.trap_guid)
> Filter: (param_oid = 'snmpTrapOID.0'::text)
> Total runtime: 0.733 ms
> (11 rows)
--
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org
signature.asc
Description: This is a digitally signed message part.
[PERFORM] optimizing query performance
Hi! I've got the following statement:
SELECT DISTINCT sub.os,
COUNT(sub.os) as total
FROM (
SELECT split_part(system.name, ' ', 1) as os
FROM system, attacks
WHERE 1 = 1
AND timestamp >= 1205708400
AND timestamp <= 1206313200
AND attacks.source = system.ip_addr
AND NOT attacks.source IN (
SELECT exclusion
FROM org_excl
WHERE orgid=2
)
) as sub
GROUP BY sub.os
ORDER BY total DESC LIMIT 5
which has the following execution plan:
Limit (cost=1831417.45..1831417.48 rows=5 width=34) (actual time=
1599.915..1599.925 rows=3 loops=1)
-> Unique (cost=1831417.45..1831417.75 rows=41 width=34) (actualtime=
1599.912..1599.918 rows=3 loops=1)
-> Sort (cost=1831417.45..1831417.55 rows=41 width=34) (actual
time=1599.911..1599.913 rows=3 loops=1)
Sort Key: count(split_part(("system".name)::text, ''::text,
1)), split_part(("system".name)::text, ' '::text, 1)
-> HashAggregate (cost=1831415.63..1831416.35 rows=41
width=34) (actual time=1599.870..1599.876 rows=3 loops=1)
-> Nested Loop (cost=23.77..1829328.68 rows=417390
width=34) (actual time=0.075..1474.260 rows=75609 loops=1)
-> Index Scan using index_attacks_timestamp on
attacks (cost=23.77..2454.92 rows=36300 width=11) (actual time=
0.041..137.045 rows=72380 loops=1)
Index Cond: (("timestamp" >= 1205708400) AND
("timestamp" <= 1206313200))
Filter: (NOT (hashed subplan))
SubPlan
-> Seq Scan on org_excl
(cost=0.00..23.75rows=6 width=32) (actual time=
0.014..0.014 rows=0 loops=1)
Filter: (orgid = 2)
-> Index Scan using ip_addr_name_index on
"system" (cost=0.00..50.15 rows=12 width=45) (actual
time=0.009..0.012rows=1 loops=72380)
Index Cond: ("outer".source =
"system".ip_addr)
Total runtime: 1600.056 ms
the NL (nested loop) is accountable for most of the total query time. Is
there any way to avoid the NL and/or speed up the query?
Thanks,
Frits
[PERFORM] POSIX file updates
(Declaration of interest: I'm researching for a publication on OLTP system design) I have a question about file writes, particularly on POSIX. This arose while considering the extent to which cache memory and command queueing on disk drives can help improve performance. Is it correct that POSIX requires that the updates to a single file are serialised in the filesystem layer? So, if we have a number of dirty pages to write back to a single file in the database (whether a table or index) then we cannot pass these through the POSIX filesystem layer into the TCQ/NCQ system on the disk drive, so it can reorder them? I have seen suggestions that on Solaris this can be relaxed. I *assume* that PostgreSQL's lack of threads or AIO and the single bgwriter means that PostgreSQL 8.x does not normally attempt to make any use of such a relaxation but could do so if the bgwriter fails to keep up and other backends initiate flushes. Does anyone know (perhaps from other systems) whether it is valuable to attempt to take advantage of such a relaxation where it is available? Does the serialisation for file update apply in the case where the file contents have been memory-mapped and we try an msync (or equivalent)? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] POSIX file updates
James Mansion wrote: (Declaration of interest: I'm researching for a publication on OLTP system design) I have a question about file writes, particularly on POSIX. This arose while considering the extent to which cache memory and command queueing on disk drives can help improve performance. Is it correct that POSIX requires that the updates to a single file are serialised in the filesystem layer? Is there anything in POSIX that seems to suggest this? :-) (i.e. why are you going under the assumption that the answer is yes - did you read something?) So, if we have a number of dirty pages to write back to a single file in the database (whether a table or index) then we cannot pass these through the POSIX filesystem layer into the TCQ/NCQ system on the disk drive, so it can reorder them? I don't believe POSIX has any restriction such as you describe - or if it does, and I don't know about it, then most UNIX file systems (if not most file systems on any platform) are not POSIX compliant. Linux itself, even without NCQ, might choose to reorder the writes. If you use ext2, the pressure to push pages out is based upon last used time rather than last write time. It can choose to push out pages at any time, and it's only every 5 seconds or so the the system task (bdflush?) tries to force out all dirty file system pages. NCQ exaggerates the situation, but I believe the issue pre-exists NCQ or the SCSI equivalent of years past. The rest of your email relies on the premise that POSIX enforces such a thing, or that systems are POSIX compliant. :-) Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] POSIX file updates
Mark Mielke wrote: Is there anything in POSIX that seems to suggest this? :-) (i.e. why are you going under the assumption that the answer is yes - did you read something?) It was something somewhere on the Sun web site, relating to tuning Solaris filesystems. Or databases. Or ZFS. :-( Needless to say I can't find a search string that finds it now. I remember being surprised though, since I wasn't aware of it either. I don't believe POSIX has any restriction such as you describe - or if it does, and I don't know about it, then most UNIX file systems (if not most file systems on any platform) are not POSIX compliant. That, I can believe. Linux itself, even without NCQ, might choose to reorder the writes. If you use ext2, the pressure to push pages out is based upon last used time rather than last write time. It can choose to push out pages at any time, and it's only every 5 seconds or so the the system task (bdflush?) tries to force out all dirty file system pages. NCQ exaggerates the situation, but I believe the issue pre-exists NCQ or the SCSI equivalent of years past. Indeed there do seem to be issues with Linux and fsync. Its one of things I'm trying to get a handle on as well - the relationship between fsync and flushes of controller and/or disk caches. The rest of your email relies on the premise that POSIX enforces such a thing, or that systems are POSIX compliant. :-) True. I'm hoping someone (Jignesh?) will be prompted to remember. It may have been something in a blog related to ZFS vs other filesystems, but so far I'm coming up empty in google. doesn't feel like something I imagined though. James -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] POSIX file updates
I don't believe POSIX has any restriction such as you describe - or if it does, and I don't know about it, then most UNIX file systems (if not most file systems on any platform) are not POSIX compliant. I suspect that indeed there are two different issues here in that the file mutex relates to updates to the file, not passing the buffers through into the drive, which indeed might be delayed. Been using direct io too much recently. :-( -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] POSIX file updates
Mark Mielke wrote: Is there anything in POSIX that seems to suggest this? :-) (i.e. why are you going under the assumption that the answer is yes - did you read something?) Perhaps it was just this: http://kevinclosson.wordpress.com/2007/01/18/yes-direct-io-means-concurrent-writes-oracle-doesnt-need-write-ordering/ Whichof course isn't on Sun. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] POSIX file updates
On Mon, 31 Mar 2008, James Mansion wrote: Is it correct that POSIX requires that the updates to a single file are serialised in the filesystem layer? Quoting from Lewine's "POSIX Programmer's Guide": "After a write() to a regular file has successfully returned, any successful read() from each byte position in the file that was modified by that write() will return the data that was written by the write()...a similar requirement applies to multiple write operations to the same file position" That's the "contract" that has to be honored. How your filesystem actually implements this contract is none of a POSIX write() call's business, so long as it does. It is the case that multiple writers to the same file can get serialized somewhere because of how this call is implemented though, so you're correct about that aspect of the practical impact being a possibility. So, if we have a number of dirty pages to write back to a single file in the database (whether a table or index) then we cannot pass these through the POSIX filesystem layer into the TCQ/NCQ system on the disk drive, so it can reorder them? As long as the reordering mechanism also honors that any reads that come after a write to a block reflect that write, they can be reordered. The filesystem and drives are already doing elevator sorting and similar mechanisms underneath you to optimize things. Unless you use a sync operation or some sort of write barrier, you don't really know what has happened. I have seen suggestions that on Solaris this can be relaxed. There's some good notes in this area at: http://www.solarisinternals.com/wiki/index.php/Direct_I/O and http://www.solarisinternals.com/wiki/index.php/ZFS_Performance It's clear that such relaxation has benefits with some of Oracle's mechanisms as described. But amusingly, PostgreSQL doesn't even support Solaris's direct I/O method right now unless you override the filesystem mounting options, so you end up needing to split it out and hack at that level regardless. I *assume* that PostgreSQL's lack of threads or AIO and the single bgwriter means that PostgreSQL 8.x does not normally attempt to make any use of such a relaxation but could do so if the bgwriter fails to keep up and other backends initiate flushes. PostgreSQL writes transactions to the WAL. When they have reached disk, confirmed by a successful f[data]sync or a completed syncronous write, that transactions is now committed. Eventually the impacted items in the buffer cache will be written as well. At checkpoint time, things are reconciled such that all dirty buffers at that point have been written, and now f[data]sync is called on each touched file to make sure those changes have made it to disk. Writes are assumed to be lost in some memory (kernel, filesystem or disk cache) until they've been confirmed to be written to disk via the sync mechanism. When a backend flushes a buffer out, as soon as the OS caches that write the database backend moves on without being concerned about how it's eventually going to get to disk one day. As long as the newly written version comes back again if it's read, the database doesn't worry about what's happening until it specifically asks for a sync that proves everything is done. So if the backends or the background writer are spewing updates out, they don't care if the OS doesn't guarantee the order they hit disk until checkpoint time; it's only the synchronous WAL writes that do. Also note that it's usually the case that backends write a substantial percentage of the buffers out themselves. You should assume that's the case unless you've done some work to prove the background writer is handling most writes (which is difficult to even know before 8.3, much less tune for). That how I understand everything to work at least. I will add the disclaimer that I haven't looked at the archive recovery code much yet. Maybe there's some expectation it has for general database write ordering in order for the WAL replay mechanism to work correctly, I can't imagine how that could work though. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Performance Implications of Using Exceptions
Hello, I have a typical many to many join table, in this instance it is capturing the multiplicity described as "one person can have many rooms and one room can have many persons". Further the join expresses where in the room the person is sitting, a seat number. I am creating a function to abstract this away, if there is no record with the same person and room the insert otherwise if it already exists update the record with the new seat value. create table person_room ( id serial, person_id int, room_id int, seat varchar(255), unique (person_id, room_id) ); -- version 1 create or replace function add_person_to_room(person int, room int, s varchar(255)) as $$ begin insert into person_room(person_id, room_id, seat) values (person, room, s); exception when unique_violation then update person_room set seat = s where (person_id = person) and (room_id = room); end; $$ language 'plpgsql'; -- version 2 create or replace function add_person_to_room(person int, room int, s varchar(255)) as $$ declare i int; begin select into i id from person_room where (person_id = person) and (room_id = room); if (not found) then insert into person_room(person_id, room_id, seat) values (person, room, s); else update person_room set seat = s where (person_id = person) and (room_id = room); end; $$ language 'plpgsql'; Which version is faster? Does the exception mechanism add any overhead? Which is more cleaner? -ravi -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance Implications of Using Exceptions
* Ravi Chemudugunta ([EMAIL PROTECTED]) wrote: > Which version is faster? In general I would recommend that you benchmark them using as-close-to-real load as possible again as-real-as-possible data. > Does the exception mechanism add any overhead? Yes, using exceptions adds a fair bit of overhead. Quote from the documentation found here: http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html Tip: A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need. > Which is more cleaner? That would be in the eye of the beholder, generally. Given the lack of complexity, I don't think 'cleanness' in this case really matters all that much. Enjoy, Stephen signature.asc Description: Digital signature
Re: [PERFORM] Performance Implications of Using Exceptions
Stephen Frost wrote > * Ravi Chemudugunta ([EMAIL PROTECTED]) wrote: > > Which version is faster? > > In general I would recommend that you benchmark them using > as-close-to-real load as possible again as-real-as-possible data. > > > Does the exception mechanism add any overhead? > > Yes, using exceptions adds a fair bit of overhead. Quote from the > documentation found here: > http://www.postgresql.org/docs/8.3/static/plpgsql-control-stru > ctures.html > > Tip: A block containing an EXCEPTION clause is significantly more > expensive to enter and exit than a block without one. Therefore, don't > use EXCEPTION without need. > > > Which is more cleaner? > > That would be in the eye of the beholder, generally. Given > the lack of > complexity, I don't think 'cleanness' in this case really matters all > that much. A third option is to update, if not found, insert. Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance Implications of Using Exceptions
Hi, thanks for the quick reply. > In general I would recommend that you benchmark them using > as-close-to-real load as possible again as-real-as-possible data. I am running a benchmark with around 900,000 odd records (real-load on the live machine :o ) ... should show hopefully some good benchmarking results for the two methods. > That would be in the eye of the beholder, generally. Given the lack of > complexity, I don't think 'cleanness' in this case really matters all > that much. I would like to make a comment that is that the only downside I saw of using the exception approach was that if for some reason someone forgot to add the unique constraint to the table, it would be a bit of a nightmare-ness. (I am porting some code into the server where the schema does not have these constraints setup, only in the devel database). Will reply back with my conclusions, I am expecting a large difference. Cheers, ravi -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance Implications of Using Exceptions
Coming to think of it. Would it fine to consider that an UPDATE query that found no records to update is (performance wise) the same as a SELECT query with the same WHERE clause ? As in, does an UPDATE query perform additional overhead even before it finds the record to work on ? *Robins* On Tue, Apr 1, 2008 at 7:53 AM, Robins Tharakan <[EMAIL PROTECTED]> wrote: > I get into these situations quite often and use exactly what stephen > pointed out. > > Do an Update, but if not found, do an insert. Its (by and large) better > than your version 2 since here you may skip running the second query (if the > record exists) but in version 2, two queries are *always* run. And > considering that exception is heavy, this may be a good attempt to give a > try as well. > > update person_room set seat = s where (person_id = person) and (room_id = > room); > if not found then >insert into person_room(person_id, room_id, seat) values (person, room, > s); > end if > > Robins > > > > On Tue, Apr 1, 2008 at 6:26 AM, Stephen Denne < > [EMAIL PROTECTED]> wrote: > > > Stephen Frost wrote > > > * Ravi Chemudugunta ([EMAIL PROTECTED]) wrote: > > > > Which version is faster? > > > > > > In general I would recommend that you benchmark them using > > > as-close-to-real load as possible again as-real-as-possible data. > > > > > > > Does the exception mechanism add any overhead? > > > > > > Yes, using exceptions adds a fair bit of overhead. Quote from the > > > documentation found here: > > > http://www.postgresql.org/docs/8.3/static/plpgsql-control-stru > > > ctures.html > > > > > > Tip: A block containing an EXCEPTION clause is significantly more > > > expensive to enter and exit than a block without one. Therefore, don't > > > use EXCEPTION without need. > > > > > > > Which is more cleaner? > > > > > > That would be in the eye of the beholder, generally. Given > > > the lack of > > > complexity, I don't think 'cleanness' in this case really matters all > > > that much. > > > > A third option is to update, if not found, insert. > > > > Regards, > > Stephen Denne. > > > > Disclaimer: > > At the Datamail Group we value team commitment, respect, achievement, > > customer focus, and courage. This email with any attachments is confidential > > and may be subject to legal privilege. If it is not intended for you please > > advise by reply immediately, destroy it and do not copy, disclose or use it > > in any way. > > __ > > This email has been scanned by the DMZGlobal Business Quality > > Electronic Messaging Suite. > > Please see http://www.dmzglobal.com/dmzmessaging.htm for details. > > __ > > > > > > > > -- > > Sent via pgsql-performance mailing list ( > > pgsql-performance@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-performance > > > >
Re: [PERFORM] Performance Implications of Using Exceptions
"Robins Tharakan" <[EMAIL PROTECTED]> writes: > Would it fine to consider that an UPDATE query that found no records to > update is (performance wise) the same as a SELECT query with the same WHERE > clause ? > As in, does an UPDATE query perform additional overhead even before it finds > the record to work on ? The UPDATE would fire BEFORE STATEMENT and AFTER STATEMENT triggers, if there are any. Also, it would take a slightly stronger lock on the table, which might result in blocking either the UPDATE itself or some concurrent query where a plain SELECT would not've. There might be some other corner cases I've forgotten. But in the basic case I think your assumption is correct. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] POSIX file updates
Greg Smith <[EMAIL PROTECTED]> writes: > Quoting from Lewine's "POSIX Programmer's Guide": > "After a write() to a regular file has successfully returned, any > successful read() from each byte position in the file that was modified by > that write() will return the data that was written by the write()...a > similar requirement applies to multiple write operations to the same file > position" Yeah, I imagine this is what the OP is thinking of. But note that what it describes is the behavior of concurrent write() and read() calls within a normally-functioning system. I see nothing there that constrains the order in which writes hit physical disk, nor (to put it another way) that promises anything much about the state of the filesystem after a crash. As you stated, PG is largely independent of these issues anyway. As long as the filesystem honors its spec-required contract that it won't claim fsync() is complete before all the referenced data is safely on persistent store, we are OK. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
