Re: [PERFORM] Speedier count(*)
Am Donnerstag, den 11.08.2005, 00:40 -0400 schrieb Mark Cotner:
Here's a trigger I wrote to perform essentially the same purpose. The nice
thing about this is it keeps the number up to date for you, but you do incur
slight overhead.
...
CREATE TRIGGER del_rowcount_tr BEFORE DELETE ON test FOR EACH ROW EXECUTE
PROCEDURE del_rowcount();
CREATE TRIGGER add_rowcount_tr BEFORE INSERT ON test FOR EACH ROW EXECUTE
PROCEDURE add_rowcount();
INSERT INTO rowcount (tablename) VALUES ('test');
...
beware of problems with concurrency and even what happens
if transactions roll back. Maybe you can fix it a bit
by regulary correcting the count via cronjob or so.
---(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] Speedier count(*)
Am Donnerstag, den 11.08.2005, 20:36 +1000 schrieb Gavin Sherry:
On Thu, 11 Aug 2005, Tino Wildenhain wrote:
Am Donnerstag, den 11.08.2005, 00:40 -0400 schrieb Mark Cotner:
Here's a trigger I wrote to perform essentially the same purpose. The
nice
thing about this is it keeps the number up to date for you, but you do
incur
slight overhead.
...
CREATE TRIGGER del_rowcount_tr BEFORE DELETE ON test FOR EACH ROW EXECUTE
PROCEDURE del_rowcount();
CREATE TRIGGER add_rowcount_tr BEFORE INSERT ON test FOR EACH ROW EXECUTE
PROCEDURE add_rowcount();
INSERT INTO rowcount (tablename) VALUES ('test');
...
beware of problems with concurrency and even what happens
if transactions roll back. Maybe you can fix it a bit
by regulary correcting the count via cronjob or so.
What problems? MVCC takes care of this.
Actually in this case MVCC works against you.
Just imagine some competing transactions to insert
end delete at will.
You could lock the count table to prevent the problem
where 2 competing transactions do an insert, read the
start value and add 1 to it and then write the result
- which is n+1 rather then n+2 - so you are off by one.
Think of the same when one transaction inserts 100
and the other 120. Then you could even be off by 100.
But locking probably gets your worser performance then
simply count(*) all the time if you insert a lot. Also
prepare for the likeness of deadlocks.
---(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] Speedier count(*)
Am Donnerstag, den 11.08.2005, 14:08 +0200 schrieb PFC: You could lock the count table to prevent the problem where 2 competing transactions do an insert, read the start value and add 1 to it and then write the result - which is n+1 rather then n+2 - so you are off by one. Think of the same when one transaction inserts 100 and the other 120. Then you could even be off by 100. Niet. If your trigger does UPDATE counts_cache SET cached_count = cached_count+N WHERE ... Then all locking is taken care of by Postgres. Of course if you use 2 queries then you have locking issues. Yes, in the case you use just the UPDATE statement you are right. This does the locking I was talking about. In either case I'd use an after trigger and not before to minimize the impact. However the UPDATE counts_cache has a problem, ie. it locks this row FOR UPDATE for the whole transaction, and all transactions which want to update the same row must wait to see if the update commits or rollbacks, so if you have one count cache row for the whole table you get MySQL style scalability... To preserve scalability you could, instead of UPDATE, INSERT the delta of rows inserted/deleted in a table (which has no concurrencies issues) and compute the current count with the sum() of the deltas, then with a cron, consolidate the deltas and update the count_cache table so that the deltas table stays very small. Yes, this is in fact a better approach to this problem. (All this provided you want an unqualified count() - as the original poster) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Looking for a large database for testing
Sebastian Hennebrueder schrieb: Hello, I would like to test the performance of my Java/PostgreSQL applications especially when making full text searches. For this I am looking for a database with 50 to 300 MB having text fields. e.g. A table with books with fields holding a comment, table of content or example chapters or what ever else. Does anybody have an idea where I can find a database like this or does even have something like this? You can download the wikipedia content. Just browse the wikimedia site. Its some work to change the data to be able to import into postgres, but at least you have a lot real world data - in many languages. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] 15,000 tables
Am Donnerstag, den 01.12.2005, 10:07 -0800 schrieb Gavin M. Roy: Hi Michael, I'm a fan of ReiserFS, and I can be wrong, but I believe using a journaling filesystem for the PgSQL database could be slowing things down. Have a 200G+ database, someone pulling the power plug or a regular reboot after a year or so. Wait for the fsck to finish. Now think again :-) ++Tino ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [GENERAL] need help
Jenny schrieb: I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been dealing with Psql for over than 2 years now, but I've never had this case before. I have a table that has about 20 rows in it. Table public.s_apotik Column | Type| Modifiers ---+--+-- obat_id| character varying(10)| not null stock | numeric | not null s_min | numeric | not null s_jual | numeric | s_r_jual | numeric | s_order| numeric | s_r_order | numeric | s_bs | numeric | last_receive | timestamp without time zone | Indexes: s_apotik_pkey PRIMARY KEY, btree(obat_id) When I try to UPDATE one of the row, nothing happens for a very long time. First, I run it on PgAdminIII, I can see the miliseconds are growing as I waited. Then I stop the query, because the time needed for it is unbelievably wrong. Then I try to run the query from the psql shell. For example, the table has obat_id : A, B, C, D. db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='A'; ( nothing happens.. I press the Ctrl-C to stop it. This is what comes out :) Cancel request sent ERROR: canceling query due to user request (If I try another obat_id) db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='B'; (Less than a second, this is what comes out :) UPDATE 1 I can't do anything to that row. I can't DELETE it. Can't DROP the table. I want this data out of my database. What should I do? It's like there's a falsely pointed index here. Any help would be very much appreciated. 1) lets hope you do regulary backups - and actually tested restore. 1a) if not, do it right now 2) reindex the table 3) try again to modify Q: are there any foreign keys involved? If so, reindex those tables too, just in case. did you vacuum regulary? HTH Tino ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Can this query go faster???
Joost Kraaijeveld schrieb: On Tue, 2005-12-06 at 10:52 +0100, Csaba Nagy wrote: Joost, Why do you use an offset here ? I guess you're traversing the table somehow, in this case it would be better to remember the last zipcode + housenumber and put an additional condition to get the next bigger than the last one you've got... that would go for the index on zipcode+housenumber and be very fast. The big offset forces postgres to traverse that many entries until it's able to pick the one row for the I am forced to translate a sorting dependent record number to a record in the database. The GUI (a Java JTable) works with record /row numbers, which is handy if one has an ISAM database, but not if one uses PostgreSQL. You can have a row number in postgres easily too. For example if you just include a serial for the row number. Cursor would work too but you would need to have a persistent connection. Regards Tino ---(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] Can this query go faster???
Joost Kraaijeveld schrieb: Hi Tino, .. Cursor would work too but you would need to have a persistent connection. I just tried it: a cursor is not faster (what does not surprise me at all, as the amount of work looks the same to me) Actually no, if you scroll forward, you just ask the database for the next rows to materialize. So if you are ahead in your database and ask for next rows, it should be faster then working w/ an offset from start each time. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Can this query go faster???
Joost Kraaijeveld schrieb: On Tue, 2005-12-06 at 12:36 +0100, Tino Wildenhain wrote: I just tried it: a cursor is not faster (what does not surprise me at all, as the amount of work looks the same to me) Actually no, if you scroll forward, you just ask the database for the next rows to materialize. So if you are ahead in your database and ask for next rows, it should be faster then working w/ an offset from start each time. Ah, a misunderstanding: I only need to calculate an index if the user wants a record that is not in or adjacent to the cache (in which case I can do a select values last value in the cache. So I must always materialize all rows below the wanted index. Yes, but still advancing a few blocks from where the cursor is should be faster then re-issuing the query and scroll thru the whole resultset to where you want to go. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Suspending SELECTs
Alessandro Baretta schrieb: [EMAIL PROTECTED] wrote: ... It looks like this is the only possible solution at present--and in the future, too--but it has a tremendouse performance impact on queries returning thousands of rows. Well actually one of the better solutions would be persistent cursors (and therefore connection pooling). I bet this is easier then fiddling with the problems of offset/limit and inventing even more compex caching in the application. Just my 0.02c ++Tino ---(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] fsync and battery-backed caches
Javier Somoza schrieb: Hi all Is it secure to disable fsync havin battery-backed disk cache? Thx No. fsync moves the data from OS memory cache to disk-adaptor cache which is required to benefit from battery backup. If this data is written to the plates immediately depends on settings of your disk adaptor card. Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] in memory views
Thomas Vatter schrieb: is there a possibility for creating views or temp tables in memory to avoid disk io when user makes select operations? No need. The data will be available in OS and database caches if they are really required often. If not, tune up the caches and do a regular pre select. Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] in memory views
Thomas Vatter schrieb: Tino Wildenhain wrote: Thomas Vatter schrieb: is there a possibility for creating views or temp tables in memory to avoid disk io when user makes select operations? No need. The data will be available in OS and database caches if they are really required often. If not, tune up the caches and do a regular pre select. Regards Tino hmm, I am selecting a resultset with 1300 rows joined from 12 tables. with jdbc I am waiting 40 seconds until the first row appears. The following rows appear really fast but the 40 seconds are a problem. Well you will need the equally 40 seconds to fill your hypothetical in memory table. (even a bit more due to the creation of a datastructure). So you can do the aproaches of semi materialized views (that are in fact writing into a shadow table) or just prefetch your data at time - just at the times you would refill your memory tables if they existed. A cronjob with select/fetch should do. Regards Tino ---(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] in memory views
Thomas Vatter schrieb: Tino Wildenhain wrote: ... Well you will need the equally 40 seconds to fill your hypothetical in memory table. (even a bit more due to the creation of a datastructure). So you can do the aproaches of semi materialized views (that are in fact writing into a shadow table) or just prefetch your data at time - just at the times you would refill your memory tables if they existed. A cronjob with select/fetch should do. Regards Tino If the in memory table is created a bootup time of the dbms it is already present when user selects the data. Of course the challenge is to keep the in memory table up to date if data are changed. What do you mean with semi materialized views, I have tried select * from this_view with the same result. Also, if I repeat the query it does not run faster. Semi materialized views are just views with aditional rules and some triggers which copy data to another table. There are several receipes if you google accordingly. I do not know what you mean by bootup time - do you really reboot your database server? *hehe* just kidding ;) In your first email you told me your query indeed runs faster the 2nd time (due to the caching) now you are telling me that it is not. Btw, judging from your analyze output you are using very cryptic table and column names - you can use aliasing in the query and dont have to resort to tiny tags when you actually name the objects ;) Maybe others have comments on your query. Btw, better use explain analyze to get realistic results. Regards Tino ---(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] Kill a session
Craig A. James wrote: There have been dozens, perhaps hundreds, of entries in the pg-admin, pg-general, and pg-performance lists regarding killing a session, but as far as I can tell, there is no Postgres solution. Did I miss something? This raises the question: Why doesn't Postgres have a kill session command that works? Oracle has it, and it's invaluable; there is no substitute. Various writers to these PG lists have raised the question repeatedly. Is it just a matter that nobody has had the time to do it (which I respect!), or is there a reason why the Postgres team decided a kill session is a bad idea? You are sure you read: http://www.postgresql.org/docs/8.1/interactive/protocol-flow.html#AEN60635 ? Regards Tino Wildenhain ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance of count(*)
Craig A. James schrieb: ... In our case (for a variety of reasons, but this one is critical), we actually can't use Postgres indexing at all -- we wrote an entirely separate indexing system for our data, one that has the following properties: 1. It can give out pages of information (i.e. rows 50-60) without rescanning the skipped pages the way limit/offset would. 2. It can give accurate estimates of the total rows that will be returned. 3. It can accurately estimate the time it will take. Thats certainly not entirely correct. There is no need to store or maintain this information along with postgres when you can store and maintain it directly in postgres as well. When you have some outside application I think I can savely assume you are doing less updates compared to many reads to have it actually pay out. So why not store this information in separate index and statistic tables? You would have just to join with your real data for retrival. On top of that, postgres has a very flexible and extensible index system. This would mean you save on database roundtrips and double information storage (and the sync problems you certainly get from it) Regards Tino ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance of count(*)
Craig A. James schrieb: Tino Wildenhain wrote: Craig A. James schrieb: ... In our case (for a variety of reasons, but this one is critical), we actually can't use Postgres indexing at all -- we wrote an entirely separate indexing system for our data... ...There is no need to store or maintain this information along with postgres when you can store and maintain it directly in postgres as well. Whether we store our data inside or outside Postgres misses the point (in fact, most of our data is stored IN Postgres). It's the code that actually performs the index operation that has to be external to Postgres. On top of that, postgres has a very flexible and extensible index system. You guys can correct me if I'm wrong, but the key feature that's missing from Postgres's flexible indexing is the ability to maintain state across queries. Something like this: select a, b, my_index_state() from foo where ... offset 100 limit 10 using my_index(prev_my_index_state); Yes, you are wrong :-) The technique is called CURSOR if you maintain persistent connection per session (e.g. stand allone application or clever pooling webapplication) If its a naive web application you just store your session in tables where you can easily maintain the scroll state as well. Regards Tino ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance of count(*)
Michael Stone schrieb: On Thu, Mar 22, 2007 at 02:24:39PM -0400, Merlin Moncure wrote: Tino was saying that rather that build a complete indexing storage management solution that lives outside the database, it is better to do intelligent session management so that you get the simplicity if a two tier client server system but the scalability of a web app. No, what he was saying was there's this thing called a cursor. I thought there was enough information in the original message to indicate that the author knew about cursors. There are certainly pros and cons and nuances to different approaches, but Tino's message didn't touch on anything that specific. Sure, the message thread sometimes loose history so I wasnt 100% sure what the framework really is - although I assumed it could be a web solution. With stand alone applications you usually have a limited number of users connecting and they are connected during the session so you can easily use cursors there. And even if you do use some kind of intelligent session management, how many simultaneous cursors can postgres sanely keep track of? Thousands? Millions? Tens of Millions? I suspect there's a scalability limit in there somewhere. Luckily I don't spend much time in the web application space, so I don't need to know. :) Depending on the application, you can even simulate above situation with a web framework if you manage session in the web framework with persistent connections for a limited amount of users to work the same time (certainly not feasable for a public web shop but for data management systems for inhouse use). In this case, cursors would be perfect too. In any other case I fail to see the advantage in storing index data outside the database with all the roundtripping involved. If the query is complex and rerunning it for every batch is expensive, fetching the whole result to the application in case of users really traversing the complete batch (How often is that really done? I mean, who browses to an end of a huge result set?) is costy as well w/o really benefit. It would be much more easy and clean imho, in this case to really fetch the data to session and batch linked scratch table. If its fast or you can prepare a batch helper table with index, you can just select the batch equival portion of the result. You dont need extensive session management in the web application to scroll thru result sets in this way. This can all be encoded in forms or links. Regards Tino ---(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] Performance of count(*)
Craig A. James schrieb: Tino Wildenhain wrote: You guys can correct me if I'm wrong, but the key feature that's missing from Postgres's flexible indexing is the ability to maintain state across queries. Something like this: select a, b, my_index_state() from foo where ... offset 100 limit 10 using my_index(prev_my_index_state); Yes, you are wrong :-) The technique is called CURSOR if you maintain persistent connection per session (e.g. stand allone application or clever pooling webapplication) That's my whole point: If relational databases had a simple mechanism for storing their internal state in an external application, the need for cursors, connection pools, and all those other tricks would be eliminated. Well the cursor is exactly the simple handle to the internal state of the relational db you are looking for. Do you really think transferring the whole query-tree, open index and data files to the client over the network would really improve the situation? As I said earlier, relational technology was invented in an earlier era, and hasn't caught up with the reality of modern web apps. There is nothing modern with todays web apps. If its a naive web application you just store your session in tables where you can easily maintain the scroll state as well. One thing I've learned in 25 years of software development is that people who use my software have problems I never imagined. I've been the one who was naive when I said similar things about my customers, and was later embarrassed to learn that their problems were more complex than I ever imagined. Sure it really depends on the application how the best solution would look like but I'm quite certain, counterfaiting internal stuff of the underlying relational database in the application makes more problems then it solves. If you can't handle SQL, dont use SQL, you can build web applications w/o any relational database if you want it. Regards Tino Wildenhain ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance of count(*)
Michael Stone schrieb: On Thu, Mar 22, 2007 at 06:27:32PM +0100, Tino Wildenhain wrote: Craig A. James schrieb: You guys can correct me if I'm wrong, but the key feature that's missing from Postgres's flexible indexing is the ability to maintain state across queries. Something like this: select a, b, my_index_state() from foo where ... offset 100 limit 10 using my_index(prev_my_index_state); Yes, you are wrong :-) The technique is called CURSOR if you maintain persistent connection per session (e.g. stand allone application or clever pooling webapplication) Did you read the email before correcting it? From the part you trimmed out: The problem is that relational databases were invented before the web and its stateless applications. In the good old days, you could connect to a database and work for hours, and in that environment cursors and such work well -- the RDBMS maintains the internal state of the indexing system. But in a web environment, state information is very difficult to maintain. There are all sorts of systems that try (Enterprise Java Beans, for example), but they're very complex. Yes, but actually this is not true. They are not so complex in this regard. All you have to do is to look in the pg_cursor view if your cursor is there and if not, create it in your session. All you need to maintain is the cursor name which maps to your session + the special query you run. This should be easy in any web application. It sounds like they wrote their own middleware to handle the problem, which is basically what you suggested (a clever pooling web application) after saying wrong. I read about building index data outside postgres which still is the wrong approach imho. This discussion is a bit theoretical until we see the actual problem and the proposed solution here. Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance of count(*)
Michael Stone schrieb: On Fri, Mar 23, 2007 at 01:01:02PM +0100, Tino Wildenhain wrote: This discussion is a bit theoretical until we see the actual problem and the proposed solution here. It's good to see you back off a bit from your previous stance of assuming that someone doesn't know what they're doing and that their solution is absolutely wrong without actually knowing anything about what they are trying to do. Well I'm sure its very likely wrong :-) At least the core part of it with the statement of keeping index data outside postgres. What I meant with my comment about the theoreticalness: we cannot make educated suggestions about alternative solutions to the problem until we know the problem and maybe the current solution in detail. Regards Tino ---(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] Apache2 PostgreSQL http authentication
Magnus Hagander schrieb: I'd consider having a small daemon LISTENing for NOTIFYs that you send by triggers whenever the table has changed. That'll make sure it only dumps if something actually changed. And you can also implement some ratelimiting if needed. Do you really think such a homegrown solution will be more performant then just accessing postgres? If you have many users the lookup time in a .htaccess/.htpasswd is not for free either. Regards Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Example web access to Postgres DB
Mohamed Ali JBELI wrote: Hi members I am looking for an example of a web application (WAR) which executea a Postgres actions. This aims to test the performance of Postgres in Web mode. I shall be grateful if someone gives me a link where I can find a WAR file. A WAR file? Postgres is peace, not war ;) Seriously, for postgres to be used for web applications (as nowadays RPC-Server with XML over HTTP are commonly named ;) you need to settle for an application server (available for many technologies - choose whatever you feel comfortable with) which then connects back to postgres. Regards Tino ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] count * performance issue
Hi, Matthew wrote: On Tue, 11 Mar 2008, Bill Moran wrote: In response to Robins Tharakan [EMAIL PROTECTED]: Sorry, if I am missing something here, but shouldn't something like this allow us to get a (fast) accurate count ? SELECT COUNT(*) from table WHERE indexed_field IS NULL + SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL For certain, qualified definitions of fast, sure. And certain, qualified definitions of accurate as well. Race condition? You mean in a three-state-logic? null, not null and something different? ;-) Tino -- 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] Benchmark: Dell/Perc 6, 8 disk RAID 10
Justin wrote: OK i'm showing my ignorance of linux. On Ubuntu i can't seem to figure out if XFS file system is installed, if not installed getting it installed. Hm? Installed/not installed? You can select that when you are preparing your partitions. If you run the automated partitioner there is of course not much choice but you can try the manual mode. Even after that you can format individual partitions with XFS if you want. XFS is long since included in the recent linux kernels, also there is raiserfs if you feel desperate (well in fact raiser fs is ok too but you should not use it on flaky hardware). Both xfs and raiser are designed for journaling - it is believed that xfs performs better with large files and raiser good with many small files (like Maildir for example). I'd suggest a test with your data and workload to be sure. Regards Tino -- 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] Best practice to load a huge table from ORACLE to PG
Adonias Malosso wrote: Hi All, I´d like to know what´s the best practice to LOAD a 70 milion rows, 101 columns table from ORACLE to PGSQL. The current approach is to dump the data in CSV and than COPY it to Postgresql. Uhm. 101 columns you say? Sounds interesting. There are dataloaders like: http://pgfoundry.org/projects/pgloader/ which could speed up loading the data over just copy csv. I wonder how much normalizing could help. Tino -- 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] Please ignore ...
Hi all the ignorers, ;) Greg Smith wrote: On Thu, 1 May 2008, D'Arcy J.M. Cain wrote: Whenever I see one of those I simply blackhole the server sending them. Ah, the ever popular vigilante spam method. What if the message is coming from, say, gmail.com, and it's getting routed so that you're not sure which account is originating it? Do you blackhole everybody on *that* server just because there's one idiot? This is the same problem on a smaller scale. It's not clear which account is reponsible, and I believe I saw that there are other people using the same ISP who also subscribe to the list. That's why Marc is testing who the guilty party is rather than unsubscribing everyone there. yes, blackholing is bad as well as accepting everything and then sending out errors. Unfortunaly, email resembles the ideas of the decade when it was invented (freedom of speach over regulating) so security is only available as ad on. I wish however everybody would go by cryptography, meaning in our case the sender signs and the list checks (1) and also the list signs (2) when sending out, which makes it easy to check for the receiver if to accept the mail or decline in band... Cheers Tino PS: happy 1st of may :-) smime.p7s Description: S/MIME Cryptographic Signature
Re: [PERFORM] I/O on select count(*)
Hi, Luke Lonergan wrote: BTW – we’ve removed HINT bit checking in Greenplum DB and improved the visibility caching which was enough to provide performance at the same level as with the HINT bit optimization, but avoids this whole “write the data, write it to the log also, then write it again just for good measure” behavior. can you go a bit deeper into how you implemented this or is it some IP of greenplum you cannot reveal? Btw, is there something with your eyes: FONT SIZE=4FONT FACE=Verdana, Helvetica, ArialSPAN STYLE='font-size:14pt' ? :-)) Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [PERFORM] NOW vs CURRENT_DATE
Hi, dforum wrote: Hello every body, I just discover a big not only big huge difference between NOW() and CURRENT_DATE. Did you already know about it and do you know why ? DELETE FROM blacklist where bl_date (NOW() - interval '2 DAY'); on 6 000 000 of records 699 ms DELETE FROM blacklist where bl_date (CURRENT_DATE - interval '2 DAY'); on 6 000 000 of records Try that with a timestamp - column and use now() and current_timestamp with a long running query and then compare min(column) max(column) in both cases :-) Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
