Re: Optimize date query for large child tables: GiST or GIN?
От | Yeb Havinga |
---|---|
Тема | Re: Optimize date query for large child tables: GiST or GIN? |
Дата | |
Msg-id | 4BF6D5C0.2030902@gmail.com обсуждение исходный текст |
Ответ на | Re: Optimize date query for large child tables: GiST or GIN? (David Jarvis <thangalin@gmail.com>) |
Ответы |
Re: Optimize date query for large child tables: GiST or
GIN?
(Stephen Frost <sfrost@snowman.net>)
|
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления:
Предыдущее
От: Yeb HavingaДата:
Сообщение: Re: Optimize date query for large child tables: GiST or GIN?
Следующее
От: Matthew WakelingДата:
Сообщение: Re: Optimize date query for large child tables: GiST or GIN?