performance help - Mailing list pgsql-general

Hello all,
In writing a perl/DBI based application for our customers, we noticed some very
intersting behavoir.  Against 2 tables, running a select, when you we do a
WHERE clause with a lot of items in it ... the query is fast.  When we do it
with just one or two items, its hugely slower!    Enough so that postgres goes to
100Megs of memory to do the query, and the box begins to swap out!  Here are
the details:

Pentium III 550Mhz
128Mb Ram
20Meg IDE disk

Postgres 7.0.0 (Should upgrade to 7.0.2?)
Installed and compiled by me, but no compiler tricks

Here is the SQL thats fast:

cw=# SELECT distinct n.news_id, headline, link, to_char(created, 'mm-dd-yyyy
hh24:mi'),
cw-# created FROM news_article_summary n, news_cat nc WHERE n.news_id =
nc.news_id AND
cw-# created > CURRENT_TIMESTAMP-30 AND nc.code_id
cw-#
in(14,227,326,321,327,4301,5179,5100,585,5175,567,5185,568,5381,5109,554,5621,5
462,
cw(# 597,5324,5117,569,5142,570,5327,571,5167,5481,5145) ORDER BY created desc;

Here is the SQL that's slow:
cw=# SELECT distinct n.news_id, headline, link,
cw-#  to_char(created, 'mm-dd-yyyy hh24:mi'),
cw-# created FROM news_article_summary n, news_cat nc
cw-# WHERE n.news_id = nc.news_id AND created > CURRENT_TIMESTAMP-30 AND
cw-# nc.code_id in(4261,4182) ORDER BY created desc;

Here's an explain on the above query:

NOTICE:  QUERY PLAN:

Unique    (cost=60322.14..60559.66 rows=1900 width=48)
  ->  Sort  (cost=60322.14..60322.14 rows=19001 width=48)
    ->  Nested Loop  (cost=0.00..58651.80 rows=19001 width=48)
          ->  Seq Scan on news_article_summary n  (cost=0.00..416.14
rows=1898 width=36)
          ->  Index Scan using news_cat_news_id on news_cat nc
(cost=0.00..30.53 rows=10 width=12)

EXPLAIN
(I ran this, but I'm not really sure what any of it means!  Is there an
explanation somewhere).

Here is news_article_summary and news_cat

cw=# \d news_article_summary
    Table "news_article_summary"
    Attribute      |    Type      | Modifier
------------------+---------------+----------
 news_id      | integer      | not null
 headline      | varchar(255)  |
 brief          | varchar(4000) |
 top_of_news      | varchar(1)      |
 urgent       | varchar(1)      |
 created      | timestamp      |
 link          | varchar(255)  |
 region_id      | integer      |
 ftfl          | varchar(1)      |
 service_mark      | varchar(1)      |
 syndication_file | varchar(12)   |
 ufs_file      | varchar(16)   |
Indices: news_article_summary_news_id,
     x1
Constraint: (news_id NOTNULL)

cw=# \d news_cat
      Table "news_cat"
 Attribute |     Type      | Modifier
-----------+--------------+----------
 news_id   | numeric(7,0) | not null
 code_id   | numeric(7,0) | not null
Indices: news_cat_code_id,
     news_cat_news_id


Let me know if more info would be helpfull... I'd appreciate any pointers
anyone could give me, I'm new to this RDBMS stuff.  Oh,here are my postmaster
options

PGOPTS="-B 256 -N 64 -S 2048"

Thanks!

-ernie