pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Greg Stark <gsstark@mit.edu>
Cc: pgsql-general@postgresql.org
Subject: Re: index scan with index cond on first column doesn't recognize sort order of second column
Date: 13 Feb 2003 17:10:17 -0500
Message-ID: <87u1f7kf1y.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <20030213113156.T47380-100000@megazone23.bigpanda.com>
References: <20030213113156.T47380-100000@megazone23.bigpanda.com>

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:

> On 13 Feb 2003, Greg Stark wrote:
> 
> > Here's a corner case where the optimizer is doing a redundant sort. I'm not
> > sure if I'm doing something wrong or if it's just something the optimizer
> > doesn't notice.
> 
> I'm guessing that it doesn't realize that in this case the sort is
> redundant since I think it's only necessarily redundant for = singlevalue
> with no ors.

I'm not sure. reading the code there does seem to be a special code path for
ors anyways. This codepath claims to be for non-'or' restriction clauses.

I'm not clear on what truncate_useless_pathkeys is doing. At a guess it's
keeping just the prefix, rather than keeping just the suffix. I think at least
for purposes of merge joins, that this is precisely the wrong thing to do.
Of course in all likelihood that just means I've misinterpreted the code.

If it were changed to just keep the suffix would that break other things?


        /*
         * 2. Match the index against non-'or' restriction clauses.
         */
        restrictclauses = group_clauses_by_indexkey(rel, index);

        /*
         * 3. Compute pathkeys describing index's ordering, if any, then
         * see how many of them are actually useful for this query.
         */
        index_pathkeys = build_index_pathkeys(root, rel, index,
                                              ForwardScanDirection);
        index_is_ordered = (index_pathkeys != NIL);
        useful_pathkeys = truncate_useless_pathkeys(root, rel,
                                                    index_pathkeys);


Incidentally, I tried being cleverer and it still doesn't notice the index
path ordering. I think because of the truncate_useless_pathkeys above.

db=> explain select * from cache_foo join (select *, 893 as key_id from foo_bar) as x  using (key_id,foo_id) ;
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Merge Join  (cost=4107.53..5119.85 rows=2674 width=40)
   Merge Cond: ("outer".foo_id = "inner".foo_id)
   ->  Index Scan using idx_foo_bar_foo on foo_bar  (cost=0.00..859.04 rows=45288 width=8)
   ->  Sort  (cost=4107.53..4114.21 rows=2673 width=32)
         Sort Key: cache_foo.foo_id
         ->  Index Scan using idx_cache_foo_foo on cache_foo  (cost=0.00..3955.38 rows=2673 width=32)
               Index Cond: (key_id = 893)


-- 
greg




view thread (10+ messages)  latest in thread

Message-ID: <87u1f7kf1y.fsf@stark.dyndns.tv>
Permalink:  ../87u1f7kf1y.fsf@stark.dyndns.tv/
Also on:    postgresql.org/message-id/87u1f7kf1y.fsf@stark.dyndns.tv

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: gsstark@mit.edu, sszabo@megazone23.bigpanda.com
  Subject: Re: index scan with index cond on first column doesn't recognize sort order of second column
  In-Reply-To: <87u1f7kf1y.fsf@stark.dyndns.tv>
  References: <20030213113156.T47380-100000@megazone23.bigpanda.com> <87u1f7kf1y.fsf@stark.dyndns.tv>

* 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