Re: [PERFORM]Is it possible to start two instances of postgresql?
[EMAIL PROTECTED] wrote: both of the two database are live but use for two different web app. my company don't want to spend more to buy a new server, so then I think of to implement both under the same server and one instance.. but then my superior don't want to do that way. they want to implement two databases in one server but if one of the database down it will not affect the other, so that's why I need to have two instances. We are currently running your suggestion (two instances of PG) in a production server, with no obvious problems attributable to the setup (we have seen some performance problems with one system, but those are likely caused by bad db/application design). In our case the two systems are running different minor versions (although we are planning to migrate them both to the latest 7.4.x). /Nis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
J. Andrew Rogers wrote: We have been using PostgreSQL on Opteron servers almost since the Opteron was first released, running both 32-bit and 64-bit versions of Linux. Both 32-bit and 64-bit versions have been bulletproof for us, with the usual stability I've become accustomed to with both PostgreSQL and Linux. We have been running nothing but 64-bit versions on mission-critical systems for the last year with zero problems. The short story is that for us 64-bit PostgreSQL on Opterons is typically something like 20% faster than 32-bit on the same, and *much* faster than P4 Xeon systems they nominally compete with. Since you sound like you have done extensive testing: Do you have any data regarding whether to enable hyperthreading or not? I realize that this may be highly dependant on the OS, application and number of CPUs, but I would be interested in hearing your recommendations (or others'). /Nis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Speedup hint needed, if available? :)
Mario Splivalo wrote: Hello again. I have to track user subscriptions to certain mailinglists, and I also need to track credits users have on those mailinglists. On one side I have procedures that add credits, on other side I have procedures that subtract available credits. Add/subtract is pretty intensive, around 30-50 adds per minute (usualy 10 or 100 credits), and around 200-500 subtracts per minute (usualy by one or two credits). I have created table user_subscriptions to track user subscriptions to certain mailing list. I have derived subscription_id as primary key. I have two other tables, user_subscription_credits_given, and _credits_taken, wich track credits for subscription added or subtracted to or from certain subscription. I created those two tables so I could eliminate a lot of UPDATES on user_subscriptions table (if I were to have a column 'credits' in that table). It sounds to me like you have decided beforehand that the obvious solution (update a credit field in the user_subscriptions table) is not going to perform well. Have you tried it? How does it perform? If it does indeed give you performance problems, you could instead run some kind of batch job to update the credits field (and delete the /given/taken records). Finally: You could refactor the query to get rid of the union: SELECT u.subscription_id, u.user_id, u.mailinglist_id, u.valid_from, u.valid_to, ( SELECT sum(credits) FROM credits_given WHERE subscription_id = u.subscription_id ) - ( SELECT sum(credits) FROM credits_taken WHERE subscription_id = u.subscription_id) ) AS credits FROM user_subscriptions u WHERE u.user_id = 1 (Not tested). You will probably need a COALESCE around each of the subqueries to avoid problems with nulls. rantThe sum of an empty set of numbers is 0. The conjunction of an empty set of booleans is true. The SQL standard somehow manages to get this wrong/rant /Nis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Question about explain-command...
I will try answering your questions. Please note that I am a newbie myself. Clemens Eisserer wrote All primary keys are indixed, and this is what explain tells me: Unique (cost=15.67..16.69 rows=34 width=115) - Sort (cost=15.67..15.75 rows=34 width=115) Sort Key: customer.email, customer.key, customer.anrede, customer.str asse, customer.plz, customer.ort, customer.vorname, customer.nachname, custtype. name, customer.land, customer.datanotvalid - Hash Left Join (cost=6.16..14.80 rows=34 width=115) Hash Cond: (outer.prodgroup = inner.key) - Hash Left Join (cost=4.97..13.10 rows=34 width=119) Hash Cond: (outer.custgroup = inner.key) - Hash Left Join (cost=3.88..11.49 rows=34 width=111) Hash Cond: (outer.goodsid = inner.key) - Hash Left Join (cost=1.98..9.08 rows=34 width=111) Hash Cond: (outer.key = inner.custid) - Seq Scan on customer (cost=0.00..6.10 rows=34 width=107) Filter: ((nachname)::text ~~ '%au%'::text) - Hash (cost=1.78..1.78 rows=78 width=8) - Seq Scan on sells (cost=0.00..1.78 rows=78 width=8) - Hash (cost=1.72..1.72 rows=72 width=8) - Seq Scan on goods (cost=0.00..1.72 rows=72 width=8) - Hash (cost=1.08..1.08 rows=8 width=16) - Seq Scan on custtype (cost=0.00..1.08 rows=8 width=16) - Hash (cost=1.15..1.15 rows=15 width=4) - Seq Scan on prodtype (cost=0.00..1.15 rows=15 width=4) What does the hash-lines mean, does that mean my query does not use the indices at all? Yes. Probably each table fits nicely into a single disk read, so reading both the index AND the table is going to be twice as expensive. Why are some table-names and some column-names surrounded by ' '? Are they threated as text-columns? They are either names generated by postgres (outer and inner) or field names which are also reserved words in SQL (key). You can always use double quotes around a field name - you have to in some cases if they are reserved words, and always if they contain special characters (not sure from memory exactly which these are - at least spaces). I recommend not to use either of these, even if a reserved word is the best description of your field. Postgres seems to be a bit better than some other dbms's in allowing unquoted reserved words as field names if there is no ambiguity. Thsis may mean that you get a problem if your application is ever ported to a different dbms. I have to admit that the tables are just filled with test-data so the analyzer may take just a very simple way since almost no data is in... Try loading your tables with a realistic number of customers, and you should see a change in the query plan to use your precious indexes. /Nis ---(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: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal
Martijn van Oosterhout wrote: On Wed, May 10, 2006 at 04:38:31PM +0200, PFC wrote: You need to do some processing to know how many rows the function would return. Often, this processing will be repeated in the function itself. Sometimes it's very simple (ie. the function will RETURN NEXT each element in an array, you know the array length...) Sometimes, for functions returning few rows, it might be faster to compute the entire result set in the cost estimator. I think the best would probably be to assign a constant. An SRF will generally return between one of 1-10, 10-100, 100-1000, etc. You don't need exact number, you just need to get within an order of magnitude and a constant will work fine for that. How many functions sometimes return one and sometimes a million rows? It will probably be quite common for the number to depend on the number of rows in other tables. Even if this is fairly constant within one db (some assumption), it is likely to be different in others using the same function definition. Perhaps a better solution would be to cache the result of the estimator function. /Nis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
