X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 3BDB4D1B510 for ; Fri, 24 Oct 2003 15:19:38 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 11409-06 for ; Fri, 24 Oct 2003 12:19:07 -0300 (ADT) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 49ED8D1B504 for ; Fri, 24 Oct 2003 12:19:07 -0300 (ADT) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 2508A369D2; Fri, 24 Oct 2003 11:19:06 -0400 (EDT) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1AD3iU-0000aj-00; Fri, 24 Oct 2003 11:19:06 -0400 To: "Medora Schauer" Cc: "Josh Berkus" , "postgresql" Subject: Re: slow select References: <906E2C446A276048A1BE283F17BCB12CDB422A@encounter.fairind.fairfield.com> In-Reply-To: <906E2C446A276048A1BE283F17BCB12CDB422A@encounter.fairind.fairfield.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 24 Oct 2003 11:19:05 -0400 Message-ID: <871xt2wtna.fsf@stark.dyndns.tv> Lines: 19 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 postgresql.org X-Archive-Number: 200310/614 X-Sequence-Number: 4362 "Medora Schauer" writes: > Merge Join (cost=0.00..287726.10 rows=100221 width=58) (actual time=61.60..5975.63 rows=100425 loops=1) > Merge Cond: (("outer".shot_line_num = "inner".shot_line_num) AND ("outer".shotpoint = "inner".shotpoint)) > -> Index Scan using hsot_record_idx on shot_record r (cost=0.00..123080.11 rows=100425 width=46) (actual time=24.15..2710.31 rows=100425 loops=1) > -> Index Scan using shotpoint_idx on shotpoint p (cost=0.00..467924.54 rows=290106 width=12) (actual time=37.38..1379.64 rows=100749 loops=1) > Total runtime: 6086.32 msec > > So why did were the indices not used before when they yield a better plan? There's another reason. Notice it thinks the second table will return 290k records. In fact it only returns 100k records. So it's optimizing on the assumption that it will have to read 3x as many records as it actually will... I'm not clear if there's anything you can do to improve this estimate though. -- greg