Search Postgresql Archives

Re: Partial indexes instead of partitions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> AFAIU the OP is trying to give the cache a chance of 
> doing some useful
> work by partitioning by time so it's going to be forced to 
> go to disk
> less.

Exactly

> have you 
> considered a couple of
> "levels" to your hierarchy.  Maybe bi-hourly (~15 
> million records?)
> within the current day and move them over into a "day" 
> table at night

I was going for the partitioned-index approach because
it would avoid re-copying the data over another table.
My idea was: 

1) create partial indexes on today's table
2) at night, create a whole index (not partial) on yesterday's
table
3) drop the partial indexes on yesterday's table

But this doesn't work, because partial indexes aren't 
"appended" the way partitioned tables are... that is, if I have
one index covering half table, and another covering the other
half, if I query the data over the "intersection" I'll always get
a plain table scan, where I would expect the planner to do
an append of the result of 2 index scans...

Would it be something that could be added to the TODO list?
It doesn't look that different from what table partitioning/pruning
does....

Thank you everybody for your replies anyway!




-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux