Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id AC2E6650E9E for ; Wed, 2 Jul 2008 16:01:50 -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 08480-01 for ; Wed, 2 Jul 2008 16:01:43 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qb-out-0506.google.com (qb-out-0506.google.com [72.14.204.228]) by postgresql.org (Postfix) with ESMTP id DA2B7650E7D for ; Wed, 2 Jul 2008 16:01:44 -0300 (ADT) Received: by qb-out-0506.google.com with SMTP id c8so1552062qbc.39 for ; Wed, 02 Jul 2008 12:01:42 -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:references; bh=P2FFcdKWP+7M22RtXiNMMoZoFNUc/l7wwe5IArlb0fE=; b=KwQsazlfRStqhwJjdwYF2q9dn4SY4q9oOZ1UmPi0zQ6iXNOIIi5UHxbAZxbclLe85I M2r94QYk+fR+6Eg4/RAAVRIORlMBdq6Q5QvV6fcpOLpF5Z+g9EjzMWG2Njg8fNiY+PCd 3c50/QGvspFbU5IoYIBzC6NA6CHVGItv13Ufg= 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:references; b=HWVQhEOwv+zovWlkQuRZvs8xFeRjNVF/VOao3+m4b7au8jZ+hly7dDmqYmqA1dqeRl rodq4wBFczqWhQjxPRQtKmWINegR+WffhM0DUVFtaxrZd+3fGKgFFTLuF7LQuOk3fKeX 1hc0besBlxTa3oaH6bmbgFGfFkhqspqQDGJdg= Received: by 10.114.184.11 with SMTP id h11mr7326561waf.175.1215025301699; Wed, 02 Jul 2008 12:01:41 -0700 (PDT) Received: by 10.114.183.9 with HTTP; Wed, 2 Jul 2008 12:01:41 -0700 (PDT) Message-ID: Date: Wed, 2 Jul 2008 15:01:41 -0400 From: "samantha mahindrakar" To: "Scott Marlowe" Subject: Re: Select running slow on Postgres Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3943_13102697.1215025301691" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200807/34 X-Sequence-Number: 30634 ------=_Part_3943_13102697.1215025301691 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I ran the explain analyze.Here is what i got: "Group (cost=112266.37..112266.40 rows=1 width=56) (actual time=5583.399..5615.476 rows=13373 loops=1)" " -> Sort (cost=112266.37..112266.38 rows=1 width=56) (actual time=5583.382..5590.890 rows=13373 loops=1)" " Sort Key: lane_data_07_08.lane_id, lane_data_07_08.measurement_start, lane_data_07_08.measurement_end, lane_data_07_08.speed, lane_data_07_08.volume, lane_data_07_08.occupancy, lane_data_07_08.quality, lane_data_07_08.effective_date" " -> Nested Loop IN Join (cost=0.00..112266.36 rows=1 width=56) (actual time=1100.307..5547.768 rows=13373 loops=1)" " -> Seq Scan on lane_data_07_08 (cost=0.00..112241.52 rows=3 width=56) (actual time=1087.666..5341.662 rows=20581 loops=1)" " Filter: (((volume = 255::double precision) OR (speed = 255::double precision) OR (occupancy = 255::double precision) OR (occupancy >= 100::double precision) OR (volume > 52::double precision) OR (volume < 0::double precision) OR (speed > 120::double precision) OR (speed < 0::double precision)) AND (date_part('hour'::text, measurement_start) >= 5::double precision) AND (date_part('hour'::text, measurement_start) <= 23::double precision) AND (date_part('day'::text, measurement_start) = 1::double precision))" " -> Index Scan using lane_info_pk on lane_info (cost=0.00..8.27 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=20581)" " Index Cond: (lane_data_07_08.lane_id = lane_info.lane_id)" " Filter: (inactive IS NULL)" "Total runtime: 5621.409 ms" Well instaed of creating extra indexes (since they eat up lot of space) i made use of the whole measurement_start field, so thet it uses the index proeprty and makes the search faster. So i changed the query to include the measuerment start as follows: 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 measurement_start between '2008-06-30 05:00:00-04' AND '2008-06-30 23:00:00-04'* GROUP BY lane_id,measurement_start,measurement_end,speed,volume,occupancy,quality,effective_date ORDER BY lane_id, measurement_start Samantha On 7/1/08, Scott Marlowe wrote: > 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? > ------=_Part_3943_13102697.1215025301691 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I ran the explain analyze.Here is what i got:


"Group  (cost=112266.37..112266.40 rows=1 width=56) (actual time=5583.399..5615.476 rows=13373 loops=1)"
"  ->  Sort  (cost=112266.37..112266.38 rows=1 width=56) (actual time=5583.382..5590.890 rows=13373 loops=1)"
"        Sort Key: lane_data_07_08.lane_id, lane_data_07_08.measurement_start, lane_data_07_08.measurement_end, lane_data_07_08.speed, lane_data_07_08.volume, lane_data_07_08.occupancy, lane_data_07_08.quality, lane_data_07_08.effective_date"
"        ->  Nested Loop IN Join  (cost=0.00..112266.36 rows=1 width=56) (actual time=1100.307..5547.768 rows=13373 loops=1)"
"              ->  Seq Scan on lane_data_07_08  (cost=0.00..112241.52 rows=3 width=56) (actual time=1087.666..5341.662 rows=20581 loops=1)"
"                    Filter: (((volume = 255::double precision) OR (speed = 255::double precision) OR (occupancy = 255::double precision) OR (occupancy >= 100::double precision) OR (volume > 52::double precision) OR (volume < 0::double precision) OR (speed > 120::double precision) OR (speed < 0::double precision)) AND (date_part('hour'::text, measurement_start) >= 5::double precision) AND (date_part('hour'::text, measurement_start) <= 23::double precision) AND (date_part('day'::text, measurement_start) = 1::double precision))"
"              ->  Index Scan using lane_info_pk on lane_info  (cost=0.00..8.27 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=20581)"
"                    Index Cond: (lane_data_07_08.lane_id = lane_info.lane_id)"
"                    Filter: (inactive IS NULL)"
"Total runtime: 5621.409 ms"


Well instaed of creating extra indexes (since they eat up lot of space) i made use of the whole measurement_start field, so thet it uses the index proeprty and makes the search faster.
So i changed the query to include the measuerment start as follows:

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 measurement_start between '2008-06-30 05:00:00-04' AND  '2008-06-30 23:00:00-04'
GROUP BY lane_id,measurement_start,measurement_end,speed,volume,occupancy,quality,effective_date
ORDER BY lane_id, measurement_start


Samantha

On 7/1/08, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Jul 1, 2008 at 1:29 PM, samantha mahindrakar
> <sam.mahindrakar@gmail.com> 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?
>
------=_Part_3943_13102697.1215025301691--