> > > > 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.
Thanks - your reply is apreciated :) > It IS reasobable for the planner to choose a different plan when you > add a LIMIT clause to a query. OK - I'll investigate this further. > > 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. OK - but there was some discussion about statistics for functional indexes, for eg: http://archives.postgresql.org/pgsql-general/2004-01/msg00978.php This does not help me solve my problem though :) > > 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? Yip - I should have said we do a daily VACUUM ANALYZE. > 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. I thought the cost values would be enough from the EXPLAIN alone. And the query takes so long to run :( Here is the output of EXPLAIN ANALYZE first with limit 1 then without: explain analyze select code from transactions where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111') order by order_date DESC LIMIT 1; -------------------------------------------------------------------------------------------------- Limit (cost=0.00..332.44 rows=1 width=33) (actual time=377745.75..377745.75 rows=0 loops=1) -> Index Scan Backward using transactions_date_aff on transactions (cost=0.00..982549.96 rows=2956 width=33) (actual time=377718.61..377718.61 rows=0 loops=1) Filter: (upper((pop)::text) = '79BCDC8A4A4F99E7C111111111111111'::text) Total runtime: 378439.32 msec explain analyze select code from transactions where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111') order by order_date DESC; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ ------------- Sort (cost=11824.16..11831.55 rows=2956 width=33) (actual time=248.17..248.17 rows=0 loops=1) Sort Key: order_date -> Index Scan using transactions_pop_i on transactions (cost=0.00..11653.79 rows=2956 width=33) (actual time=126.13..126.13 rows=0 loops=1) Index Cond: (upper((pop)::text) = '79BCDC8A4A4F99E7C111111111111111'::text) Total runtime: 248.25 msec Thank you, Alexandra ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
