Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.
Possibly. A lot of my queries show comparable performance, some a little slower and a few a little faster. There are a few, however, that really grind on PostgreSQL. I am leaning patterns from these to try and and target the most likely performance problems to come and hand tune these types of SQL. I'm not complaining about PostgreSQL or saying that SQLServer is better, in most cases it is not. SQLServer seems to be more predictable and forgiving in performance which tends to make for lazy SQL programming. It also has implications when the SQL is dynamically created based on user input, there are more chances of PostgreSQL hitting a performance problem than SQLServer. Overall I'm still very impressed with PostgreSQL. Given the $7000 per processor licence for SQLServer makes the case for PostgreSQL even stronger! Cheers, Gary. On 3 Apr 2004 at 17:43, Aaron Werman wrote: Almost any cross dbms migration shows a drop in performance. The engine effectively trains developers and administrators in what works and what doesn't. The initial migration thus compares a tuned to an untuned version. /Aaron - Original Message - From: Josh Berkus [EMAIL PROTECTED] To: Gary Doades [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, April 03, 2004 1:59 PM Subject: Re: [PERFORM] PostgreSQL and Linux 2.6 kernel. Gary, There are no indexes on the columns involved in the update, they are not required for my usual select statements. This is an attempt to slightly denormalise the design to get the performance up comparable to SQL Server 2000. We hope to move some of our databases over to PostgreSQL later in the year and this is part of the ongoing testing. SQLServer's query optimiser is a bit smarter that PostgreSQL's (yet) so I am hand optimising some of the more frequently used SQL and/or tweaking the database design slightly. Hmmm ... that hasn't been my general experience on complex queries. However, it may be due to a difference in ANALYZE statistics. I'd love to see you increase your default_stats_target, re-analyze, and see if PostgreSQL gets smarter. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Incoming mail is certified Virus Free. Checked by AVG Anti-Virus (http://www.grisoft.com). Version: 7.0.230 / Virus Database: 262.6.5 - Release Date: 31/03/2004 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.
Unfortunately I don't understand the question! My background is the primarily Win32. The last time I used a *nix OS was about 20 years ago apart from occasional dips into the linux OS over the past few years. If you can tell be how to find out what you want I will gladly give you the information. Regards, Gary. On 3 Apr 2004 at 16:52, Cott Lang wrote: On Sat, 2004-04-03 at 03:50, Gary Doades wrote: On 2 Apr 2004 at 22:36, [EMAIL PROTECTED] wrote: OK, some more detail: Before wiping 2.4 off my test box for the second time: Perhaps I missed it, but which io scheduler are you using under 2.6? ---(end of broadcast)--- TIP 3: 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 -- Incoming mail is certified Virus Free. Checked by AVG Anti-Virus (http://www.grisoft.com). Version: 7.0.230 / Virus Database: 262.6.5 - Release Date: 31/03/2004 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.
Unfortunately I have to try and keep both SQLServer and PostgreSQL compatibilty. Our main web application is currently SQLServer, but we want to migrate customers who don't care what the DB server is over to PostgreSQL. Some of our larger customers demand SQLServer, you know how it is! I don't want to maintain two sets of code or SQL, so I am trying to find common ground. The code is not a problem, but the SQL sometimes is. Cheers, Gary. On 3 Apr 2004 at 17:43, Aaron Werman wrote: Almost any cross dbms migration shows a drop in performance. The engine effectively trains developers and administrators in what works and what doesn't. The initial migration thus compares a tuned to an untuned version. /Aaron - Original Message - From: Josh Berkus [EMAIL PROTECTED] To: Gary Doades [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, April 03, 2004 1:59 PM Subject: Re: [PERFORM] PostgreSQL and Linux 2.6 kernel. Gary, There are no indexes on the columns involved in the update, they are not required for my usual select statements. This is an attempt to slightly denormalise the design to get the performance up comparable to SQL Server 2000. We hope to move some of our databases over to PostgreSQL later in the year and this is part of the ongoing testing. SQLServer's query optimiser is a bit smarter that PostgreSQL's (yet) so I am hand optimising some of the more frequently used SQL and/or tweaking the database design slightly. Hmmm ... that hasn't been my general experience on complex queries. However, it may be due to a difference in ANALYZE statistics. I'd love to see you increase your default_stats_target, re-analyze, and see if PostgreSQL gets smarter. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: 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 -- Incoming mail is certified Virus Free. Checked by AVG Anti-Virus (http://www.grisoft.com). Version: 7.0.230 / Virus Database: 262.6.5 - Release Date: 31/03/2004 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.
On 04/04/2004 09:56 Gary Doades wrote: Unfortunately I don't understand the question! My background is the primarily Win32. The last time I used a *nix OS was about 20 years ago apart from occasional dips into the linux OS over the past few years. If you can tell be how to find out what you want I will gladly give you the information. Googling threw up http://spider.tm/apr2004/cstory2.html Interesting and possibly relevant quote: Benchmarks have shown that in certain conditions the anticipatory algorithm is almost 10 times faster than what 2.4 kernel supports. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Substitute for this oracle query in postGre
Hi, Try looking at the contrib/tablefunc add-in module. Chris Kamalraj Singh Madhan wrote: Hi Friends, Does anybody know the substitute of the oracle function 'connect by prior' in postgre sql. The query is basically being used to get a tree structure of records. The query in oracle is :- select pkmsgid from mstmessage connect by prior pkmsgid = msgparentid start with msgparentid = 1 Kindly suggest. regards Kamal * Network Programs is a SEI CMM Level 5 Certified Company The information contained in this communication (including any attachments) is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. It may contain confidential or legally privileged information. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. Network Programs (India) Limited is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. * ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: 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] PostgreSQL and Linux 2.6 kernel.
On Sun, 2004-04-04 at 01:56, Gary Doades wrote: Unfortunately I don't understand the question! My background is the primarily Win32. The last time I used a *nix OS was about 20 years ago apart from occasional dips into the linux OS over the past few years. If you can tell be how to find out what you want I will gladly give you the information. There are two available io schedulers in 2.6 (new feature), deadline and anticipatory. It should show be listed in the boot messages: dmesg | grep scheduler I've seen people arguing for each of the two schedulers, saying one is better than the other for databases. I'm curious which one you're using. :) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] single index on more than two coulumns a bad thing?
Hi Aaron, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Aaron Werman Sent: vrijdag 2 april 2004 13:57 another thing that I have all over the place is a hierarchy: index on grandfather_table(grandfather) index on father_table(grandfather, father) index on son_table(grandfather, father, son) It depends on your data-distribution, but I find that in almost all cases it's beneficial to have your indexes the other way round in such cases: index on grandfather_table(grandfather) index on father_table(father, grandfather) index on son_table(son, father, grandfather) That usually gives a less common, more selective value at the start of the index, making the initial selection in the index smaller. And AFAIK I don't have to rewrite my queries for that; the planner doesn't care about the order of expressions in the query that are on the same level. That said, I tend to use 'surrogate keys'; keys generated from sequences or auto-number columns for my tables. It makes the tables less readable, but the indexes remain smaller. Greetings, --Tim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.
On 3 Apr 2004 at 21:23, Mike Nolan wrote: Almost any cross dbms migration shows a drop in performance. The engine effectively trains developers and administrators in what works and what doesn't. The initial migration thus compares a tuned to an untuned version. I think it is also possible that Microsoft has more programmers working on tuning issues for SQL Server than PostgreSQL has working on the whole project. -- Mike Nolan Agreed. Also considering the high price of SQLServer it is in their interests to spend a lot of resources on tuning/performance to give it a commercial edge over it rivals and in silly benchmark scores. Cheers, Gary. -- Incoming mail is certified Virus Free. Checked by AVG Anti-Virus (http://www.grisoft.com). Version: 7.0.230 / Virus Database: 262.6.5 - Release Date: 31/03/2004 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
