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 EB0A78B9FE8 for ; Wed, 9 Feb 2005 20:50:25 +0000 (GMT) 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 40534-05 for ; Wed, 9 Feb 2005 20:50:16 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id F1FF48B9F04 for ; Wed, 9 Feb 2005 20:50:17 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1Cyyml-0003rm-00; Wed, 09 Feb 2005 15:50:07 -0500 To: John Arbash Meinel Cc: Silke Trissl , Josh Berkus , pgsql-performance@postgresql.org Subject: Re: Tell postgres which index to use? References: <420A3309.5040507@informatik.hu-berlin.de> <200502090941.01447.josh@agliodbs.com> <420A4F2A.1060506@informatik.hu-berlin.de> <420A5836.4080605@arbash-meinel.com> In-Reply-To: <420A5836.4080605@arbash-meinel.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 09 Feb 2005 15:50:06 -0500 Message-ID: <873bw5e9vl.fsf@stark.xeocode.com> Lines: 41 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.057 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200502/151 X-Sequence-Number: 10480 John Arbash Meinel writes: > > -> Hash (cost=1418.68..1418.68 rows=3226 width=4) (actual > > time=77.062..77.062 rows=0 loops=1) > > This seems to be at least one of the problems. The planner thinks there > are going to be 3000+ rows, but in reality there are 0. No, that's a red herring. Hash nodes always report 0 rows. > > Nested Loop (cost=0.00..23849.81 rows=7533 width=8) (actual time=0.341..198.162 rows=5798 loops=1) > > -> Seq Scan on pdb_entry (cost=0.00..1418.68 rows=3226 width=4) (actual time=0.145..78.177 rows=3329 loops=1) > > Filter: ((resolution > 0::double precision) AND (resolution < 1.7::double precision)) > > -> Index Scan using chain_pdb_id_ind on "chain" (cost=0.00..6.87 rows=6 width=8) (actual time=0.021..0.027 rows=2 loops=3329) > > Index Cond: ("outer".id = "chain".pdb_id) The actual number of records is pretty close to the estimated number. And the difference seems to come primarily from selectivity of the join where it thinks an average of 6 rows will match every row whereas in fact an average of about 2 rows matches. So it thinks it's going to read about 18,000 records out of 67,000 or about 25%. In that case the sequential scan is almost certainly better. In fact it's going to read about 6,000 or just under 10%, in which case the sequential scan is probably still better but it's not so clear. I suspect the only reason you're seeing such a big difference when I would expect it to be about even is because nearly all the data is cached. In that case the non-sequential access pattern of the nested loop has little effect. You might get away with lowering random_page_cost but since it thinks it's going to read 25% of the table I suspect you'll have to get very close to 1 before it switches over, if it does even then. Be careful about tuning settings like this based on a single query, especially to unrealistically low values. You might also want to try raising the statistics target on pdb_entry. See if that makes the estimate go down from 6 to closer to 2. -- greg