David Jarvis <thangalin@gmail.com> wrote:
> Hi,
>
> I have the following query:
>
> Select avg(d.amount) AS amount, y.year
> From year_ref y
> Join month_ref m
> On m.year_ref_id = y.id
> Join daily d
> On d.month_ref_id = m.id
> Where y.year Between 1980 And 2000
> And m.month = 12
> And m.category_id = '001'
> And d.daily_flag_id <> 'M'
> And exists (
I think, you have a bad table design: you have splitted a date into
year, month and day, and stored that in different tables.
If i were you, i would use a regular date-field and indexes like:
test=# create table d (d date);
CREATE TABLE
test=*# create index idx_d_year on d(extract (year from d));
CREATE INDEX
test=*# create index idx_d_month on d(extract (month from d));
CREATE INDEX
Your query with this structure:
select ... from table where
extract(year from d) between 1980 And 2000
and extract(month from d) = 12
and daily_flag_id ...
This can use the indexes and avoid the seq-scan. You can also use
table-partitioning and constraint exclusion (for instance, one table per
month)
> http://i.imgur.com/m6YIV.png
>
> I have yet to let this query finish.
>
> Any ideas how I can speed it up?
Do you have an index on daily.daily_flag_id ?
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
