On Thu, 4 Oct 2007, Tom Lane wrote:
There's some limited smarts in there about deciding that leading columns
of an index don't matter to the sort ordering if they're constrained to
just one value by the query. But it doesn't catch the case you need,
which is that columns of an ORDER BY reques
Ben <[EMAIL PROTECTED]> writes:
> On Thu, 4 Oct 2007, Simon Riggs wrote:
> I thought that might explain it, but then I'm surprised that it can still
> use an index when the first two columns of the index aren't in the query.
> Wouldn't that mean that it might have to walk the entire index to find
Scott Marlowe wrote:
On 10/4/07, Ben <[EMAIL PROTECTED]> wrote:
On Thu, 4 Oct 2007, Tom Lane wrote:
You're being about as clear as mud here, except that you obviously lied
about what you were doing in your first message. If you have a planner
problem, show us the *exact* query, the *exact* ta
On 10/4/07, Ben <[EMAIL PROTECTED]> wrote:
> On Thu, 4 Oct 2007, Tom Lane wrote:
>
> > You're being about as clear as mud here, except that you obviously lied
> > about what you were doing in your first message. If you have a planner
> > problem, show us the *exact* query, the *exact* table defini
On Thu, 4 Oct 2007, Simon Riggs wrote:
In the first query, Postgres cannot use the index because the sort order
of the index does not match the sort order of the query. When you change
the sort order of the query so that it matches that of the index, then
the index is used.
If you define your i
On 10/4/07, Ben <[EMAIL PROTECTED]> wrote:
> If I have this:
>
> create table foo (bar int primary key);
>
> ...then in my ideal world, Postgres would be able to use that index on bar
> to help me with this:
>
> select bar from foo order by bar desc limit 20;
>
> But in my experience, PG8.2 is doin
On Thu, 2007-10-04 at 12:52 -0700, Ben wrote:
> The original, slow query:
>
> explain analyze SELECT * FROM log WHERE clientkey in
> ('450101') AND premiseskey in
> ('450101') and logicaldel = 'N'
> ORDER BY logtime desc, logkey desc, clientkey d
On Thu, 4 Oct 2007, Tom Lane wrote:
You're being about as clear as mud here, except that you obviously lied
about what you were doing in your first message. If you have a planner
problem, show us the *exact* query, the *exact* table definition, and
unfaked EXPLAIN ANALYZE output.
I didn't rea
Ben <[EMAIL PROTECTED]> writes:
> No, the tables are recently analyzed and there are a couple hundred
> thousand rows in there. But I think I just figured it out it's a
> 3-column index, and two columns of that index are the same for every row.
> When I drop those two columns from the orderi
On Thu, 4 Oct 2007, Bill Moran wrote:
However, 2 guesses:
1) You never analyzed the table, thus PG has awful statistics and
doesn't know how to pick a good plan.
2) You have so few rows in the table that a seq scan is actually
faster than an index scan, which is why PG uses it instead.
No,
Ben <[EMAIL PROTECTED]> schrieb:
> If I have this:
>
> create table foo (bar int primary key);
>
> ...then in my ideal world, Postgres would be able to use that index on bar
> to help me with this:
>
> select bar from foo order by bar desc limit 20;
>
> But in my experience, PG8.2 is doing a
In response to Ben <[EMAIL PROTECTED]>:
> If I have this:
>
> create table foo (bar int primary key);
>
> ...then in my ideal world, Postgres would be able to use that index on bar
> to help me with this:
>
> select bar from foo order by bar desc limit 20;
>
> But in my experience, PG8.2 is d
On Thu, 2007-10-04 at 11:00 -0700, Ben wrote:
> If I have this:
>
> create table foo (bar int primary key);
>
> ...then in my ideal world, Postgres would be able to use that index on bar
> to help me with this:
>
> select bar from foo order by bar desc limit 20;
>
> But in my experience, PG8.2
13 matches
Mail list logo
