From: Brice Ruth <brice@webprojkt.com>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Query never returns ...
Date: Thu, 08 Feb 2001 10:52:00 -0600
Message-ID: <3A82CEB1.764160D5@webprojkt.com> (raw)
References: <Pine.BSF.4.21.0102080844480.54197-100000@megazone23.bigpanda.com>
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/
> >
--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: brice@webprojkt.com, sszabo@megazone23.bigpanda.com, pgsql-general@postgresql.org
Subject: Re: [SQL] Query never returns ...
In-Reply-To: <3A82CEB1.764160D5@webprojkt.com>
References: <Pine.BSF.4.21.0102080844480.54197-100000@megazone23.bigpanda.com> <3A82CEB1.764160D5@webprojkt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox