Re: [GENERAL] Slow lookups on dates? Or something else?

On Mon, 25 Oct 1999, Stephen Walton wrote:

> Hi,
>
> I am a recent PostgreSQL convert (previously using mSQL), largely because
> of its security flexibilty, Python support, and mSQL's licence
> restriction. This is my first post to the list (subscribed yesterday) so
> be patient with me.
>
> I have a database of astronomical images which contains five
> fields: date (DATETIME), cd_name (VARCHAR), cd_filename (VARCHAR),
> wavelength (INT4), and type (VARCHAR). The same data had been previously
> in an mSQL database, but the date was separated into an INT4 of the format
> yyyymmdd and a time field of the form '12:34:56'. I elected to use
> DATETIME on the basis of the documentation recommendation. There are
> about 20,000 records in the table.
>
> Many of the lookups I have to do on this database are on the date, in
> particular on date_trunc('day',date). Such lookups seem much slower
> (5-10x) than they were on mSQL against the yyyymmdd field, with both
> servers on the same hardware. Is this inherent in the format, or is
> PostgreSQL that much slower? If the format is the problem, is there some
> way of adding a field to my table which would be automatically set equal
> to date_trunc('day',date) whenever the date field was set or updated? Or
> should I look elsewhere for performance improvements?
>
> Thanks in advance.
>

Such a query is not as likely to be able to take advantage of an index
on the date column. Make sure you have an index on the column and try
using a range in your queries (... WHERE date >= '1999-04-03 00:00:00'
AND date < '1999-04-03 00:00:00' -- BETWEEN might be problematic since
the syntax for specifying DATETIME constants appears to make provision
for precision down to the second, whereas the actual storage resolution
is to the microsecond).

Hope this helps.

--
Bob Kline
mailto:bkline(at)rksystems(dot)com
http://www.rksystems.com