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

Re: I have a question about using index in order statement.

From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
To: kevin <kevin(at)mail(dot)kinew(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: I have a question about using index in order statement.
Date: 2007-11-02 11:54:40
Message-ID: 472B1000.6000208@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugs
kevin wrote:
> Question: 
> I have a question about using index in order statement.
> Why index ix_2 work by Seq Scan and index ix_3 work by Index Scan.
> 
> Example :
> 
> ix_2 condition :
> When I try
> 
>   explain
>   select * from a_test 
>   order by code_ desc
> 
> Postgresql response 
>   Sort  (cost=100001815.08..100001852.56 rows=14990 width=56)
>     Sort Key: code_
>     ->  Seq Scan on a_test  (cost=100000000.00..100000260.90 rows=14990 width=56)
> 
> ix_3 condition :
> When I try
> 
>   explain
>   select * from a_test 
>   order by lower(code_) desc
> 
> Postgresql response 
>     Index Scan using ix_3 on a_test  (cost=0.00..769.27 rows=14990 width=18)

Thanks for the report. This seems to have been broken by this patch back 
in May:

http://archives.postgresql.org/pgsql-committers/2007-05/msg00394.php

that wraps pathkey expressions with a relabel node. Because of that, 
get_eclass_for_sort_expr doesn't recognize that the ordering of the 
index matches that of the query.

Attached is a patch that fixes that test case. I'm not very familiar 
with that piece of code, though, and I have a sneaking suspicion that 
the patch is either not general enough, there may be other places where 
we should ignore relabel nodes, or it brakes something else.

I'm surprised this hasn't been noticed before. It doesn't happen with 
text datatype, but varchar is very common datatype as well.

PS. Kevin, in the future, please specify which PostgreSQL version you're 
using. The fact that the above DDL statements don't work until 8.3beta 
releases gave it away this time :-).

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Attachment: relabel-order-fix-1.patch
Description: text/x-diff (1.6 KB)

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-11-02 15:53:13
Subject: Re: I have a question about using index in order statement.
Previous:From: Dave PageDate: 2007-11-02 11:20:22
Subject: Re: Postgresql Domain Names

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