Re: Retrieve month from date

On 4/20/07, RPK <rpk(dot)general(at)gmail(dot)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