Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-16 Thread Simon Riggs
Josh Berkus Treating the optimizer as a black box is something I'm very used to from other RDBMS. My question is, how can you explicitly re-write a query now to improve it? If there's no way of manipulating queries without actually re-writing the optimizer, we're now in a position where

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-15 Thread Bruno Wolff III
On Wed, Apr 14, 2004 at 21:12:18 +0100, Simon Riggs [EMAIL PROTECTED] wrote: I guess what I'm saying is it's not how many people you've got working on the optimizer, its how many accurate field reports of less-than perfect optimization reach them. In that case, PostgreSQL is likely in a

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-15 Thread Simon Riggs
Bruno Wolff Simon Riggs [EMAIL PROTECTED] wrote: I guess what I'm saying is it's not how many people you've got working on the optimizer, its how many accurate field reports of less-than perfect optimization reach them. In that case, PostgreSQL is likely in a better position than

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-15 Thread Greg Stark
Bruno Wolff III [EMAIL PROTECTED] writes: I have seen exactly this happen a number of times over the last several years. However there is still only one Tom Lane implementing the improvements. Ob: Well clearly the problem is we need more Tom Lanes. -- greg ---(end

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-15 Thread Tom Lane
Greg Stark [EMAIL PROTECTED] writes: Ob: Well clearly the problem is we need more Tom Lanes. ObHHGReference: Haven't you heard? I come in six-packs! regards, tom lane ---(end of broadcast)--- TIP 9: the planner will

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-15 Thread Geoffrey
Greg Stark wrote: Bruno Wolff III [EMAIL PROTECTED] writes: I have seen exactly this happen a number of times over the last several years. However there is still only one Tom Lane implementing the improvements. Ob: Well clearly the problem is we need more Tom Lanes. my $pgGuru = Tom Lane; my

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-15 Thread Joshua D. Drake
my $pgGuru = Tom Lane; my @morepgGurus; my $howmany = 10; while($howmany--) { push @morepgGurus, $pgGuru; } This is just wrong... -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 -

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-15 Thread Greg Stark
Joshua D. Drake [EMAIL PROTECTED] writes: while($howmany--) { push @morepgGurus, $pgGuru; } This is just wrong... yeah, it would have been much clearer written as: push @morepgGurus, ($pgGuru)x$howmany; Or at least the perlish: for (1..$howmany) instead of C style while syntax. Ok. I

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-14 Thread Simon Riggs
Josh Berkus wrote: Unfortunately, these days only Tom and Neil seem to be seriously working on the query planner (beg pardon in advance if I've missed someone) so I think the real answer is that we need another person interested in this kind of optimization before it's going to get much

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-07 Thread Neil Conway
On Mon, 2004-04-05 at 11:36, Josh Berkus wrote: Unfortunately, these days only Tom and Neil seem to be seriously working on the query planner (beg pardon in advance if I've missed someone) Actually, Tom is the only person actively working on the planner -- while I hope to contribute to it in

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-05 Thread Josh Berkus
Mike, 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. Ah, but quantity != quality.Or they wouldn't be trolling our mailing lists trying to hire PostgreSQL programmers for the SQL

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-05 Thread markw
On 4 Apr, Cott Lang wrote: 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

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-05 Thread Magnus Naeslund(t)
Gary Doades wrote: Has anyone else done any comparative testing with the 2.6 kernel? I know for a fact that certain stuff is recognized differently between 2.2, 2.4 and 2.6 kernels. For example i have one box that i installed debian stable on that used a 2.2 kernel which automatically tuned on

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-05 Thread Gary Doades
On 5 Apr 2004 at 8:36, Josh Berkus wrote: Point taken, though, SQL Server has done a better job in opitimizing for dumb queries. This is something that PostgreSQL needs to work on, as is self-referential updates for large tables, which also tend to be really slow. Mind you, in SQL

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-04 Thread Gary Doades
] 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

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-04 Thread Gary Doades
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

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-04 Thread Gary Doades
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

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-04 Thread Paul Thomas
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

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-04 Thread Cott Lang
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

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-04 Thread Gary Doades
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

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-03 Thread Josh Berkus
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

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-03 Thread Aaron Werman
] 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

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-03 Thread Gary Doades
Actually it hasn't been my experience either. Most of my queries against the database, large and small are either a little quicker or no real difference. I have only really noticed big differences under stress when memory (RAM) is being squeezed. The main winner on 2.6 seems to be write

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-03 Thread Gary Doades
Thanks, I know about set showplan_text, but it is only the equivalent of explain, not explain analyze. The graphical plan gives full statistics, runtime, percentage cost, loop execution counts etc. which is much more useful. I don't know of a way of getting the graphical plan content in text

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-03 Thread Gary Doades
Following on from Josh's response and my previous reply on SQLServer planning. The main problem query is this one: SELECT VS.*,VL.TEL1,SC.CONTRACT_ID,SC.CONTRACT_REF, SC.MAX_HOURS, SC.MIN_HOURS, (SELECT COUNT(*) FROM TIMESHEET_DETAIL JOIN MAIN_ORDER ON (MAIN_ORDER.ORDER_ID =

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-01 Thread Tom Lane
Gary Doades [EMAIL PROTECTED] writes: As a test in PosgreSQL I issued a statement to update a single column of a table containing 2.8 million rows with the values of a column in a table with similar rowcount. Using the above spec I had to stop the server after 17 hours. The poor thing was

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-01 Thread Gary Doades
The post was not intended to be content-rich, just my initial feedback after only just switching to 2.6. Since I had largely given up on this particular line of attack using 2.4 I didn't think to do a detailed analysis at this time. I was also hoping that others would add to the discussion.