From: David Wilson <david.t.wilson@gmail.com>
To: Sebastjan Trepca <trepca@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Problems with ordering (can't force query planner to use an index)
Date: Tue, 3 Mar 2009 12:34:55 -0500
Message-ID: <e7f9235d0903030934q3352b5ffs23ca0de97ab34252@mail.gmail.com> (raw)
In-Reply-To: <cd329af80903030905y2efa15bdt123779ee8877df4a@mail.gmail.com>
References: <cd329af80903030905y2efa15bdt123779ee8877df4a@mail.gmail.com>
On Tue, Mar 3, 2009 at 12:05 PM, Sebastjan Trepca <trepca@gmail.com> wrote:
> -> Nested Loop (cost=0.00..31883.13 rows=2861 width=860)
> (actual time=0.089..543.497 rows=68505 loops=1)
> -> Index Scan using core_accessor_fresh_idx on
> core_accessor (cost=0.00..5460.07 rows=2970 width=92) (actual
> time=0.068..54.921 rows=69312 loops=1)
> Index Cond: ((slot_id = 472) AND (slot_type_id =
> 119) AND (label = ''::text) AND (user_id = 0) AND (role = 0) AND
> (publish_state >= 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.
--
- David T. Wilson
david.t.wilson@gmail.com
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: david.t.wilson@gmail.com, trepca@gmail.com
Subject: Re: Problems with ordering (can't force query planner to use an index)
In-Reply-To: <e7f9235d0903030934q3352b5ffs23ca0de97ab34252@mail.gmail.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