Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group