pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bricklen <bricklen@zyahoo.zcomz>
To: pgsql-performance@postgresql.org
Subject: Re: Performance Anomalies in 7.4.5
Date: Thu, 21 Oct 2004 21:04:58 GMT
Message-ID: <_vVdd.31479$z96.8087@clgrps12> (raw)
In-Reply-To: <D307B888-23A0-11D9-8068-000D93AE0944@sitening.com>
References: <D307B888-23A0-11D9-8068-000D93AE0944@sitening.com>

Thomas F.O'Connell wrote:
> I'm seeing some weird behavior on a repurposed server that was wiped  
> clean and set up to run as a database and application server with  
> postgres and Apache, as well as some command-line PHP scripts.
> 
> The box itself is a quad processor (2.4 GHz Intel Xeons) Debian woody  
> GNU/Linux (2.6.2) system.
> 
> postgres is crawling on some fairly routine queries. I'm wondering if  
> this could somehow be related to the fact that this isn't a  
> database-only server, but Apache is not really using any resources when  
> postgres slows to a crawl.
> 
> Here's an example of analysis of a recent query:
> 
> EXPLAIN ANALYZE SELECT COUNT(DISTINCT u.id)
> FROM userdata as u, userdata_history as h
> WHERE h.id = '18181'
> AND h.id = u.id;
>                                                                     
> QUERY PLAN
> ------------------------------------------------------------------------ 
> ------------------------------------------------------------------------
>  Aggregate  (cost=0.02..0.02 rows=1 width=8) (actual  
> time=298321.421..298321.422 rows=1 loops=1)
>    ->  Nested Loop  (cost=0.00..0.01 rows=1 width=8) (actual  
> time=1.771..298305.531 rows=2452 loops=1)
>          Join Filter: ("inner".id = "outer".id)
>          ->  Seq Scan on userdata u  (cost=0.00..0.00 rows=1 width=8)  
> (actual time=0.026..11.869 rows=2452 loops=1)
>          ->  Seq Scan on userdata_history h  (cost=0.00..0.00 rows=1  
> width=8) (actual time=0.005..70.519 rows=41631 loops=2452)
>                Filter: (id = 18181::bigint)
>  Total runtime: 298321.926 ms
> (7 rows)
> 
> userdata has a primary/foreign key on id, which references  
> userdata_history.id, which is a primary key.
> 
> At the time of analysis, the userdata table had < 2,500 rows.  
> userdata_history had < 50,000 rows. I can't imagine how even a seq scan  
> could result in a runtime of nearly 5 minutes in these circumstances.
> 
> Also, doing a count( * ) from each table individually returns nearly  
> instantly.
> 
> I can provide details of postgresql.conf and kernel settings if  
> necessary, but I'm using some pretty well tested settings that I use  
> any time I admin a postgres installation these days based on box  
> resources and database size. I'm more interested in knowing if there  
> are any bird's eye details I should be checking immediately.
> 
> Thanks.
> 
> -tfo
> 
> -- 
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-260-0005

Is your enable_seqscan set to true?
Try it after issuing set enable_seqscan to off;





view thread (15+ messages)  latest in thread

Message-ID: <_vVdd.31479$z96.8087@clgrps12>
Permalink:  ../_vVdd.31479$z96.8087@clgrps12/
Also on:    postgresql.org/message-id/_vVdd.31479$z96.8087@clgrps12

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: bricklen@zyahoo.zcomz
  Subject: Re: Performance Anomalies in 7.4.5
  In-Reply-To: <_vVdd.31479$z96.8087@clgrps12>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox