[HACKERS] Any optimizations to the join code in 7.1?

2001-04-25 Thread Mike Mascari
Hello. I have a particular query which performs a 15-way join; I believe in normalization ;-). Under 7.0.3, using the defaults where GEQO is enabled after 11, the query (which returns 1 row) takes 10 seconds. With GEQO turned off, it takes 18 seconds. Naturally I intend to upgrade as soon as

Re: [HACKERS] Comment about PostgreSQL on Epinions.com

2001-04-25 Thread Vince Vielhaber
On Wed, 25 Apr 2001, Alessio Bragadini wrote: While searching for some info and using google.com I came across http://www.epinions.com/ensw-review-7F55-42531AD1-3A43D81B-prod3 I am the first to understand that the opinion in such a site is worthless and the guy seems not to understand

Re: [HACKERS] Re: refusing connections based on load ...

2001-04-25 Thread The Hermit Hacker
On Tue, 24 Apr 2001, Nathan Myers wrote: On Tue, Apr 24, 2001 at 11:28:17PM -0300, The Hermit Hacker wrote: I have a Dual-866, 1gig of RAM and strip'd file systems ... this past week, I've hit many times where CPU usage is 100%, RAM is 500Meg free and disks are pretty much sitting idle

Re: [HACKERS] Re: [BUG?] tgconstrrelid doesn't survive a dump/restore

2001-04-25 Thread Philip Warner
I'll make the change ASAP. Now in CVS along with PG 7.0 compat. code. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@)

[HACKERS] Comment about PostgreSQL on Epinions.com

2001-04-25 Thread Alessio Bragadini
While searching for some info and using google.com I came across http://www.epinions.com/ensw-review-7F55-42531AD1-3A43D81B-prod3 I am the first to understand that the opinion in such a site is worthless and the guy seems not to understand anything about DBMSs but it's quite harsh anyway. --

Re: [HACKERS] refusing connections based on load ...

2001-04-25 Thread Christopher Masto
The whole argument over how to get load averages seems rather silly, and it's moot if the idea of using the load information to alter PG behavior is rejected. I personally have no use for it, but I don't think it's a bad idea in general. Particularly given future redundancy/load sharing

Re: [HACKERS] ERROR: parser: parse error at or near JOIN

2001-04-25 Thread Fernando Nasser
Tom Lane wrote: Fernando Nasser [EMAIL PROTECTED] writes: Is anyone else seeing this? No. I have the current CVS sources and make check ends up with one failure. My regression.diffs shows: I think you must have built gram.c with a broken bison or yacc. What exactly is

Re: [HACKERS] ERROR: parser: parse error at or near JOIN

2001-04-25 Thread Tom Lane
Fernando Nasser [EMAIL PROTECTED] writes: Tom Lane wrote: I think you must have built gram.c with a broken bison or yacc. What exactly is configure picking, and what version is it? Yes you are right. With: [12:03:04] flex -V flex version 2.5.4

Re: [HACKERS] Any optimizations to the join code in 7.1?

2001-04-25 Thread Tom Lane
Mike Mascari [EMAIL PROTECTED] writes: I have a particular query which performs a 15-way join; You should read http://www.postgresql.org/devel-corner/docs/postgres/explicit-joins.html regards, tom lane ---(end of

[HACKERS] Re: Any optimizations to the join code in 7.1?

2001-04-25 Thread Joel Burton
On Wed, 25 Apr 2001, Tom Lane wrote: Mike Mascari [EMAIL PROTECTED] writes: I have a particular query which performs a 15-way join; You should read http://www.postgresql.org/devel-corner/docs/postgres/explicit-joins.html I was recently poring over this page myself, as I've been working

Re: [HACKERS] ERROR: parser: parse error at or near JOIN

2001-04-25 Thread Tom Lane
Fernando Nasser [EMAIL PROTECTED] writes: Is anyone else seeing this? No. I have the current CVS sources and make check ends up with one failure. My regression.diffs shows: I think you must have built gram.c with a broken bison or yacc. What exactly is configure picking, and what version

Re: [HACKERS] refusing connections based on load ...

2001-04-25 Thread Peter Eisentraut
Tom Lane writes: A conncurrent-xacts limit isn't perfect of course, but I think it'd be pretty good, and certainly better than anything based on the available load-average numbers. The concurrent transaction limit would allow you to control the absolute load of the PostgreSQL server, but we

[HACKERS] Re: Any optimizations to the join code in 7.1?

2001-04-25 Thread Tom Lane
Joel Burton [EMAIL PROTECTED] writes: 1) it appears (from my tests) that SELECT * FROM CREATE VIEW joined as SELECT p.id, p.pname, c.cname FROM p LEFT OUTER JOIN c using (id) gives the same answer as SELECT * FROM CREATE VIEW nested SELECT

Re: [HACKERS] refusing connections based on load ...

2001-04-25 Thread Tom Lane
Jan Wieck and I talked about this for awhile yesterday, and we came to the conclusion that load-average-based throttling is a Bad Idea. Quite aside from the portability and permissions issues that may arise in getting the numbers, the available numbers are the wrong thing: (1) On most Unix

Re: [HACKERS] refusing connections based on load ...

2001-04-25 Thread The Hermit Hacker
On Wed, 25 Apr 2001, Peter Eisentraut wrote: Tom Lane writes: A conncurrent-xacts limit isn't perfect of course, but I think it'd be pretty good, and certainly better than anything based on the available load-average numbers. The concurrent transaction limit would allow you to control

Re: [HACKERS] refusing connections based on load ...

2001-04-25 Thread Tom Lane
Peter Eisentraut [EMAIL PROTECTED] writes: The idea behind the load average based approach is to make the postmaster respect the situation of the overall system. That'd be great if we could do it, but as I pointed out, the available stats do not allow us to do it very well. I think this will

Re: [HACKERS] concurrent Postgres on NUMA - howto ?

2001-04-25 Thread Tom Lane
Mauricio Breternitz [EMAIL PROTECTED] writes: Notice that WriteBuffer would just put the fresh copy of the page out in the shared space. Other backends would get the latest copy of the page when THEY execute BufferAlloc() afterwards. You seem to be assuming that BufferAlloc is

[HACKERS] Cursor support in pl/pg

2001-04-25 Thread Nathan Myers
Now that 7.1 is safely in the can, is it time to consider this patch? It provides cursor support in PL. http://www.airs.com/ian/postgresql-cursor.patch Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at

[HACKERS] tables/indexes/logs on different volumes

2001-04-25 Thread Nathan Myers
On Wed, Apr 25, 2001 at 09:41:57AM -0300, The Hermit Hacker wrote: On Tue, 24 Apr 2001, Nathan Myers wrote: On Tue, Apr 24, 2001 at 11:28:17PM -0300, The Hermit Hacker wrote: I have a Dual-866, 1gig of RAM and strip'd file systems ... this past week, I've hit many times where CPU usage

Re: [HACKERS] Cursor support in pl/pg

2001-04-25 Thread Tom Lane
[EMAIL PROTECTED] (Nathan Myers) writes: Now that 7.1 is safely in the can, is it time to consider this patch? Not till we've forked the tree for 7.2, which is probably a week or so away... regards, tom lane ---(end of

Re: [HACKERS] refusing connections based on load ...

2001-04-25 Thread Jan Wieck
The Hermit Hacker wrote: Agreed ... by default, the loadavg method could be set to zero, to ignore ... I don't care if I'm off by 1min before I catch the increase, the fact is that I have caught it, and prevent any new ones coming in until it drops off again ... Make it two variables:

Re: [HACKERS] Cursor support in pl/pg

2001-04-25 Thread Jan Wieck
Tom Lane wrote: [EMAIL PROTECTED] (Nathan Myers) writes: Now that 7.1 is safely in the can, is it time to consider this patch? Not till we've forked the tree for 7.2, which is probably a week or so away... IIRC the patch only provides the syntax for CURSOR to PL/pgSQL.

Re: [HACKERS] refusing connections based on load ...

2001-04-25 Thread Tom Lane
The Hermit Hacker [EMAIL PROTECTED] writes: Autoconf has a 'LOADAVG' check already, so what is so problematic about using that to enabled/disable that feature? Because it's tied to a GNU getloadavg.c implementation, which we'd have license problems with using. regards,

[HACKERS] Re: Any optimizations to the join code in 7.1?

2001-04-25 Thread Joel Burton
On Wed, 25 Apr 2001, Tom Lane wrote: 2) The explicit-joins help suggests that manual structuring and experimentation might help -- has anyone written (or could anyone write) anthing about where to start in guessing what join order might be optimal? The obvious starting point

Re: [HACKERS] refusing connections based on load ...

2001-04-25 Thread Tom Lane
Jan Wieck [EMAIL PROTECTED] writes: This proves that limiting the number of concurrently running transactions is sufficient to keep the system load down. Combined these two look as follows: - We start with a fairly high setting in the semaphore. - When the

[HACKERS] Re: Any optimizations to the join code in 7.1?

2001-04-25 Thread Tom Lane
Joel Burton [EMAIL PROTECTED] writes: In other DB systems I've used, some find that for this original query: SELECT * FROM a, b WHERE a.id=b.id AND b.name = 'foo'; that this version SELECT * FROM a JOIN b USING (id) WHERE b.name = 'foo'; has slower performance than SELECT * FROM b

Re: [HACKERS] refusing connections based on load ...

2001-04-25 Thread The Hermit Hacker
On Wed, 25 Apr 2001, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: The idea behind the load average based approach is to make the postmaster respect the situation of the overall system. That'd be great if we could do it, but as I pointed out, the available stats do not allow

Re: [HACKERS] refusing connections based on load ...

2001-04-25 Thread Vince Vielhaber
On Wed, 25 Apr 2001, Tom Lane wrote: The Hermit Hacker [EMAIL PROTECTED] writes: Autoconf has a 'LOADAVG' check already, so what is so problematic about using that to enabled/disable that feature? Because it's tied to a GNU getloadavg.c implementation, which we'd have license problems

[HACKERS] Schema Issue

2001-04-25 Thread V. M.
I want to extract tables schema information, i've looked at src/bin/psql/describe.c but i cannot determine the datatype 'serial' and 'references' from pg_*, i understand that triggers are generated for serial and references, so how i can understand from my perl application the full schema ?

Re: [HACKERS] refusing connections based on load ...

2001-04-25 Thread The Hermit Hacker
On Wed, 25 Apr 2001, Vince Vielhaber wrote: On Wed, 25 Apr 2001, Tom Lane wrote: The Hermit Hacker [EMAIL PROTECTED] writes: Autoconf has a 'LOADAVG' check already, so what is so problematic about using that to enabled/disable that feature? Because it's tied to a GNU getloadavg.c

Re: [HACKERS] refusing connections based on load ...

2001-04-25 Thread The Hermit Hacker
On Wed, 25 Apr 2001, Tom Lane wrote: I'm still concerned about portability issues, and about whether load average is really the right number to be looking at, however. Its worked for Sendmail for how many years now, and the code is there to use, with all portability issues resolved for every

[HACKERS] Open source is great, but too tempting

2001-04-25 Thread mlw
Just a little note of pseudo humor. We could not postmaster (pg version 7.0.3) and I could not figure out why. I checked directory permissions, all that. It kept complaining that it could not create the pid file. I did not understand why it would not work. I grepped through all the postgres