pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedunneeded joins on view
3+ messages / 2 participants
[nested] [flat]
* unneeded joins on view
@ 2014-04-16 15:13 Linos <info@linos.es>
2014-04-16 15:57 ` Re: unneeded joins on view Heikki Linnakangas <hlinnakangas@vmware.com>
0 siblings, 1 reply; 3+ messages in thread
From: Linos @ 2014-04-16 15:13 UTC (permalink / raw)
To: pgsql-performance
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 my items 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 planner join the unneeded tables.
Is possible to tell Postgresql do the right thing? If so, how? Thanks!
Regards,
Miguel Angel.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: unneeded joins on view
2014-04-16 15:13 unneeded joins on view Linos <info@linos.es>
@ 2014-04-16 15:57 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2014-04-16 16:08 ` Re: unneeded joins on view Linos <info@linos.es>
0 siblings, 1 reply; 3+ messages in thread
From: Heikki Linnakangas @ 2014-04-16 15:57 UTC (permalink / raw)
To: Linos <info@linos.es>; pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: unneeded joins on view
2014-04-16 15:13 unneeded joins on view Linos <info@linos.es>
2014-04-16 15:57 ` Re: unneeded joins on view Heikki Linnakangas <hlinnakangas@vmware.com>
@ 2014-04-16 16:08 ` Linos <info@linos.es>
0 siblings, 0 replies; 3+ messages in thread
From: Linos @ 2014-04-16 16:08 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnakangas@vmware.com>; pgsql-performance
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 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
>
>
You are right, I knew I was forgetting something important but I didn't know what was, thank you Heikki.
Regards,
Miguel Angel.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-04-16 16:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-16 15:13 unneeded joins on view Linos <info@linos.es>
2014-04-16 15:57 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2014-04-16 16:08 ` Linos <info@linos.es>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox