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

Re: Optimize date query for large child tables: GiST or GIN?

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 (view raw or flat)
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

In response to

pgsql-performance by date

Next:From: David JarvisDate: 2010-05-21 00:28:26
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Previous:From: Stephen FrostDate: 2010-05-20 21:30:29
Subject: Re: Optimize date query for large child tables: GiST orGIN?

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