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 2828BD1B1D0 for ; Tue, 29 Jun 2004 10:50:35 -0300 (ADT) 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 69865-06 for ; Tue, 29 Jun 2004 13:50:35 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id BE44CD1B1CD for ; Tue, 29 Jun 2004 10:50:31 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5TDoXce028631; Tue, 29 Jun 2004 09:50:33 -0400 (EDT) To: Chris Cheston Cc: Josh Berkus , pgsql-performance@postgresql.org Subject: Re: postgres 7.4 at 100% In-reply-to: References: <1088397641.1105.10.camel@localhost> <17884.1088399982@sss.pgh.pa.us> <200406280947.59420.josh@agliodbs.com> Comments: In-reply-to Chris Cheston message dated "Tue, 29 Jun 2004 01:00:10 -0700" Date: Tue, 29 Jun 2004 09:50:32 -0400 Message-ID: <28630.1088517032@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/293 X-Sequence-Number: 7351 Chris Cheston writes: > Wow, this simple query is taking 676.24 ms to execute! it only takes > 18 ms on our other machine. > This table has 150,000 rows. Is this normal? > live=# explain analyze SELECT id FROM calllogs WHERE from = 'you'; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------- > Seq Scan on calllogs (cost=0.00..136.11 rows=24 width=4) (actual > time=0.30..574.72 rows=143485 loops=1) > Filter: (from = 'you'::character varying) > Total runtime: 676.24 msec > (3 rows) So the query is pulling 140K+ rows out of a table with 150K entries? No chance that an index will help for that. You're fortunate that the thing did not try to use an index though, because it thinks there are only 24 rows matching 'you', which is one of the more spectacular statistical failures I've seen lately. I take it you haven't ANALYZEd this table in a long time? It is hard to believe that your other machine can pull 140K+ rows in 18 msec, though. Are you sure the table contents are the same in both cases? If they are, the only reason I can think of for the discrepancy is a large amount of dead space in this copy of the table. What does VACUUM VERBOSE show for it, and how does that compare to what you see on the other machine? Try a CLUSTER or VACUUM FULL to see if you can shrink the table's physical size (number of pages). regards, tom lane