Running Red Hat 7.0 kernel 2.2.16
Front End App is Cold Fusion
We only have about 22000 rows in the article table and it will grow quite a bit
When I turn debugging on (cold fusion) this query takes a while. Almost 3
rstDispThread (Records=6, Time=2982ms)
getNumPostsInThread(a.id_article,0) AS numPosts,
isThreadNew(a.id_article,'2001-01-2617:28:37',0) AS intThreadNew,
FROM ARTICLE a, USER_TAB ut
WHERE a.id_article IN (75254,76255,79262,84264,94273,94277)
AND a.author = ut.id_user
ORDER BY a.date_create ASC
I have tried numerous indexing techniques and starting up the database with
larger Buffer size, and even the dreaded -F option, but nothing seems to help.
The two functions getNumPostsInThread and isThreadNew are pretty quick. I have
found the date comparison to be slow and was wondering if that could be sped
This is part of the isThreadNew function, when I strip everything out and only
leave the WHERE date_create >=
TO_TIMESTAMP('2001-01-2617:28:37',YYYY-MM-DDHH24:MI:SS) clause and only pass
one article id to it - the query still takes .5 sec!! Is there any known
slowness attributed with date comparisons, etc??
WHERE date_create >= TO_TIMESTAMP('2001-01-2617:28:37',YYYY-MM-DDHH24:MI:SS)
AND (id_article = 79262
OR reply_to = 79262);
Does anyone have any ideas how to speed this up??
I also read something about being able to create an index based on a function
or am I way off base again?
Thanks in Advance,
In response to
pgsql-novice by date
|Next:||From: pejac||Date: 2001-01-30 15:24:50|
|Subject: re : Slow date comparison|
|Previous:||From: Brett W. McCoy||Date: 2001-01-29 23:48:47|
|Subject: Re: Best WebInterface...?|