Re: Retrieve month from date

On 4/20/07, RPK <rpk.general@gmail.com> wrote:
>
> Thanks both of you,
>
> I ran EXPLAIN command on above suggested query and got following result:
>
> "Aggregate  (cost=2.77..2.79 rows=1 width=10)"
> "  ->  Seq Scan on studentfeespayment  (cost=0.00..2.77 rows=1 width=10)"
> "        Filter: (date_part('month'::text, (recieptmonthyear)::timestamp
> without time zone) = 4::double precision)"
>
> What does this mean? Can I optimize it better?

create index lala on "StudentFeesPayment"(Extract(Month from
"ReceiptMonthYear"))

explain Select max("ReceiptNo") from "StudentFeesPayment" where
Extract(Month from "ReceiptMonthYear")=4;

Aggregate  (cost=14.97..14.98 rows=1 width=4) ->  Bitmap Heap Scan on "StudentFeesPayment"  (cost=4.33..14.94
rows=10 width=4)       Recheck Cond: (date_part('month'::text,
("ReceiptMonthYear")::timestamp without time zone) = 4::double
precision)       ->  Bitmap Index Scan on lala  (cost=0.00..4.33 rows=10 width=0)             Index Cond:
(date_part('month'::text,
("ReceiptMonthYear")::timestamp without time zone) = 4::double
precision)

See:

http://www.postgresql.org/docs/8.2/static/indexes-expressional.html