Re: limit 1 and functional indexes

On Wed, Jan 28, 2004 at 12:23:38 +0100,
Alexandra Birch <alexandra(at)trymedia(dot)com> wrote:
> Hi,
>
> Postgres choses the wrong index when I add limit 1 to the query.
> This should not affect the index chosen.

I don't know the complete answer to your question, but since no one else
has commented I will answer what I can.

It IS reasobable for the planner to choose a different plan when you
add a LIMIT clause to a query.

> I read that functional indexes are sometimes not chosen correctly by
> optimizer.

I don't believe there are any particular problems with functional indexes.
The opitmizer isn't perfect and will sometimes choose poor plans.

> Is there anything I can do to always use the functional index in the
> following queries?

Have you done an ANALYZE of the table recently?

It might be useful to see the EXPLAIN ANALYZE output, rather than just
the EXPLAIN output, as that will give the actual times needed to do
the various steps.

>
> Query with limit 1 choses wrong index:
> ---------------------------------------------------------------------------------------
> explain
> select code
> from transactions
> where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
> order by order_date DESC LIMIT 1
>
> Index Scan Backward using transactions_date_aff on transactions (cost=0.00..930780.96 rows=2879 width=33)
> ---------------------------------------------------------------------------------------
>
> Without limit 1 choses correct index:
> ---------------------------------------------------------------------------------------
> explain
> select code
> from transactions
> where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
> order by order_date DESC
>
> Index Scan using transactions_pop_i on transactions (cost=0.00..11351.72 rows=2879 width=33)
> ---------------------------------------------------------------------------------------
>
> We have postgresql-7.3.2-3.
> Thank you,
>
> Alexandra
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly