Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 4FF906335CF for ; Fri, 20 Mar 2009 12:28:16 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 01179-07 for ; Fri, 20 Mar 2009 12:28:14 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 8C96A633BC4 for ; Fri, 20 Mar 2009 12:28:11 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n2KFRv7F013824; Fri, 20 Mar 2009 11:27:57 -0400 (EDT) To: Richard Huxton cc: Anne Rosset , pgsql-performance@postgresql.org Subject: Re: Need help with one query In-reply-to: <49C3600E.20504@archonet.com> References: <49C2AC75.5060601@collab.net> <49C3600E.20504@archonet.com> Comments: In-reply-to Richard Huxton message dated "Fri, 20 Mar 2009 09:21:18 -0000" Date: Fri, 20 Mar 2009 11:27:57 -0400 Message-ID: <13823.1237562877@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.011 tagged_above=0 required=5 tests=AWL=0.011 X-Spam-Level: X-Archive-Number: 200903/288 X-Sequence-Number: 33210 Richard Huxton writes: >> Hash Join (cost=8.79..253664.55 rows=4 width=136) (actual >> time=4612.674..6683.158 rows=4 loops=1) >> Hash Cond: ((audit_change.audit_entry_id)::text = (audit_entry.id)::text) >> -> Seq Scan on audit_change (cost=0.00..225212.52 rows=7584852 >> width=123) (actual time=0.009..2838.216 rows=7584852 loops=1) >> -> Hash (cost=8.75..8.75 rows=3 width=45) (actual time=0.049..0.049 >> rows=4 loops=1) >> -> Index Scan using audit_entry_object on audit_entry >> (cost=0.00..8.75 rows=3 width=45) (actual time=0.033..0.042 rows=4 loops=1) >> Index Cond: ((object_id)::text = 'artf414029'::text) >> Total runtime: 6683.220 ms > Very odd. It knows the table is large and that the seq-scan is going to > be expensive. Yeah, *very* odd. A nestloop with inner indexscan should have an estimated cost far lower than this plan. What Postgres version is this exactly? Do you have any nondefault planner parameter settings? regards, tom lane