Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using index with order desc

Using index with order desc

From
Dave Smith
Date:
Using pg 7.2.1

If I have a simple table transfer

company_id int,
module character(2)
otherfields ...

and an index
CREATE INDEX transfer_5 on transfer(company_id,module);

with the query

explain select * from transfer where company_id=1012 order by
company_id,module desc;

I get ..

NOTICE:  QUERY PLAN:

Sort  (cost=462.74..462.74 rows=117 width=176)
  ->  Index Scan using transfer_5 on transfer  (cost=0.00..458.71
rows=117 width=176)

Other info ..

select count(*) from transfer  where company_id=1012;
 count
-------
  5264
(1 row)

import=# select count(*) from transfer;
 count
-------
 23481
(1 row)



Why does it have to sort it?  The desc is at the end and the first
argument is constant. Is there some way I can force postgres to read the
index backwards and save the sort step?

Re: Using index with order desc

From
"Peter Gibbs"
Date:
"Dave Smith" wrote:

> Using pg 7.2.1
>
> If I have a simple table transfer
>
> company_id int,
> module character(2)
> otherfields ...
>
> and an index
> CREATE INDEX transfer_5 on transfer(company_id,module);
>
> with the query
>
> explain select * from transfer where company_id=1012 order by
> company_id,module desc;
>
> I get ..
>
> NOTICE:  QUERY PLAN:
>
> Sort  (cost=462.74..462.74 rows=117 width=176)
>   ->  Index Scan using transfer_5 on transfer  (cost=0.00..458.71
> rows=117 width=176)
>
>
> Why does it have to sort it?  The desc is at the end and the first
> argument is constant. Is there some way I can force postgres to read the
> index backwards and save the sort step?
>

Try:

explain select * from transfer
  where company_id=1012
  order by company_id desc, module desc;

--
Peter Gibbs
EmKel Systems

Re: Using index with order desc

From
"Peter Gibbs"
Date:
"Dave Smith" wrote:


> Using pg 7.2.1
>
> If I have a simple table transfer
>
> company_id int,
> module character(2)
> otherfields ...
>
> and an index
> CREATE INDEX transfer_5 on transfer(company_id,module);
>
> with the query
>
> explain select * from transfer where company_id=1012 order by
> company_id,module desc;
>
> I get ..
>
> NOTICE:  QUERY PLAN:
>
> Sort  (cost=462.74..462.74 rows=117 width=176)
>   ->  Index Scan using transfer_5 on transfer  (cost=0.00..458.71
> rows=117 width=176)
>
>
> Why does it have to sort it?  The desc is at the end and the first
> argument is constant. Is there some way I can force postgres to read the
> index backwards and save the sort step?
>

Try:

explain select * from transfer
  where company_id=1012
  order by company_id desc, module desc;

--
Peter Gibbs
EmKel Systems

Re: Using index with order desc

From
Helge Bahmann
Date:
On 16 Jul 2002 Dave Smith wrote:

> CREATE INDEX transfer_5 on transfer(company_id,module);
[snip]
> explain select * from transfer where company_id=1012 order by
> company_id,module desc;
[snip]
> NOTICE:  QUERY PLAN:
>
> Sort  (cost=462.74..462.74 rows=117 width=176)
>   ->  Index Scan using transfer_5 on transfer  (cost=0.00..458.71
> rows=117 width=176)

yes, similiar problem once hit me as well... try:

explain select * from transfer where company_id=1012 order by
company_id desc, module desc;
           ^^^^

Regards
--
Helge Bahmann <bahmann@math.tu-freiberg.de>             /| \__
The past: Smart users in front of dumb terminals       /_|____\
                                                     _/\ |   __)
$ ./configure                                        \\ \|__/__|
checking whether build environment is sane... yes     \\/___/ |
checking for AIX... no (we already did this)            |

Re: Using index with order desc

From
Manfred Koizar
Date:
On 16 Jul 2002 16:35:18 -0400, Dave Smith <dave.smith@candata.com>
wrote:
>CREATE INDEX transfer_5 on transfer(company_id,module);
>
>explain select * from transfer where company_id=1012 order by
>company_id,module desc;

Dave,
    ...
    ORDER BY company_id DESC, module DESC
                            ^^^^

*might* help.

Servus
 Manfred