Thread: unneeded joins on view

Hello all,

I am trying to simplify some of the queries I use with my database creating a big view of all the possible attributes
myitems can have, the view is rather large: 

http://pastebin.com/ScnJ8Hd3


I thought that Postgresql would optimize out joins on columns I don't ask for when I use the view but it doesn't, this
query:

SELECT referencia
FROM articulo_view
WHERE referencia = '09411000';


Have this query plan:

http://explain.depesz.com/s/4lW0


Maybe I am surpassing some limit? I have tried changing from_collapse_limit and join_collapse_limit but still the
plannerjoin the unneeded tables. 

Is possible to tell Postgresql do the right thing? If so, how? Thanks!

Regards,
Miguel Angel.

On 04/16/2014 06:13 PM, Linos wrote:
> I thought that Postgresql would optimize out joins on columns I
> don't ask for when I use the view but it doesn't, this query:

It doesn't, because it would be wrong. It still has to check that the
tables have a matching row (or multiple matching rows).

If you use LEFT JOINs instead, and have a unique index on all the ID
columns, then the planner can do what you expected and leave out the joins.

- Heikki

On 16/04/14 17:57, Heikki Linnakangas wrote:
> On 04/16/2014 06:13 PM, Linos wrote:
>> I thought that Postgresql would optimize out joins on columns I
>> don't ask for when I use the view but it doesn't, this query:
>
> It doesn't, because it would be wrong. It still has to check that the tables have a matching row (or multiple
matchingrows). 
>
> If you use LEFT JOINs instead, and have a unique index on all the ID columns, then the planner can do what you
expectedand leave out the joins. 
>
> - Heikki
>
>

You are right, I knew I was forgetting something important but I didn't know what was, thank you Heikki.

Regards,
Miguel Angel.