Skip site navigation (1) Skip section navigation (2)

Re: Select running slow on Postgres

From: "samantha mahindrakar" <sam(dot)mahindrakar(at)gmail(dot)com>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Select running slow on Postgres
Date: 2008-07-02 19:01:41
Message-ID: f0c828c40807021201g1fd77a0n5fd7d65a36634df7@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(dot)marlowe(at)gmail(dot)com> wrote:
> On Tue, Jul 1, 2008 at 1:29 PM, samantha mahindrakar
> <sam(dot)mahindrakar(at)gmail(dot)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?
>

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2008-07-02 19:07:45
Subject: Re: Select running slow on Postgres
Previous:From: Scott MarloweDate: 2008-07-02 18:47:23
Subject: Re: [QUESTION]Concurrent Access

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group