X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id CFEADD1DCF5 for ; Mon, 15 Dec 2003 18:06:17 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 89394-01 for ; Mon, 15 Dec 2003 14:05:56 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id A7BA0D1DDDB for ; Mon, 15 Dec 2003 14:05:49 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hBFI5p19013829; Mon, 15 Dec 2003 13:05:52 -0500 (EST) To: LIANHE SHAO Cc: pgsql-performance Subject: Re: Index problem or function problem? In-reply-to: <4595eb458230.4582304595eb@jhmimail.jhmi.edu> References: <4595eb458230.4582304595eb@jhmimail.jhmi.edu> Comments: In-reply-to LIANHE SHAO message dated "Tue, 09 Dec 2003 23:24:19 +0000" Date: Mon, 15 Dec 2003 13:05:51 -0500 Message-ID: <13828.1071511551@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/227 X-Sequence-Number: 5087 LIANHE SHAO writes: > PGA=> explain select ei.expid, er.geneid, > er.sampleid, ei.annotation, si.samplename, > ei.title as exp_name, aaa.chip, > aaa.sequence_derived_from as accession_number, > aaa.gene_symbol, aaa.title as gene_function, > er.exprs, er.mas5exprs from expressiondata er, > experimentinfo ei, sampleinfo si, > affy_array_annotation aaa where exists (select > distinct ei.expid from experimentinfo) and > ei.annotation = aaa.chip and (lower (aaa.title) > like '%mif%' or lower(aaa.sequence_description) like > '%mif%') and exists (select distinct ei.annotation > from experimentinfo) and ei.expid = er.expid and > er.expid = si.expid and er.sampleid = si.sampleid > and er.geneid = aaa.probeset_id order by si.sampleid > limit 20; What is the purpose of the EXISTS() clauses? They are almost surely not doing what you intended, because AFAICS they are just an extremely expensive means of producing a constant-TRUE result. In exists (select distinct ei.expid from experimentinfo) "ei.expid" is an outer reference, which will necessarily be the same value over all rows of the sub-select. After computing this same value for every row of experimentinfo, the system performs a DISTINCT operation (sort + unique, not cheap) ... and then all it checks for is whether at least one row was produced, which means the DISTINCT operation was completely unnecessary. The only way the EXISTS could return false is if experimentinfo were empty, but if it were so then the outer FROM would've produced no rows and we'd not have got to WHERE anyway. I'm not sure why you get a worse plan for the simpler variant of the query; it would help to see EXPLAIN ANALYZE rather than EXPLAIN output. But it's not worth trying to improve the performance until you are calculating correct answers, and I suspect the above is not doing what you are after at all. regards, tom lane