Re: --EXTERNAL--Re: PSQL does not remove obvious useless joins

OK. Will change our query generation code to not use the view.
(I have tried the LEFT JOIN approach, but it just does not seem to perform.)

Thanks,
Igor

PS: Here are the numbers for the real production query (will not provide details):
Original query: 300s
Query on a manually optimized view: 1ms
Using left joins: 200s

I would have gladly paid a few ms in additional planning time!

-----Original Message-----
From: Kevin Grittner [mailto:kgrittn(at)gmail(dot)com]
Sent: Friday, July 01, 2016 1:57 PM
To: Sfiligoi, Igor <Igor(dot)Sfiligoi(at)ga(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious useless joins

On Fri, Jul 1, 2016 at 3:33 PM, Sfiligoi, Igor <Igor(dot)Sfiligoi(at)ga(dot)com> wrote:
> No, I don't want to use LEFT JOINS.
> I want to use regular joins.
>
> But (as mentioned in my other follow-up), all the fields are not null
> (was not in the original email, sorry), and are foreign keys, so it is
> guaranteed to always match.

In that case there is no difference between the inner join and the left join except that the left join currently supports and optimization that makes your query faster if the optional table is not reference. Whether you want to take advantage of that is up to you.

> The key part (in my mind) is that I am not filtering on any of the
> useless tables, and I am not returning any columns from those tables
> either.
> Both is known at planning time.

The fact that something can be determined at planning time doesn't mean that checking for it is free.

> is my logic still broken?

Your logic seems OK with the table definitions you are now showing.

Whether we ever decide it is OK to omit tables which use an inner join rather than only considering omitting them when the query specifies that the join is optional is anybody's guess. If it is important enough to you you could submit a patch or fund development of such a feature; but since it would add at least some small amount of planning time to every inner join just to avoid specifying that the join is an optional one when writing the query, it seems to me unlikely to be accepted.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company