Re: Re: [SQL] Query never returns ...

Um, no.

You should run vacuum analyze AFTER you loaded up the data, otherwise,
your table statistics will be all wrong (it'll contain 'empty table'
statistics).

-alex
On Thu, 8 Feb 2001, Brice Ruth wrote:

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