Running Red Hat 7.0 kernel 2.2.16
PostgreSQL 7.0.3
Front End App is Cold Fusion
We only have about 22000 rows in the article table and it will grow quite a bit
larger.
When I turn debugging on (cold fusion) this query takes a while. Almost 3
sec...
rstDispThread (Records=6, Time=2982ms)
SQL =
SELECT a.id_article,
a.subject,
getNumPostsInThread(a.id_article,0) AS numPosts,
isThreadNew(a.id_article,'2001-01-2617:28:37',0) AS intThreadNew,
a.is_hidden,
a.is_locked,
a.reply_to
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
up?
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??
SELECT COUNT(id_article)
FROM ARTICLE
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,
Rudy Laczkovich