Re: Left joining table to setof function - Mailing list pgsql-general

"Robert Fitzpatrick" <lists@webtent.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