Re: Index Analysis: Filters

"Thomas F.O'Connell" <tfo@sitening.com> writes:
> I'm interested to know a little bit more about the postgres
> implementation of indexes. I'm specifically wondering what it means in
> the output of EXPLAIN when a filter is applied.

The index itself is using the condition(s) indicated as "Index Cond" ---
that is, the index scan will pull all rows satisfying "Index Cond" from
the table.  The "Filter" condition, if any, is then evaluated at each
such row to decide whether to return it up to the next plan level.
Basically the filter is whatever conditions apply to the table but can't
be implemented directly with the chosen index.

> Here's what I've got:
> WHERE some_date LIKE '<year>-<month>%' *

Seems like you'd be better off turning this into a range query.  A
textual LIKE is just about the most inefficient way of testing a date
range that I can think of.  How about

WHERE some_date >= 'year-month-01'::date AND some_date <
('year-month-01'::date + '1 month'::interval)::date

(adjust as appropriate if it's really a timestamp column).

            regards, tom lane