Thread: How to make a research
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 !!"
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.walton@csun.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 !!"
>
> ************
>
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@rksystems.com
http://www.rksystems.com
hi...
> 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?
my experience has been that this:
select date_time_field::date from table_name;
is much faster than:
select date_trunc('day',date) from table_name;
perhaps it's just me @;-)
--
Aaron J. Seigo
Sys Admin
> 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 ? See contrib/fulltextindex for fast searching of that type. ~ operator does it already. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Mon, 25 Oct 1999, Aaron J. Seigo wrote:
> hi...
> my experience has been that this:
>
> select date_time_field::date from table_name;
>
> is much faster than:
>
> select date_trunc('day',date) from table_name;
>
Yes, date_trunc() is comlicated routine, this func. must internal parse and
search your 'day' - it is slowly. (.. instead of datetime_date() which make
date from full DateTime only.)
Zakkr
------------------------------------------------------------------------------
<zakkr@zf.jcu.cz> http://home.zf.jcu.cz/~zakkr/
Kim Project: http://home.zf.jcu.cz/~zakkr/kim/ (process manager)
FTP: ftp://ftp2.zf.jcu.cz/users/zakkr/ (C/ncurses/PgSQL)
------------------------------------------------------------------------------
...and cathedral dilapidate
At 12:19 AM 26-10-1999 -0400, Bruce Momjian 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 ? > >See contrib/fulltextindex for fast searching of that type. ~ operator >does it already. What does the ~ operator already do? Does it do a fast indexed search? E.g. "search engine" style searches - keywords, key phrases. Or is that only done by contrib/fulltextindex? Thanks, Link.