Received: from morpheus.webprojkt.com (morpheus.webprojkt.com [209.61.155.148]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f18Gq6x01453 for ; Thu, 8 Feb 2001 11:52:06 -0500 (EST) (envelope-from brice@webprojkt.com) Received: (qmail 2270 invoked by uid 604); 8 Feb 2001 16:52:01 -0000 Received: from unknown (HELO webprojkt.com) (216.170.131.123) by morpheus.webprojkt.com with SMTP; 8 Feb 2001 16:52:01 -0000 Message-ID: <3A82CEB1.764160D5@webprojkt.com> Date: Thu, 08 Feb 2001 10:52:00 -0600 From: Brice Ruth X-Mailer: Mozilla 4.76 (Macintosh; U; PPC) X-Accept-Language: en MIME-Version: 1.0 To: Stephan Szabo CC: pgsql-general@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [SQL] Query never returns ... References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200102/398 X-Sequence-Number: 4274 Stephan, Here is what EXPLAIN shows: NOTICE: QUERY PLAN: Sort (cost=0.02..0.02 rows=1 width=64) -> Nested Loop (cost=0.00..0.01 rows=1 width=64) -> Seq Scan on tblmedcond (cost=0.00..0.00 rows=1 width=36) -> Seq Scan on tblsidedruglink (cost=0.00..0.00 rows=1 width=28) As for vacuum analyze - prior to running into these problems, I deleted all data from the database (using delete from ) and then ran vacuumdb -a, after which I loaded the data into the tables using 'copy ... from' - there have been no updates to the database since then - merely selects. -Brice Stephan Szabo wrote: > > What does explain show for the query and have you run > vacuum analyze recently on the tables? > > On Thu, 8 Feb 2001, Brice Ruth wrote: > > > The following query: > > > > SELECT > > tblSIDEDrugLink.DrugID, > > tblSIDEDrugLink.MedCondID, > > tblMedCond.PatientName AS MedCondPatientName, > > tblMedCond.ProfessionalName AS MedCondProfessionalName, > > tblSIDEDrugLink.Frequency, > > tblSIDEDrugLink.SeverityLevel > > FROM > > tblSIDEDrugLink, > > tblMedCond > > WHERE > > (tblSIDEDrugLink.DrugID IN ('DG-18698')) AND > > (tblSIDEDrugLink.MedCondID = tblMedCond.MedCondID) > > ORDER BY > > tblSIDEDrugLink.DrugID, > > tblSIDEDrugLink.Frequency, > > tblSIDEDrugLink.SeverityLevel, > > tblSIDEDrugLink.MedCondID; > > > > seems to not be liked by PostgreSQL. Table 'tblSIDEDrugLink' has the > > following structure: > > > > CREATE TABLE TBLSIDEDRUGLINK > > ( > > DRUGID VARCHAR(10) NOT NULL, > > MEDCONDID VARCHAR(10) NOT NULL, > > FREQUENCY INT2, > > SEVERITYLEVEL INT2, > > CONSTRAINT PK_TBLSIDEDRUGLINK PRIMARY KEY (DRUGID, MEDCONDID) > > ); > > > > with the following index: > > CREATE INDEX IX_TBLSIDEDRUGLINK1 ON TBLSIDEDRUGLINK (MEDCONDID); > > > > This table has 153,288 rows. > > > > Table 'tblMedCond' has the following structure: > > > > CREATE TABLE TBLMEDCOND > > ( > > MEDCONDID VARCHAR(10) NOT NULL, > > PROFESSIONALNAME VARCHAR(58), > > PATIENTNAME VARCHAR(58), > > CONSTRAINT PK_TBLMEDCOND PRIMARY KEY (MEDCONDID) > > ); > > > > This table has 1,730 rows. > > > > The query above is made by a third-party API that I don't have the > > source for, so I can't modify the query in the API, though the > > third-party has been quite willing to help out - they may even ship me a > > 'special' version of the API if there's something in this query that > > PostgreSQL for some reason doesn't implement efficiently enough. > > > > If it would help anyone to see the query plan or such - I can modify the > > logs to show that, just let me know. > > > > Btw - I've let this query run for a while & I haven't seen it complete > > ... soooo ... I don't know if it would ever complete or not. > > > > Any help at all is as always, appreciated. > > > > Sincerest regards, > > -- > > Brice Ruth > > WebProjkt, Inc. > > VP, Director of Internet Technology > > http://www.webprojkt.com/ > > -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/