Re: [GENERAL] Re: Query never returns ...
От | Alex Pilosov |
---|---|
Тема | Re: [GENERAL] Re: Query never returns ... |
Дата | |
Msg-id | Pine.BSO.4.10.10102081326380.26806-100000@spider.pilosoft.com обсуждение исходный текст |
Ответ на | Re: Query never returns ... (Brice Ruth <brice@webprojkt.com>) |
Список | pgsql-sql |
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/ > > > > >
В списке pgsql-sql по дате отправления: