From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew W. Gibbs <awgibbs@awgibbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: query against large table not using sensible index to find very small amount of data
Date: Tue, 08 Apr 2014 09:55:38 -0400
Message-ID: <23514.1396965338@sss.pgh.pa.us> (raw)
In-Reply-To: <20140408124835.GA5618@raptor.commandosoftware.com>
References: <20140408124835.GA5618@raptor.commandosoftware.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
"Andrew W. Gibbs" <awgibbs@awgibbs.com> writes:
> A very common query against this table is of the form...
> SELECT * FROM events WHERE entity_type_id = XXX ORDER BY published_at DESC LIMIT 25;
> ... to get the most recent 25 events from the table for a given type
> of entity, and generally the query planner does the expected thing of
> using the two-part index on (entity_type_id, published_at). Every now
> and again, though, I have found the query planner deciding that it
> ought use the single column (published_at) index.
What is the estimated rows count according to EXPLAIN when it does that,
versus when it chooses the better plan?
> FWIW, we're running on 8.4.X and using the out-of-the-box
> default_statistics_target setting and haven't dabbled with setting
> table level statistics configurations.
8.4.X is due to reach EOL in July, so you really ought to be thinking
about an upgrade. It's not clear from the given info whether this issue
is fixable with stats configuration adjustments, is a bug already fixed
in later versions, or neither, but we're unlikely to make any significant
changes in the 8.4 planner code at this point...
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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, awgibbs@awgibbs.com
Subject: Re: query against large table not using sensible index to find very small amount of data
In-Reply-To: <23514.1396965338@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