Re: Query never returns ...

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/
>