Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CF5A9650EA9 for ; Tue, 1 Jul 2008 16:47:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 84127-05 for ; Tue, 1 Jul 2008 16:47:26 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.29]) by postgresql.org (Postfix) with ESMTP id BBD1F64FD64 for ; Tue, 1 Jul 2008 16:47:28 -0300 (ADT) Received: by yw-out-2324.google.com with SMTP id 3so6600ywj.73 for ; Tue, 01 Jul 2008 12:47:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=WXf1PPqaaVN2e7csNXptjtDFdW09en9nZXwYSFXcDCQ=; b=lrSAW41HWLJccMQE9t2ChjR+NTRPjlPg95nGFiLbKjMbJdcrj78j8ubu8iv9qdquO4 aMaS818JYoR3MQRWX22AedinftlCLrPMRRTlBYoS/7f6B7g6jGnoRARDZ2SjTHjNmVEK uTLPM40B0TMEbAzeUOHrW/Izqs9P0nfTj/jQY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=WASYboix+5zYyU15SRlweyY1rNCxRg01B6IAUqbZ9D79zeMXkEghfZLxu8vUSXs02f jvPfBgL/kw9JwzvxDfEf4ekBZaZQzhyT5vqKgwU3R3hkfhRXstJqB4dgFBpfDrPDlbdq VG822Qxgu5TR0mU+PtEce7aViXxmomZfE9pb4= Received: by 10.114.156.1 with SMTP id d1mr6183487wae.171.1214941646830; Tue, 01 Jul 2008 12:47:26 -0700 (PDT) Received: by 10.114.180.17 with HTTP; Tue, 1 Jul 2008 12:47:26 -0700 (PDT) Message-ID: Date: Tue, 1 Jul 2008 13:47:26 -0600 From: "Scott Marlowe" To: "samantha mahindrakar" Subject: Re: Select running slow on Postgres Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/10 X-Sequence-Number: 30610 On Tue, Jul 1, 2008 at 1:29 PM, samantha mahindrakar wrote: > Hi > I have a select statement that runs on a partition having say couple > million rows. > The tabel has indexes on two colums. However the query uses the > non-indexed colums too in its where clause. > For example: > SELECT lane_id,measurement_start, > measurement_end,speed,volume,occupancy,quality,effective_date > FROM tss.lane_data_06_08 > WHERE lane_id in(select lane_id from lane_info where inactive is null ) > AND date_part('hour', measurement_start) between 5 and 23 > AND date_part('day',measurement_start)=30 > GROUP BY lane_id,measurement_start,measurement_end,speed,volume,occupancy,quality,effective_date > ORDER BY lane_id, measurement_start > > out of this only lane_id and mesaurement_start are indexed. This query > will return around 10,000 rows. But it seems to be taking a long time > to execute which doesnt make sense for a select statement. It doesnt > make any sense to create index for every field we are gonna use in tne > where clause. > Isnt there any way we can improve the performance? I'm guessing that adding an index for either date_part('hour',measurement_start) or date_part('day',measurement_start) or both would help. What does explain analyze select ... (rest of query here) say?