Slow lookups on dates? Or something else?

From: Stephen Walton <swalton(at)galileo(dot)csun(dot)edu>
To: pgsql-general(at)postgreSQL(dot)org
Subject: Slow lookups on dates? Or something else?
Date: 1999-10-25 16:32:54
Message-ID: Pine.HPX.4.03.9910250925190.11155-100000@galileo.csun.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

--
Stephen Walton, Professor of Physics and Astronomy,
California State University, Northridge
stephen(dot)walton(at)csun(dot)edu

On Sun, 24 Oct 1999, Edouard DESSIOUX wrote:

> Hello,
>
> I've heard that the last version of PostgreSQL
> which i think is 6.5.2 allow to make a search
> in a attribute as a plain text search.
> How can i make that ?
> --
> Edouard DESSIOUX
> Proverbe chien :
> "Si ca se mange pas,
> Si ca se baise pas,
> Pisse dessus !!"
>
> ************
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message E.E. Mellor 1999-10-25 16:57:53 Re: [INTERFACES] Re: LIKE clause
Previous Message Thomas Byrnes 1999-10-25 16:05:26 RE: [INTERFACES] Re: LIKE clause