Re: Optimize date query for large child tables: GiST or GIN?
| От | David Jarvis |
|---|---|
| Тема | Re: Optimize date query for large child tables: GiST or GIN? |
| Дата | |
| Msg-id | AANLkTikRB45IZDwObGbfXJbDV8nQHfHrNVwcQpv0GRwB@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Optimize date query for large child tables: GiST or GIN? (Stephen Frost <sfrost@snowman.net>) |
| Список | pgsql-performance |
Hi,
~300 million measurements
~12000 stations (not 70000 as I mentioned before)
~5500 cities
PG 8.4
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. :-(
I've since added a constraint on elevation; it'll help a bit:
Dave
~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
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);
ON climate.station
USING btree
(elevation);
В списке pgsql-performance по дате отправления: