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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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