DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=I1QFGxX3F44Ro1xEcnv2BNSw9npThLgCwlKOfDOZh8/LrFL+N+OC2HYHWKkVbcuN96
	HWDIZFSV82TJcxzkqoZmz1TfPncBZEjPBJaAWBRbqUKBlU7a0uuSqu1E3zLOMC/DzY1b
	prVJg6iZ94nBKeMyALVK/pllfTLu2fmZDLj6w=
Message-ID: <4BF6D5C0.2030902@gmail.com>
Date: Fri, 21 May 2010 20:49:36 +0200
From: Yeb Havinga <yebhavinga@gmail.com>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: David Jarvis <thangalin@gmail.com>
CC: Stephen Frost <sfrost@snowman.net>, Tom Lane <tgl@sss.pgh.pa.us>,
	Thom Brown <thombrown@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Optimize date query for large child tables: GiST or
 GIN?
References: <AANLkTimk7lRS8FHJOot3tF-c6Ds4jexCvWKEQNU1V0Xt@mail.gmail.com>	
	<AANLkTikOEC-Gr3eqGlyGauRAZJknQEaS5DEWw-ZOcl6r@mail.gmail.com>	
	<7917.1274385834@sss.pgh.pa.us>	
	<AANLkTimLdBWaVpSF5Sc3ArT_9jcAjhNiB9WBBA4HIyXj@mail.gmail.com>	
	<8132.1274386738@sss.pgh.pa.us>	
	<AANLkTikK_xUrI7iOU9CzpYAKbtxZz-chc3SaHGhTEGXP@mail.gmail.com>	
	<20100520210106.GO21875@tamriel.snowman.net>	
	<AANLkTinAs8sWmMjEynf1K0DNsmaIiqRRNqVR5tNlkbuC@mail.gmail.com>	
	<4BF64693.8050800@gmail.com> <4BF64830.4080902@gmail.com>
	<AANLkTinFXWorUTT9iqagOKbsid_ojdniMC0e41ZK_mB7@mail.gmail.com>
In-Reply-To: <AANLkTinFXWorUTT9iqagOKbsid_ojdniMC0e41ZK_mB7@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

David Jarvis wrote:
> Hi, Yeb.
>
> This is starting to go back to the design I used with MySQL:
>
>     * YEAR_REF - Has year and station
>     * MONTH_REF - Has month, category, and yea referencer
>     * MEASUREMENT - Has month reference, amount, and day
>
> Normalizing by date parts was fast. Partitioning the tables by year 
> won't do much good -- users will probably choose 1900 to 2009, 
> predominately.
Ok, in that case it is a bad idea.
> I thought about splitting the data by station by category, but that's 
> ~73000 tables. My understanding is that PostgreSQL uses files per 
> index, which would be messy at the OS level (Linux 2.6.31). Even by 
> station alone is 12139 tables, which might be tolerable for now, but 
> with an order of magnitude more stations on the distant horizon, it 
> will not scale.
Yes, I've read a few times now that PG's partitioning doesn't scale 
beyond a few 100 partitions.
> I also thought about splitting the data by station district by 
> category -- there are 79 districts, yielding 474 child tables, which 
> is ~575000 rows per child table. Most of the time I'd imagine only one 
> or two districts would be selected. (Again, hard to know exactly.)
I agee with Matthew Wakeling in a different post: its probably wise to 
first see how fast things can get by using indexes. Only if that fails 
to be fast, partitioning might be an option. (Though sequentially 
scanning 0.5M rows is not cheap).

I experimented a bit with a doy and year function.

-- note: leap year fix must still be added
create or replace function doy(timestamptz) RETURNS float8
as 'select extract(doy from $1);'
language sql
immutable
strict;
create or replace function year(timestamptz) RETURNS float8
as 'select extract(year from $1);'
language sql
immutable
strict;

\d parent
             Table "public.parent"
 Column |           Type           | Modifiers
--------+--------------------------+-----------
 t      | timestamp with time zone |
 y      | smallint                 |
Indexes:
    "doy_i" btree (doy(t))
    "year_i" btree (year(t))

A plan like the following is probably what you want

test=# explain select * from parent where doy(t) between 10 and 20 and 
year(t) between 1900 and 2009;
                                                                               
QUERY 
PLAN                                                                                

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on parent  (cost=9.95..14.97 rows=1 width=10)
   Recheck Cond: ((year(t) >= 1900::double precision) AND (year(t) <= 
2009::double precision) AND (doy(t) >= 10::double precision) AND (doy(t) 
<= 20::double precision))
   ->  BitmapAnd  (cost=9.95..9.95 rows=1 width=0)
         ->  Bitmap Index Scan on year_i  (cost=0.00..4.85 rows=10 width=0)
               Index Cond: ((year(t) >= 1900::double precision) AND 
(year(t) <= 2009::double precision))
         ->  Bitmap Index Scan on doy_i  (cost=0.00..4.85 rows=10 width=0)
               Index Cond: ((doy(t) >= 10::double precision) AND (doy(t) 
<= 20::double precision))
(7 rows)

regards,
Yeb Havinga