Skip site navigation (1) Skip section navigation (2)

Slow date comparison

From: rudy <rudy(at)heymax(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Slow date comparison
Date: 2001-01-30 14:37:36
Message-ID: 3A76D1B0.69B096FA@heymax.com (view raw or flat)
Thread:
Lists: pgsql-novice
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



In response to

Responses

pgsql-novice by date

Next:From: pejacDate: 2001-01-30 15:24:50
Subject: re : Slow date comparison
Previous:From: Brett W. McCoyDate: 2001-01-29 23:48:47
Subject: Re: Best WebInterface...?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group