Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 849F99F9776 for ; Fri, 31 Aug 2007 18:07:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 12737-04 for ; Fri, 31 Aug 2007 18:07:15 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from itspmx51.state.ms.us (relay.ms.gov [205.144.225.177]) by postgresql.org (Postfix) with ESMTP id EEE599F976D for ; Fri, 31 Aug 2007 18:07:18 -0300 (ADT) Received: from itspmx51.state.ms.us (localhost [127.0.0.1]) by localhost (Postfix) with SMTP id 5E0BF5B17 for ; Thu, 31 Aug 2000 16:07:12 -0500 (CDT) Received: from rray.drdc.mstc.ms.gov (unknown [10.3.192.2]) by itspmx51.state.ms.us (Postfix) with ESMTP id 4BF2E5B13 for ; Thu, 31 Aug 2000 16:07:12 -0500 (CDT) Received: from rray.drdc.mstc.ms.gov (localhost.localdomain [127.0.0.1]) by rray.drdc.mstc.ms.gov (8.13.8/8.13.8) with ESMTP id l7VL7HQf004643 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Fri, 31 Aug 2007 16:07:17 -0500 Received: from localhost (rray@localhost) by rray.drdc.mstc.ms.gov (8.13.8/8.13.8/Submit) with ESMTP id l7VL7HdD004640 for ; Fri, 31 Aug 2007 16:07:17 -0500 X-Authentication-Warning: rray.drdc.mstc.ms.gov: rray owned process doing -bs Date: Fri, 31 Aug 2007 16:07:17 -0500 (CDT) From: Richard Ray X-X-Sender: rray@rray.drdc.mstc.ms.gov To: pgsql-sql@postgresql.org Subject: Re: How to influence the planner In-Reply-To: <373D2F52-E9CD-485A-9BF1-B019349FA5EC@seespotcode.net> Message-ID: References: <373D2F52-E9CD-485A-9BF1-B019349FA5EC@seespotcode.net> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: ClamAV 0.91.1/4112/Fri Aug 31 15:01:59 2007 on rray.drdc.mstc.ms.gov X-Virus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/271 X-Sequence-Number: 29209 On Fri, 31 Aug 2007, Michael Glaesemann wrote: > > On Aug 31, 2007, at 13:32 , Richard Ray wrote: > >> "select * from t1 where length(bar) = 0;" runs about 2 minutes >> "select * from t1 where length(bar) = 0 order by foo ;" ran until I >> stopped it after about 20 minutes > > > EXPLAIN ANALYZE will help you see what the planner is doing to produce the > results. mda=# EXPLAIN ANALYZE select * from t1 where length(bar) = 0 order by foo; QUERY PLAN --------------------------------------------------------------- Index Scan using t1_pkey on t1 (cost=0.00..46698478.18 rows=60038 width=334) (actual time=4612.740..2349582.520 rows=32705 loops=1) Filter: (length(bar) = 0) Total runtime: 2349614.258 ms (3 rows) mda=# EXPLAIN ANALYZE select * from t1 where length(bar) = 0; QUERY PLAN --------------------------------------------------------------- Seq Scan on t1 (cost=100000000.00..102020349.17 rows=60038 width=334) (actual time=39.065..108645.233 rows=32705 loops=1) Filter: (length(bar) = 0) Total runtime: 108677.759 ms (3 rows) mda=# The index for foo on t1 is the primary index t1_pkey Why is it slower using the index > Have you recently ANALYZEd t1? I run vacuum analyze nightly > If length(bar) = 0 is a common > operation on this table, you might consider using an expression index on t1: > > create index t1_length_bar_idx on t1 (length(bar)); This is a one time procedure to fix some data but I've had this problem before I'm running PostgreSQL 8.1.0 on Fedora Core 6 > > You might want to ask on the performance list as well, as this is right up > their alley. > > Hope this gets you started on the right track. > > Michael Glaesemann > grzm seespotcode net > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings