Re: question about performance

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