DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type
	:content-transfer-encoding:content-disposition;
	b=mG+aloJ5TKdJHEIh8slHOrnbQCbFJy3FyHZSLZc0qBZKnCptYAz+yhLQnOCE9cwvlN
	WASBZR8jFWGz9wXZCa4XWtV5gza8VlP1mwLz1dPsin8XGY6ANWcR/kiF8u03IESqXV7C
	+q5uRE2yvRL4gxY1GKBPOiT3QefRX1m0UkbYc=
Message-ID: <f0c828c40807011229l2df5ac43h7fc950d1e90fe4eb@mail.gmail.com>
Date: Tue, 1 Jul 2008 15:29:26 -0400
From: "samantha mahindrakar" <sam.mahindrakar@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Select running slow on Postgres
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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?


Samantha