On Mon, 8 Apr 2019 19:21:37 +0530
Arup Rakshit <ar@zeit.io> wrote:
> Hi,
>
> Thanks for showing different ways to achieve the goal. So what should
> be the optimal way to solve this. I have an composite index using
> company_id and feature_id columns for project_features table.
there are even more ways for that simple task. i can imagine some fancy
ways including lateral joins, cte returning subset of company_features
to produce positive results and be reused to produce set difference for
negative results, etc. but too fancy isn't good. the simpler the better.
those already mentioned should be enough. since you need all features
and all company_features for a given company id, there won't be any
much better.
it is enough to limit company_features to company_id which we already
do in join condition, and for big tables optimizer could use your index.
we can probably assume there won't be so much companies and so much
features to make really big table of three ids tuples to make optimizer
even consider using an index, but it may be good habit to think how we
could help optimizer to filter out unnecessary data sooner than later.
regards, mariusz
> I do ruby on rails development, where table names are plural always
> by convention. The tables I created above in different schema to ask
> question with sample data and test the query output. So they are
> little inconsistent, yes you are right.
>
> Thanks,
>
> Arup Rakshit
> ar@zeit.io
>
>
>
> > On 08-Apr-2019, at 4:36 PM, mariusz <marius@mtvk.pl> wrote:
> >
> > bear in mind that with a large number of companies and proper index
> > on company_features the optimizer could limit company_features as
> > necessary, while your examples read everything anyway and mangle
> > output to get proper result (with dups and bugs, but also not
> > optimal)
>
>
>