From: Tom Lane <tgl@sss.pgh.pa.us>
To: tv@fuzzy.cz
Cc: Chris <dmagick@gmail.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: planner index choice
Date: Thu, 29 Jul 2010 11:20:45 -0400
Message-ID: <12653.1280416845@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, tv@fuzzy.cz, dmagick@gmail.com
Subject: Re: planner index choice
In-Reply-To: <12653.1280416845@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox