Re: Index Analysis: Filters

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Thomas F(dot)O'Connell" <tfo(at)sitening(dot)com>
Cc: "PgSQL - General" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Index Analysis: Filters
Date: 2004-09-29 22:59:24
Message-ID: 4281.1096498764@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Thomas F.O'Connell" <tfo(at)sitening(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-09-29 23:17:21 Re: 7.3.4 vacuum/analyze error
Previous Message Tom Lane 2004-09-29 22:49:51 Re: EXTRACT Clarification