"Dani Oderbolz" <oderbolz@ecologic.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
