Re: Followup - expression (functional) index use in joins

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Followup - expression (functional) index use in joins
Дата
Msg-id 23864.1070301901@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Followup - expression (functional) index use in joins  (Roger Ging <rging@paccomsys.com>)
Список pgsql-performance
Roger Ging <rging@paccomsys.com> writes:
> See results below.

Thanks for the report.  It seems the issue is that the estimate for the
number of matching rows is way off (870 vs 8):

>    ->  Index Scan using idx_program_mri_id_no_program on program p
> (cost=0.00..3400.74 rows=870 width=40) (actual time=0.041..0.127 rows=8
> loops=32)

which discourages the planner from using a nestloop.  I'm not sure we
can do much about this in the short term.  There's been some discussion
of keeping statistics about the values of functional indexes, which
would allow a better estimate to be made in this situation; but that
won't happen before 7.5 at the earliest.

> Turning enable_hashjoin off made the query run as it had on v7.3.  We
> have worked around this by changing the index from a function call to a
> direct index on a new column with the results of the function maintained
> by a trigger.  Would there be performance issues from leaving
> enable_hashjoin off, or do you recomend enabling it, and working around
> function calls in indices?

Turning enable_hashjoin off globally would be a *really bad* idea IMHO.
The workaround with a derived column seems okay, though certainly a pain
in the neck.  Can you manage to turn off enable_hashjoin just for this
one query?  That might be the best short-term workaround.

            regards, tom lane

В списке pgsql-performance по дате отправления:

Предыдущее
От: Roger Ging
Дата:
Сообщение: Re: Followup - expression (functional) index use in joins
Следующее
От: Josh Berkus
Дата:
Сообщение: Re: cross table indexes or something?