Re: question about performance

On Sunday 20 July 2008, Robert Urban <urban@unix-beratung.de> wrote:
> Hi PostgreSQLer,
>
> if I have a table, the_table, with a DATE field, i'll call it 'day', and
> I'd like to find all rows whos day falls within a given month, which of
> the following methods is faster/costs less:
>
> 1.
>
>     SELECT * FROM the_table WHERE day LIKE '2008-01-%';
>
> 2.
>
>     SELECT * FROM the_table
>         WHERE ( day BETWEEN '$month_begin' AND '$month_end' );
>
>     # where $month_begin is '2008-01-01' and $month_end is '2008-01-31';

2. Particularly if you have an index on the day field. Doing text operations
on date fields is not conducive to performance.

--
Alan