pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Brice Ruth <brice@webprojkt.com>
To: Brice Ruth <brice@webprojkt.com>
Subject: Re: Query never returns ...
Date: Thu, 08 Feb 2001 10:02:34 -0600
Message-ID: <3A82C31B.77923607@webprojkt.com> (raw)
References: <3A82C15D.9375591A@webprojkt.com>

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/



view thread (12+ messages)  latest in thread

Message-ID: <3A82C31B.77923607@webprojkt.com>
Permalink:  ../3A82C31B.77923607@webprojkt.com/
Also on:    postgresql.org/message-id/3A82C31B.77923607@webprojkt.com

 · 

reply

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
  Subject: Re: Query never returns ...
  In-Reply-To: <3A82C31B.77923607@webprojkt.com>
  References: <3A82C15D.9375591A@webprojkt.com> <3A82C31B.77923607@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