Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 3F4E46348C8 for ; Tue, 3 Mar 2009 13:35:25 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 46618-05 for ; Tue, 3 Mar 2009 13:35:08 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f173.google.com (mail-ew0-f173.google.com [209.85.219.173]) by mail.postgresql.org (Postfix) with ESMTP id 4BB2463491E for ; Tue, 3 Mar 2009 13:35:04 -0400 (AST) Received: by ewy21 with SMTP id 21so3045642ewy.19 for ; Tue, 03 Mar 2009 09:34:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=KsmGKqx/SOCrMCU1qmTz6uIArcsfYg0+adebCc6Mj8c=; b=O2qYcN82E+nwWjSvl4aHPOE5dqR91AOuM1Qkq8XmdMmsLokmM6tk+qHWouqOxBnSAO Lbzo3KO1nX1MelOju6cGj6WRVyXyCGo9VwKVqz2xIFHXQ7ua2a91JZU+WZLAjvZ1U8dn HK7KYWNPXhZlw8IGXFEtsBRqq6NhfjF9mmrQQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=p+0kAiZqc6tUzpbynSVflbapdR5/wlvPd3X9haMrAmWJh2iLelmZQcGJSDCGEagYRn k3ER29FSB7O+pAautDJ+Ncn+HfLfGGGsKl/klheiqWLP6ytTKmbCgqs3RA9RI0C21eiu d+IIBG31qyuEMpHQkjvSatQR9YLCiEC94JpPI= MIME-Version: 1.0 Received: by 10.220.76.1 with SMTP id a1mr2334528vck.101.1236101695462; Tue, 03 Mar 2009 09:34:55 -0800 (PST) In-Reply-To: References: Date: Tue, 3 Mar 2009 12:34:55 -0500 Message-ID: Subject: Re: Problems with ordering (can't force query planner to use an index) From: David Wilson To: Sebastjan Trepca Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200903/28 X-Sequence-Number: 32950 On Tue, Mar 3, 2009 at 12:05 PM, Sebastjan Trepca wrote: > =A0 =A0 =A0 =A0 -> =A0Nested Loop =A0(cost=3D0.00..31883.13 rows=3D2861 w= idth=3D860) > (actual time=3D0.089..543.497 rows=3D68505 loops=3D1) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using core_accessor_fresh_id= x on > core_accessor =A0(cost=3D0.00..5460.07 rows=3D2970 width=3D92) (actual > time=3D0.068..54.921 rows=3D69312 loops=3D1) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((slot_id =3D 472) AN= D (slot_type_id =3D > 119) AND (label =3D ''::text) AND (user_id =3D 0) AND (role =3D 0) AND > (publish_state >=3D 60)) That index scan on core_accessor_fresh_idx has a pretty big disparity between what the planer expects to get (2970 rows) and what it actually gets (69312 rows). You should try increasing the statistics target if you haven't, then re-analyze and try the query again to see if the planner picks something better. The default of 10 is pretty small- try 100, or higher. --=20 - David T. Wilson david.t.wilson@gmail.com