Обсуждение: question about performance

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';

thanks,

Rob Urban

am  Sun, dem 20.07.2008, um 20:08:21 +0200 mailte Robert Urban folgendes:
> 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';

Probably the second one, but it depends if you have a propper index.

Other solution: create a functional index based on date_trunc and
rewrite your where-condition also.

Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

A. Kretschmer schrieb:

>> 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';
>
> Probably the second one, but it depends if you have a propper index.
>
> Other solution: create a functional index based on date_trunc and
> rewrite your where-condition also.

Can you give an example?

Greetings,
Torsten

am  Mon, dem 21.07.2008, um  9:40:19 +0200 mailte Torsten Zühlsdorff folgendes:
> A. Kretschmer schrieb:
>
> >>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';
> >
> >Probably the second one, but it depends if you have a propper index.
> >
> >Other solution: create a functional index based on date_trunc and
> >rewrite your where-condition also.
>
> Can you give an example?

Sure. For instance, i have a table called 'frachtschein', this table
contains a field 'ts', type timestamp. First, there are no index:

scholl=# explain analyse select * from frachtschein where date_trunc('month',ts::timestamp)='2008-02-01
00:00:00'::timestamp;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on frachtschein  (cost=0.00..243.89 rows=29 width=84) (actual time=4.967..10.398 rows=524 loops=1)
   Filter: (date_trunc('month'::text, ts) = '2008-02-01 00:00:00'::timestamp without time zone)
 Total runtime: 11.468 ms
(3 rows)


Now i create a index:


scholl=*# create index idx_e on frachtschein(date_trunc('month',ts));
CREATE INDEX


The same select, using the index:


scholl=*# explain analyse select * from frachtschein where date_trunc('month',ts::timestamp)='2008-02-01
00:00:00'::timestamp;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on frachtschein  (cost=2.10..76.12 rows=29 width=84) (actual time=0.336..1.570 rows=524 loops=1)
   Recheck Cond: (date_trunc('month'::text, ts) = '2008-02-01 00:00:00'::timestamp without time zone)
   ->  Bitmap Index Scan on idx_e  (cost=0.00..2.10 rows=29 width=0) (actual time=0.316..0.316 rows=524 loops=1)
         Index Cond: (date_trunc('month'::text, ts) = '2008-02-01 00:00:00'::timestamp without time zone)
 Total runtime: 2.624 ms
(5 rows)




Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net