From: samantha mahindrakar <sam.mahindrakar@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Select running slow on Postgres
Date: Tue, 1 Jul 2008 15:29:26 -0400
Message-ID: <f0c828c40807011229l2df5ac43h7fc950d1e90fe4eb@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sam.mahindrakar@gmail.com
Subject: Re: Select running slow on Postgres
In-Reply-To: <f0c828c40807011229l2df5ac43h7fc950d1e90fe4eb@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox