limit 1 and functional indexes
От | Alexandra Birch |
---|---|
Тема | limit 1 and functional indexes |
Дата | |
Msg-id | BJELKOAELOIHMLJIEGHJGENNENAA.alexandra@trymedia.com обсуждение исходный текст |
Ответы |
Re: limit 1 and functional indexes
|
Список | pgsql-sql |
Hi, Postgres choses the wrong index when I add limit 1 to the query. This should not affect the index chosen. I read that functional indexes are sometimes not chosen correctly by optimizer. Is there anything I can do to always use the functional index in the following queries? 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
В списке pgsql-sql по дате отправления: