Skip site navigation (1) Skip section navigation (2)

Re: planner index choice

From: tv(at)fuzzy(dot)cz
To: "Chris" <dmagick(at)gmail(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "PostgreSQL Performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: planner index choice
Date: 2010-07-29 11:14:54
Message-ID: 2727.85.161.90.190.1280402094.squirrel@sq.gransy.com (view raw or flat)
Thread:
Lists: pgsql-performance
> 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


In response to

Responses

pgsql-performance by date

Next:From: Michael StoneDate: 2010-07-29 14:45:34
Subject: Re: Testing Sandforce SSD
Previous:From: ChrisDate: 2010-07-29 07:29:23
Subject: Re: planner index choice

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group