"Robert Fitzpatrick" <lists@xxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
