From: | David Jarvis <thangalin(at)gmail(dot)com> |
---|---|
To: | Stephen Frost <sfrost(at)snowman(dot)net> |
Cc: | Matthew Wakeling <mnw21(at)cam(dot)ac(dot)uk>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Optimize date query for large child tables: GiST or GIN? |
Date: | 2010-05-21 00:19:06 |
Message-ID: | AANLkTikRB45IZDwObGbfXJbDV8nQHfHrNVwcQpv0GRwB@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
~300 million measurements
~12000 stations (not 70000 as I mentioned before)
~5500 cities
some serious data tho, at least. Basically, PG is sequentially scanning
> through all of the tables in your partitioning setup. What is
> constraint_exclusion set to? What version of PG is this? Do the
> results og this query look at all correct to you?
>
PG 8.4
show constraint_exclusion;
partition
With so much data, it is really hard to tell if the query looks okay without
having it visualized. I can't visualize it until I have the query set up
correctly. At the moment it looks like the query is wrong. :-(
Have you considered an index on elevation, btw? How many records in
> that city table are there and how many are actually in that range?
>
I've since added a constraint on elevation; it'll help a bit:
CREATE INDEX station_elevation_idx
ON climate.station
USING btree
(elevation);
Dave
From | Date | Subject | |
---|---|---|---|
Next Message | David Jarvis | 2010-05-21 00:28:26 | Re: Optimize date query for large child tables: GiST or GIN? |
Previous Message | Stephen Frost | 2010-05-20 21:30:29 | Re: Optimize date query for large child tables: GiST or GIN? |