Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f18GUUx98962; Thu, 8 Feb 2001 11:30:30 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f18G2oG66083; Thu, 8 Feb 2001 11:02:50 -0500 (EST) (envelope-from news) From: Brice Ruth X-Newsgroups: comp.databases.postgresql.sql,comp.databases.postgresql.general Subject: Re: Query never returns ... Date: Thu, 08 Feb 2001 10:02:34 -0600 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 90 Message-ID: <3A82C31B.77923607@webprojkt.com> References: <3A82C15D.9375591A@webprojkt.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org To: Brice Ruth X-Mailer: Mozilla 4.76 (Macintosh; U; PPC) X-Accept-Language: en To: pgsql-general@postgresql.org.pgsql-sql@postgresql.org X-Archive-Number: 200102/390 X-Sequence-Number: 4264 Followup: This query, run against the same dataset in a MS Access 2000 database, returns immediately with the resultset. (I truly mean immediately) The computer running the Access db is a laptop, PII 266 w/ 128MB RAM (an old Dell Latitude). The server running PostgreSQL is a dual PIII 450 w/ 256MB RAM. This is just meant as an FYI. -Brice 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/