[PERFORM] multi-threaded pgloader needs your tests
Hi, You may remember some thread about data loading performances and multi-threading support in pgloader: http://archives.postgresql.org/pgsql-performance/2008-02/msg00081.php The pgloader code to handle this is now ready to get tested, a more structured project could talk about a Release Candidate status. http://pgloader.projects.postgresql.org/dev/TODO.html http://pgloader.projects.postgresql.org/dev/pgloader.1.html#_parallel_loading http://packages.debian.org/pgloader --- experimental has the next version As for the performances benefit of this new version (2.3.0~dev2), all the work could be reduced to zilch because of the python Global Interpreter Lock, which I've been aware of late in the development effort. http://docs.python.org/api/threads.html This documentation states that (a) using generators you're not that concerned, and (b) the global lock still allows for IO and processing at the same time. As pgloader uses generators, I'm still not sure how much a problem this will be. I'd like to have some feedback about the new version, in term of bugs encountered and performance limitations (is pgloader up to what you would expect a multi-threaded loader to be at?) Regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: [PERFORM] response time when querying via JDBC and via psql differs
Do not use setString() method to pass the parameter to the PreparedStatement in JDBC. Construct an SQL query string as you write it here and query the database with this new SQL string. This will make the planner to recreate a plan every time for every new SQL string per session (that is not usually good) but it will make the planner to choose a correct plan. -- Valentine Gogichashvili On Feb 25, 11:06 am, [EMAIL PROTECTED] (Pavel Rotek) wrote: Hi all, i have strange problem with performance in PostgreSQL (8.1.9). My problem shortly: I'm using postgreSQL via JDBC driver (postgresql-8.1-404.jdbc3.jar) and asking the database for search on table with approximately 3 000 000 records. I have created functional index table(lower(href) varchar_pattern_ops) because of lower case like searching. When i ask the database directly from psql, it returns result in 0,5 ms, but when i put the same command via jdbc driver, it returns in 10 000 ms. Where can be the problem?? Any problem with PostgreSQL tuning?? The command is select df.id as id, df.c as c, df.href as href, df.existing as existing, df.filesize as filesize from documentfile df where (lower(href) like 'aba001!_223581.djvu' escape '!' ) order by id limit 1 Thank you very much for any help, Kind regards, Pavel Rotek ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] LISTEN / NOTIFY performance in 8.3
At 11:58 PM -0500 2/23/08, Tom Lane wrote: Attached is a perl script that sort of simulates what's going on. Thanks for the script. It's not showing any particular problems here, though. With log_min_duration_statement = 10, the only statements that (slightly) exceed 10ms are the select count(*) from generate_series(1, 15000) ones. I tried the test script on another machine (similar but not identical to the original machine) running 8.3 and although the notify performance was *much* better than the original I still see notifications taking longer than the select count(*) from generate_series(1, 15000) queries, and also longer than some simple updates to other tables that are also happening on the server. duration: 10.030 ms statement: select count(*) from generate_series(1, 15000) duration: 224.833 ms statement: NOTIFY to_producer Perhaps this shouldn't be made much of as I'm sure there are many way that this could quite naturally happen. I've been thinking of LISTEN / NOTIFY as one of the least expensive and therefore speedy ways to get the word out to participating processes that something has changed (versus using a manually setup signals table that interested parties updated and selected from). Now that I see a little bit more of what goes on under the hood of this function I see that it's still basically table-driven and I'll adjust my expectations accordingly, but I'm still puzzled by the hugely slow notifications showing up on the original server running the producer / consumer setup. With ps I can see some postgres backends with a 'notify interrupt waiting' command line during the tests - could it be an issue with signal handling on the original machine - something entirely outside of PG's control? Thx, -Joel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] PG planning randomly ?
Laurent Raufaste [EMAIL PROTECTED] writes: I'm having some issues with this simple query: SELECT _comment.*, _article.title AS article_title, _article.reference AS article_reference FROM _comment INNER JOIN _article ON _article.id = _comment.parent_id WHERE _comment.path @ '0.1.3557034' ORDER BY _comment.date_publishing DESC OFFSET 0 LIMIT 5 ; The varying information here is the ltree path 0.1.3557034 What PG version is this? If it's 8.2 or later then increasing the stats target for _comment.path to 100 or more would likely help. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Q on views and performance
On Mon, Feb 25, 2008 at 11:56 AM, Matthew [EMAIL PROTECTED] wrote: On Mon, 25 Feb 2008, Kynn Jones wrote: This is just GREAT!!! It fits the problem to a tee. It makes the queries quick then? It is good that you ask. Clearly you know the story: a brilliant-sounding optimization that in practice has only a small effect at best... I'm totally puzzled. It makes absolutely no sense to me... For my analysis, in addition to creating the index on (type, zipk) that you suggested, I also added an extra column to T containing a random integer in the range 0..99, and created an index on this, so that I could produce a totally shuffled clustering. I compared the performance in going from a randomly-clustered table to a (type, zipk)-clustered table, and the output of EXPLAIN was encouraging, but when I ran the actual queries under EXPLAIN ANALYZE the difference in execution time was negligible. Live and learn! Actually, what's driving me absolutely insane is the documentation for EXPLAIN and for Pg's query planning in general. I've read the docs (in particular the chapter on performance), but I still can't make any sense of EXPLAINs results, so I can't begin to understand why optimizations like the one you suggested turned out to be ineffective. For example, the first lines of two recent EXPLAIN ANALYZE outputs are Nested Loop Left Join (cost=58.00..1154.22 rows=626 width=26) (actual time= 1.462..26.494 rows=2240 loops=1) Merge Left Join (cost=33970.96..34887.69 rows=58739 width=26) (actual time= 106.961..126.589 rows=7042 loops=1) Actual runtimes are 27ms and 128ms. The ratio 128/27 is much smaller than one would expect from the relative costs of the two queries. It looks like there is no proportionality at all between the estimated costs and actual running time... (BTW, all these runs of EXPLAIN were done after calls to VACUUM ANALYZE.) This is one of the many things I don't understand about this case... What I would like to be able to do is to at least make enough sense of query plans to determine whether they are reasonable or not. This requires knowing the algorithms behind each type of query tree node, but I have not found this info... On the positive side, in the course of all this analysis I must have done *something* to improve the performance, because now even the unoptimized queries are running pretty fast (e.g. queries that used to take about 1.5seconds are now taking 130ms). But unfortunately I don't know what was it that I did to bring this speed-up about! Anyway, be that as it may, thank you very much for your suggestion. Kynn
Re: [PERFORM] LISTEN / NOTIFY performance in 8.3
Joel Stevenson [EMAIL PROTECTED] writes: Now that I see a little bit more of what goes on under the hood of this function I see that it's still basically table-driven and I'll adjust my expectations accordingly, Yeah, there's been discussion of replacing the implementation with some all-in-memory queue kind of setup, but no one's got round to that yet. With ps I can see some postgres backends with a 'notify interrupt waiting' command line during the tests - could it be an issue with signal handling on the original machine - something entirely outside of PG's control? No, that's not unexpected if you have the same notify being delivered to multiple processes that had been idle. They'll all get wakened and try to read pg_listener to see what happened, but since this is a read-modify-write type of operation it uses an exclusive lock, so only one can clear its pg_listener entry at a time. The 'waiting' ones you are seeing are stacked up behind whichever one has the lock at the moment. They shouldn't be waiting for long. I'm still baffled by why we aren't seeing comparable performance for the same test case. What I'm testing on is couple-year-old desktop kit (dual 2.8GHz Xeon, consumer-grade disk drive) --- I had assumed your server would be at least as fast as that, but maybe not? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] PG planning randomly ?
Laurent Raufaste [EMAIL PROTECTED] writes: 2008/2/26, Tom Lane [EMAIL PROTECTED]: If it's 8.2 or later then increasing the stats target for _comment.path to 100 or more would likely help. I'm using PG 8.2.4. We are using 100 as default_statistics_target by default and all our column are using this value: Hmm, that ought to be enough to activate the better selectivity estimator. Unless ... did you update this database from a pre-8.2 DB that already had contrib/ltree in it? If so, did you just load the existing old definition of ltree as part of your dump, or did you install 8.2's version fresh? I'm wondering if you have a definition of operator @ that doesn't specify the new selectivity estimator. Please try a pg_dump -s and see what it shows as the definition of @. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] PG planning randomly ?
2008/2/26, Tom Lane [EMAIL PROTECTED]: Laurent Raufaste [EMAIL PROTECTED] writes: 2008/2/26, Tom Lane [EMAIL PROTECTED]: If it's 8.2 or later then increasing the stats target for _comment.path to 100 or more would likely help. I'm using PG 8.2.4. We are using 100 as default_statistics_target by default and all our column are using this value: Hmm, that ought to be enough to activate the better selectivity estimator. Unless ... did you update this database from a pre-8.2 DB that already had contrib/ltree in it? If so, did you just load the existing old definition of ltree as part of your dump, or did you install 8.2's version fresh? I'm wondering if you have a definition of operator @ that doesn't specify the new selectivity estimator. Please try a pg_dump -s and see what it shows as the definition of @. regards, tom lane Here's the first definition of the @ operator in my dump: -- -- Name: @; Type: OPERATOR; Schema: public; Owner: postgres -- CREATE OPERATOR @ ( PROCEDURE = ltree_risparent, LEFTARG = ltree, RIGHTARG = ltree, COMMUTATOR = @, RESTRICT = ltreeparentsel, JOIN = contjoinsel ); ALTER OPERATOR public.@ (ltree, ltree) OWNER TO postgres; Our data was created on an older PG (8.1.x) but we installed 8.2.x from scratch, only dumping the schema and the data in it. I used ltree found in the 8.2.4 source. Do you think an update of ltree, or better of the database will fix the problem ? We plan on upgrading to the 8.3 branch in the next weeks, but this behavior can't wait this much as our servers are overburned from time to time =( Thanks for your help ;) -- Laurent Raufaste http://www.glop.org/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] PG planning randomly ?
On Tue, Feb 26, 2008 at 11:12 AM, Laurent Raufaste [EMAIL PROTECTED] wrote: 2008/2/26, Tom Lane [EMAIL PROTECTED]: What PG version is this? If it's 8.2 or later then increasing the stats target for _comment.path to 100 or more would likely help. I'm using PG 8.2.4. We are using 100 as default_statistics_target by default and all our column are using this value: # SELECT attname,attstattarget FROM pg_attribute WHERE attrelid=16743 AND attname='path' ; attname | attstattarget -+--- path|-1 I tried increasing the stats target with the command: SET default_statistics_target=1000 ; That's the command I launched before executing the ANALYZE showed in the previous mail. The ANALYZE were longer to complete, but it did not change the planner behavior (sometimes right, sometimes wrong). You're doing it wrong. The default target affects newly created columns / tables. You need to use alter table to change a stats target after creation. Like so: alter table abc alter column xyz set statistics 100; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] disabling an index without deleting it?
This might be a weird question...is there any way to disable a particular index without dropping it? There are a few queries I run where I'd like to test out the effects of having (and not having) different indexes on particular query plans and performance. I'd really prefer not to have to drop and ultimately recreate a particular index, as some of the data sets are quite large. So, is there any way to do this, or at least mimic this sort of behavior? Peter ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] disabling an index without deleting it?
On Tue, Feb 26, 2008 at 2:46 PM, Peter Koczan [EMAIL PROTECTED] wrote: This might be a weird question...is there any way to disable a particular index without dropping it? There are a few queries I run where I'd like to test out the effects of having (and not having) different indexes on particular query plans and performance. I'd really prefer not to have to drop and ultimately recreate a particular index, as some of the data sets are quite large. So, is there any way to do this, or at least mimic this sort of behavior? The brick to the head method would use set enable_indexscan = off; However, you can delete an index without actually deleting it like so: begin; drop index abc_dx; select rollback; and viola, your index is still there. note that there are likely some locking issues with this, so be careful with it in production. But on a test box it's a very easy way to test various indexes. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] disabling an index without deleting it?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 26 Feb 2008 14:57:51 -0600 Scott Marlowe [EMAIL PROTECTED] wrote: The brick to the head method would use set enable_indexscan = off; However, you can delete an index without actually deleting it like so: begin; drop index abc_dx; select rollback; and viola, your index is still there. note that there are likely some locking issues with this, so be careful with it in production. But on a test box it's a very easy way to test various indexes. Wouldn't you also bloat the index? Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHxH6rATb/zqfZUUQRAp//AJ4wKiA4WRprp3L3y9UEAzz2rb2+hACaA9b7 A1k3n6GkyFwx2vrbnpD8CX0= =zYaI -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] PG planning randomly ?
Laurent Raufaste [EMAIL PROTECTED] writes: 2008/2/26, Tom Lane [EMAIL PROTECTED]: ... I'm wondering if you have a definition of operator @ that doesn't specify the new selectivity estimator. Please try a pg_dump -s and see what it shows as the definition of @. Here's the first definition of the @ operator in my dump: CREATE OPERATOR @ ( PROCEDURE = ltree_risparent, LEFTARG = ltree, RIGHTARG = ltree, COMMUTATOR = @, RESTRICT = ltreeparentsel, JOIN = contjoinsel ); That's the right RESTRICT function, but what exactly did you mean by first definition? Are there more? It may be that it's just not possible for the estimator to come up with accurate rowcount estimates given the amount of info it has available. The query you are complaining about confuses the issue quite a lot by involving other issues. Would you try just explain analyze select 1 from _commment where path @ ''; for various typical path values, and see if it's coming up with estimated rowcounts that are in the right ballpark compared to the actual ones? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] disabling an index without deleting it?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 26 Feb 2008 17:22:40 -0500 Tom Lane [EMAIL PROTECTED] wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Scott Marlowe [EMAIL PROTECTED] wrote: begin; drop index abc_dx; select rollback; and viola, your index is still there. note that there are likely some locking issues with this, so be careful with it in production. But on a test box it's a very easy way to test various indexes. Wouldn't you also bloat the index? No, what makes you think that? Well now that I am obviously wrong :P. I was thinking about it from the: BEGIN; DELETE FROM ROLLBACK; Perspective. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHxJSyATb/zqfZUUQRAnSPAJkB6Gz0gUTPohXcFak9LbVYIdxCtwCfWvxp gQZymMaKEXfo2Mf1E2BWtUk= =p+EO -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] LISTEN / NOTIFY performance in 8.3
Tom Lane wrote: read-modify-write type of operation it uses an exclusive lock, so only one can clear its pg_listener entry at a time. The 'waiting' ones you are seeing are stacked up behind whichever one has the lock at the moment. They shouldn't be waiting for long. I certainly hadn't expected that to be the implementation technique - isn't it smply that we need a sngle flag per worker process and can set/test-and-clear with atomic operations and then a signal to wake them up? Anyway - how hard would it be to install triggers on commit and rollback? Then we could write our own mechanisms. James ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] LISTEN / NOTIFY performance in 8.3
Joel Stevenson [EMAIL PROTECTED] writes: What's really baffling is that there are plenty of other OLTP queries going in multiple backends simultaneously that don't fall over my 300ms query log threshold, and yet NOTIFY and LISTEN consistently do. What's more it's looks like it's only happening for registered listener relnames. Hmm, that says that it's not a matter of locking on pg_listener, but of actually applying the row update(s) and/or signaling the recipient(s). If you're not seeing performance issues for ordinary table-update operations it's hard to see why pg_listener updates would be any worse, so that seems to point the finger at the signaling. Which is just a matter of a kill(2) and shouldn't be that expensive. It might be interesting to try strace'ing the whole PG process tree while these notifies are going on, and seeing if you can identify any specific kernel calls that seem to take a long time. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] LISTEN / NOTIFY performance in 8.3
James Mansion [EMAIL PROTECTED] writes: I certainly hadn't expected that to be the implementation technique - isn't it smply that we need a sngle flag per worker process and can set/test-and-clear with atomic operations and then a signal to wake them up? Hardly --- how's that going to pass a notify name? Also, a lot of people want some payload data in a notify, not just a condition name; any reimplementation that doesn't address that desire probably won't get accepted. There's lots of threads in the -hackers archives about reimplementing listen/notify in a saner fashion. Personally I lean towards using something much like the sinval queue. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] disabling an index without deleting it?
2008/2/27, Tom Lane [EMAIL PROTECTED]: Joshua D. Drake [EMAIL PROTECTED] writes: Scott Marlowe [EMAIL PROTECTED] wrote: begin; drop index abc_dx; select rollback; and viola, your index is still there. note that there are likely some locking issues with this, so be careful with it in production. But on a test box it's a very easy way to test various indexes. Wouldn't you also bloat the index? No, what makes you think that? The index won't change at all in the above example. The major problem is, as Scott says, that DROP INDEX takes exclusive lock on the table so any other sessions will be locked out of it for the duration of your test query. Why is the exclusive lock not taken later, so that this method can be used reasonably risk-free on production systems? From what I understand the later would be either a statement that would (potentially) be modifying the index, like an UPDATE or an INSERT, or actual transaction commit. If none of these occur and the transaction is rollbacked, the exclusive lock doesn't have to be taken at all. Markus -- Markus Bertheau Blog: http://www.bluetwanger.de/blog/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] disabling an index without deleting it?
On Tue, Feb 26, 2008 at 8:48 PM, Markus Bertheau [EMAIL PROTECTED] wrote: 2008/2/27, Tom Lane [EMAIL PROTECTED]: Joshua D. Drake [EMAIL PROTECTED] writes: Scott Marlowe [EMAIL PROTECTED] wrote: begin; drop index abc_dx; select rollback; and viola, your index is still there. note that there are likely some locking issues with this, so be careful with it in production. But on a test box it's a very easy way to test various indexes. Wouldn't you also bloat the index? No, what makes you think that? The index won't change at all in the above example. The major problem is, as Scott says, that DROP INDEX takes exclusive lock on the table so any other sessions will be locked out of it for the duration of your test query. Why is the exclusive lock not taken later, so that this method can be used reasonably risk-free on production systems? From what I understand the later would be either a statement that would (potentially) be modifying the index, like an UPDATE or an INSERT, or actual transaction commit. If none of these occur and the transaction is rollbacked, the exclusive lock doesn't have to be taken at all. It would rock to be able to do that on a production database. Any Oracle DBA looking over your shoulder would fall to the floor and need resuscitation. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] disabling an index without deleting it?
Markus Bertheau [EMAIL PROTECTED] writes: 2008/2/27, Tom Lane [EMAIL PROTECTED]: No, what makes you think that? The index won't change at all in the above example. The major problem is, as Scott says, that DROP INDEX takes exclusive lock on the table so any other sessions will be locked out of it for the duration of your test query. Why is the exclusive lock not taken later, so that this method can be used reasonably risk-free on production systems? Er, later than what? Once the DROP is pending, other transactions can hardly safely use the index for lookups, and what should they do about insertions? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] disabling an index without deleting it?
On Tue, Feb 26, 2008 at 10:48 PM, Tom Lane [EMAIL PROTECTED] wrote: Markus Bertheau [EMAIL PROTECTED] writes: 2008/2/27, Tom Lane [EMAIL PROTECTED]: No, what makes you think that? The index won't change at all in the above example. The major problem is, as Scott says, that DROP INDEX takes exclusive lock on the table so any other sessions will be locked out of it for the duration of your test query. Why is the exclusive lock not taken later, so that this method can be used reasonably risk-free on production systems? Er, later than what? Once the DROP is pending, other transactions can hardly safely use the index for lookups, and what should they do about insertions? I see what you're saying. Sadly, my dreams of drop index concurrently appear dashed. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] disabling an index without deleting it?
Markus Bertheau [EMAIL PROTECTED] writes: On the other hand, if the only reason to have that feature is to plan and execute queries pretending that one index doesn't exist, then DROP INDEX DEFERRED is not the most straightforward syntax. Yeah, I was just about to mention that 8.3 has a hook that allows a plug-in to manipulate the planner's notions of which indexes exist. Ignoring a specific index would be really trivial. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] disabling an index without deleting it?
2008/2/27, Scott Marlowe [EMAIL PROTECTED]: On Tue, Feb 26, 2008 at 10:48 PM, Tom Lane [EMAIL PROTECTED] wrote: Markus Bertheau [EMAIL PROTECTED] writes: 2008/2/27, Tom Lane [EMAIL PROTECTED]: No, what makes you think that? The index won't change at all in the above example. The major problem is, as Scott says, that DROP INDEX takes exclusive lock on the table so any other sessions will be locked out of it for the duration of your test query. Why is the exclusive lock not taken later, so that this method can be used reasonably risk-free on production systems? Er, later than what? Once the DROP is pending, other transactions can hardly safely use the index for lookups, and what should they do about insertions? I see what you're saying. Sadly, my dreams of drop index concurrently appear dashed. Maybe a different syntax: DROP INDEX DEFERRED, which will make the current transaction behave as if the index was dropped but not actually drop it until the end of the transaction. Inserts and updates of this and other transactions behave as if the index existed. On the other hand, if the only reason to have that feature is to plan and execute queries pretending that one index doesn't exist, then DROP INDEX DEFERRED is not the most straightforward syntax. Markus -- Markus Bertheau Blog: http://www.bluetwanger.de/blog/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] LISTEN / NOTIFY performance in 8.3
Tom Lane wrote: Hardly --- how's that going to pass a notify name? Also, a lot of people want some payload data in a notify, not just a condition name; any reimplementation that doesn't address that desire probably won't get accepted. Ah - forgot about the name. At least there need be just one instance of a name record queued per worker if I'm reading the documentation right - it suggest that notifications can be folded with the proviso that if the process generates a notification and at least one other process generates a notification then it will get at least (but possibly only) two events. Not sure why the PID is there rather than a couple of flag bits. You'll alsways have the danger of overflowing a shm area and need to spill: is the signal and then lookup in storage materially quicker than using the master process to route messages via pipes? As you say, you have a lock contention issue and often the total signal data volume outstanding for a single back end will be less than will fit in a kernel's pipe buffer. The sending processes can track what signals they've generated in the current transaction so the master (or signal distributor) needn't get bombarded with signals from lots of rows within one transaction. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
