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

Select running slow on Postgres

From: "samantha mahindrakar" <sam(dot)mahindrakar(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Select running slow on Postgres
Date: 2008-07-01 19:29:26
Message-ID: f0c828c40807011229l2df5ac43h7fc950d1e90fe4eb@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2008-07-01 19:47:26
Subject: Re: Select running slow on Postgres
Previous:From: Richard HuxtonDate: 2008-07-01 15:33:54
Subject: Re: Does max size of varchar influence index size

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