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: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Thom Brown <thombrown(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Date: 2010-05-23 22:55:21
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

The problem is now solved (in theory).

Well, it's not the functions per se that's the problem, it's the lack of
> a useful index on the expression.

The measurement table indexes (on date and weather station) were not being
used because the only given date ranges (e.g., 1900 - 2009) were causing the
planner to do a full table scan, which is correct. What I had to do was find
a way to reduce the dates so that the planner would actually use the index,
rather than doing a full table scan on 43 million records. By passing in
1955 - 1960 the full table scan went away in favour of an index scan, as

Each weather station has a known lifespan (per climate category). That is,
not all weather stations between 1880 and 2009 collected data.  For example,
one weather station monitored the maximum daily temperature between
2006-11-29 and 2009-12-31. Some stations span more than 30 years, but I
believe those are in the minority (e.g., 1896-12-01 to 1959-01-31). (I'll be
able to verify once the analysis is finished.)

I will add another table that maps the stations to category and min/max
dates. I can then use this reference table which should (theory part here)
tell the planner to use the index.

What is *really impressive*, though... If my understanding is correct...

PostgreSQL scanned 43 million rows 78 times, returning results in ~90 sec.

Thanks again for all your help, everybody. I sincerely appreciate your
patience, comments, and ideas.


In response to


pgsql-performance by date

Next:From: Jayadevan MDate: 2010-05-24 03:45:22
Subject: Re: pg_dump and pg_restore
Previous:From: Peter KoczanDate: 2010-05-22 19:55:22
Subject: Re: pg_dump and pg_restore

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