Re: Determining which index to create - Mailing list pgsql-general

From Martijn van Oosterhout
Subject Re: Determining which index to create
Date
Msg-id 20011122122112.A2666@svana.org
Whole thread Raw
In response to Re: Determining which index to create  (Eric Cholet <cholet@logilune.com>)
Responses Re: Determining which index to create
List pgsql-general
On Wed, Nov 21, 2001 at 04:09:52PM +0100, Eric Cholet wrote:
> => explain select * from dico_frs where motid=4742 order by date desc limit
> 10;
> NOTICE:  QUERY PLAN:
>
> Limit  (cost=46172.25..46172.25 rows=10 width=16)
>   ->  Sort  (cost=46172.25..46172.25 rows=11382 width=16)
>         ->  Index Scan using dico_frs_motid_date on dico_frs
> (cost=0.00..45405.39 rows=11382 width=16)

That's wrong. It doesn't seem to realise that a reverse scan on the index
would give the right answer. Note that that's only true because you're
selecting only a single motid. If there were multiple, a reverse scan would
definitly not be appropriate.

Not sure how to fix this though.

Functional index?
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Magnetism, electricity and motion are like a three-for-two special offer:
> if you have two of them, the third one comes free.

pgsql-general by date:

Previous
From: Russ McBride
Date:
Subject: Re: PG vs MySQL
Next
From: Dado Feigenblatt
Date:
Subject: backup: pg_dumpall and full backups in general