Re: Slow date comparison

rudy wrote:
>
> 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

Don't use IN ( ... ), use EXISTS.

See the FAQ for more details, but IN won't use an index.

Cheers,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew(at)catalyst(dot)net(dot)nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267