Re: Partition DB Tables by month

"Dani Oderbolz" <oderbolz(at)ecologic(dot)de> wrote:
> Mendola Gaetano wrote:
>
> >you can easilly accomplish this using a partial index.
> >
> >
> Would that really work with a view?
> Can you post a syntax example for this?

CREATE TABLE foo (
field_a
field_b
......
fast_search BOOLEAN NOT NULL DEFAULT 1,
time_stamp TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_fast_search ON foo ( fast_search ) where fast_search = 't';

for each field to index:
CREATE INDEX idx_field_a ON foo ( field_a ) where fast_search = 't';
CREATE INDEX idx_field_b ON foo ( field_b ) where fast_search = 't';

at the beginning of each month you can now do:

UPDATE foo SET fast_search = 'f'
WHERE time_stamp < now() AND
fast_search = 't';

Your improved query for the last month:

SELECT *
FROM foo
WHERE fast_search = 't' AND
<field_a> = XXXXX AND
<field_b> = YYYYYY;

I hope this help

Regards
Gaetano