Re: Checking = with timestamp field is slow

On Fri, 2004-11-05 at 12:46 +0530, Antony Paul wrote:
> Hi all,
> I have a table which have more than 200000 records. I need to get
> the records which matches like this
>
> where today::date = '2004-11-05';
>
> This is the only condition in the query. There is a btree index on the
> column today.
> Is there any way to optimise it.

Hi Antony,

I take it your field is called "today" (seems dodgy, but these things
happen...). Anywa, have you tried indexing on the truncated value?

create index xyz_date on xyz( today::date );
analyze xyz;

That's one way. It depends on how many of those 200,000 rows are on
each date too, as to whether it will get used by your larger query.

Regards,
Andrew.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267
When in doubt, tell the truth.
-- Mark Twain
-------------------------------------------------------------------------