Received: from candle.pha.pa.us (maillist@s5-03.ppp.op.net [209.152.195.67]) by hub.org (8.9.2/8.9.1) with ESMTP id PAA15899 for ; Thu, 11 Mar 1999 15:17:33 -0500 (EST) (envelope-from maillist@candle.pha.pa.us) Received: (from maillist@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id PAA20137; Thu, 11 Mar 1999 15:17:05 -0500 (EST) From: Bruce Momjian Message-Id: <199903112017.PAA20137@candle.pha.pa.us> Subject: Re: [SQL] Very disappointing performance -- All Indexes Ignored. In-Reply-To: <36E819EE.ED2911E6@kearneydev.com> from secret at "Mar 11, 1999 2:30:54 pm" To: secret@kearneydev.com (secret) Date: Thu, 11 Mar 1999 15:17:05 -0500 (EST) Cc: pgsql-sql@postgreSQL.org X-Mailer: ELM [version 2.4ME+ PL47 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > > Second, indexes are not of a huge value in this query, because there are > > no restrictions, i.e. x=3. In most cases, walking the index and then > > going to the heap table takes longer than just reading the entire table > > into a hash and doing the join there. > > I see, and after doing a more specific query(from_job_id=xxx) the speed > is much increased... The main thing that disturbed me is the rather large > time between the query with joins(300 seconds) and with the functions(30s)... > Shouldn't the functions be far slower?(Seems like they would be less > efficient) Well, you are doing the functions only for matched rows, while the other does full-table joins. Can you easily test the snapshot and let me know? I have re-done the optimizer for not-yet-released 6.5, and that may fix the problem. I fixed many optimizer problems, and it may be choosing a non-optimal join path. > > PostgreSQL is also crashing 1-2 times a day on me, although I have a > handy perl script to keep it alive now ... If PostgreSQL does work for > my project I'll of course buy CDs/books/whatever to support the project. > Despite the 2 problems I'm having I am very impressed with the project. That is strange. Don't here that much. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026