From: tv@fuzzy.cz
To: Chris <dmagick@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: planner index choice
Date: Thu, 29 Jul 2010 13:14:54 +0200 (CEST)
Message-ID: <2727.85.161.90.190.1280402094.squirrel@sq.gransy.com> (raw)
In-Reply-To: <4C512DD3.9030201@gmail.com>
References: <4C50D07D.4040004@gmail.com>
<628.1280375626@sss.pgh.pa.us>
<4C512DD3.9030201@gmail.com>
> 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.
Try to increase te statistics target for the columns, e.g.
ALTER TABLE table ALTER COLUMN column SET STATISTICS integer
where "integer" is between 0 and 1000 (the default value is 10 so use 100
or maybe 1000), run analyze and try to run the query again.
Tomas
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: tv@fuzzy.cz, dmagick@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: planner index choice
In-Reply-To: <2727.85.161.90.190.1280402094.squirrel@sq.gransy.com>
* 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