X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4C28C32A06A for ; Thu, 21 Oct 2004 22:21:04 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69877-05 for ; Thu, 21 Oct 2004 21:20:47 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id DBBA532A5A2 for ; Thu, 21 Oct 2004 22:20:48 +0100 (BST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i9LLKjJ7070808 for ; Thu, 21 Oct 2004 21:20:45 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i9LL4ul7065956 for pgsql-performance@postgresql.org; Thu, 21 Oct 2004 21:04:56 GMT From: Bricklen User-Agent: Mozilla Thunderbird 0.8 (X11/20040926) X-Accept-Language: en-us, en MIME-Version: 1.0 X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Performance Anomalies in 7.4.5 References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Lines: 69 Message-ID: <_vVdd.31479$z96.8087@clgrps12> Date: Thu, 21 Oct 2004 21:04:58 GMT To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.1 tagged_above=0.0 required=5.0 tests=NO_DNS_FOR_FROM X-Spam-Level: * X-Archive-Number: 200410/415 X-Sequence-Number: 8891 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;