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

performance help

From: Ernie <ernie(dot)cline(at)ipgdirect(dot)com>
To: "postgres" <pgsql-general(at)postgresql(dot)org>
Subject: performance help
Date: 2000-07-26 16:02:11
Message-ID: 200007262002.QAA27997@localhost.localdomain (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-general by date

Next:From: Fetter, David MDate: 2000-07-26 16:32:34
Subject: Perl Module Installed?
Previous:From: Tom LaneDate: 2000-07-26 15:12:08
Subject: Re: Some questions on user defined types and functions.

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