Skip site navigation (1) Skip section navigation (2)

Full table scan: 300 million rows

From: David Jarvis <thangalin(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Full table scan: 300 million rows
Date: 2010-05-15 01:47:52
Message-ID: AANLkTin00xec2tVESlMoXv5uQphddPLIqNhfKg5y7ExM@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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   (
                Select 1
                From station s1
                    inner Join city c1
                        On c1.id = 10663
                Where 6371.009
                      * SQRT(
                            POW(RADIANS(c1.latitude_decimal -
s1.latitude_decimal), 2)
                            + (
                                COS(RADIANS(c1.latitude_decimal +
s1.latitude_decimal) / 2)
                                * POW(RADIANS(c1.longitude_decimal -
s1.longitude_decimal), 2)
                                )
                            ) <= 50
                    And S1.station_district_id = y.station_district_id
                )
Group By y.year

I posted the explain plan as an image (can't figure out how to export it
from the pgadmin3):

http://i.imgur.com/m6YIV.png

I have yet to let this query finish.

Any ideas how I can speed it up?

Thank you!

Responses

pgsql-novice by date

Next:From: Andreas KretschmerDate: 2010-05-15 07:22:27
Subject: Re: Full table scan: 300 million rows
Previous:From: Tom LaneDate: 2010-05-14 21:45:52
Subject: Re: Out of memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group