From: Scott Marlowe <scott.marlowe@gmail.com>
To: samantha mahindrakar <sam.mahindrakar@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Select running slow on Postgres
Date: Tue, 1 Jul 2008 13:47:26 -0600
Message-ID: <dcc563d10807011247s262fef0cm67ae0ee076bb1e51@mail.gmail.com> (raw)
In-Reply-To: <f0c828c40807011229l2df5ac43h7fc950d1e90fe4eb@mail.gmail.com>
References: <f0c828c40807011229l2df5ac43h7fc950d1e90fe4eb@mail.gmail.com>
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?
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: scott.marlowe@gmail.com, sam.mahindrakar@gmail.com
Subject: Re: Select running slow on Postgres
In-Reply-To: <dcc563d10807011247s262fef0cm67ae0ee076bb1e51@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