Re: Determining which index to create

On Wed, 21 Nov 2001, 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)
>
>
> It's a bit better but still quite long, depending on how many rows for a
> particular motid.
> Dropping the "desc" in the "order by date" clause makes things much faster,
> but I need the
> results in reverse chronological order!

Hmm, it looks like the sort is the expensive bit even though it's
estimating something low for it (relative to the index scan).
Have you tried setting sort_mem higher than the defaults (which are
really low) to see if it's just going out to disk for the sort.