Re: Left joining table to setof function

"Robert Fitzpatrick" <lists(at)webtent(dot)net> writes:

> Indexes:
> "tblsearch_selections_pkey" PRIMARY KEY, btree (search_selection_id)
> "search_selection_unique_idx" UNIQUE, btree (search_id, user_id, selection_value, selection_type)

SELECT *
FROM client_search_id_func(62)
LEFT JOIN tblsearch_selections ON search_id = 62
AND user_id = 'RF'
AND (( selected_value = clientno
AND selection_type = 'client'
) OR (
selected_value = contactno
AND selection_type = 'contact'))

According to the index the unique way to identify a record is (search_id,
user_id, selection_value, selection_type). But your join condition is matching
on (search_id, user_id, ***selected_value***, selection_type).

I'm not sure what these columns are but this looks like a bug. Certainly it
will make PostgreSQL less likely to use the index since it can only use the
first two columns of it which leaves it with few options.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com