Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 3986F63283D for ; Thu, 29 Jul 2010 12:21:01 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 00701-02 for ; Thu, 29 Jul 2010 15:20:53 +0000 (UTC) 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 79C2D6322DA for ; Thu, 29 Jul 2010 12:20:53 -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 o6TFKjHe012654; Thu, 29 Jul 2010 11:20:46 -0400 (EDT) To: tv@fuzzy.cz cc: "Chris" , "PostgreSQL Performance" Subject: Re: planner index choice In-reply-to: <2727.85.161.90.190.1280402094.squirrel@sq.gransy.com> References: <4C50D07D.4040004@gmail.com> <628.1280375626@sss.pgh.pa.us> <4C512DD3.9030201@gmail.com> <2727.85.161.90.190.1280402094.squirrel@sq.gransy.com> Comments: In-reply-to tv@fuzzy.cz message dated "Thu, 29 Jul 2010 13:14:54 +0200" Date: Thu, 29 Jul 2010 11:20:45 -0400 Message-ID: <12653.1280416845@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201007/447 X-Sequence-Number: 39836 tv@fuzzy.cz writes: >> http://explain.depesz.com/s/br9 >> http://explain.depesz.com/s/gxH > Well, I don't have time to do a thorough analysis right now, but in all > the plans you've posted there are quite high values in the "Rows x" column > (e.g. the 5727.5 value). > That means a significant difference in estimated and actual row number, > which may lead to poor choice of indexes etc. The planner may simply think > the index is better due to imprecise statistics etc. Yeah. The sq_ast_attr_val_attrid scan is a lot more selective than the planner is guessing (3378 rows estimated vs an average of 15 actual), and I think that is making the difference. If you look at the estimated row counts and costs, it's expecting that adding the second index will cut the number of heap fetches about 7x, hence saving somewhere around 4800 cost units in the heapscan step, more than it thinks the indexscan will cost. But taking 15 row fetches down to 2 isn't nearly enough to pay for the extra indexscan. > Try to increase te statistics target for the columns, e.g. > ALTER TABLE table ALTER COLUMN column SET STATISTICS integer It's worth a try but I'm not sure how much it'll help. A different line of attack is to play with the planner cost parameters. In particular, reducing random_page_cost would reduce the estimated cost of the heap fetches and thus discourage it from using the extra index. If you're working with mostly-cached tables then this would probably improve behavior overall, too. regards, tom lane