Re: Full table scan: 300 million rows

David Jarvis <thangalin(at)gmail(dot)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°