Re: Best way to "and" from a one-to-many joined table?

I guess it depends on the optimiser and how clever it is. With the
former the db will probably generate 2 sets of ids for the 2 joined
tables (a, b) which only contain the values you require, these lists are
probably much smaller than the total number of rows in the table
therefore any merges and sorts on them have to operate on less rows and
will be quicker. With the latter query it has to fetch all the rows
regardless of the attribute and then do the restriction at the end,
which results in more rows, bigger merges and sorts and takes longer...
Obviously postgres may be clever enough to realise what you want and
rearrange the query internally to a more efficient form.

Generally to find out what it is doing stick "EXPLAIN (ANALYZE)" in
front. This will show you the steps the db is taking to perform the
query and in what order.
If you include ANAYLZE then the db actually does the query (throwing
away the results) and gives you accurate values, etc otherwise it shows
you estimated values based on the various stats collected for the table.

>> SELECT person_name
>> FROM test_people p
>> JOIN test_attributes a
>> ON ((a.people_id = p.people_id) AND (a."attribute" = @firstAttr))
>> JOIN test_attributes b
>> ON ((b."people_id" = p."people_id") AND (b."attribute" = @secondAttr));
>
> Hi,
>
> I saw a few people post answers to this question and it raised another
> related question for me.
>
> What are the differences between the above query and this one. Are
> they semantically/functionally identical but might differ in
> performance? Or would they be optimized down to an identical query? Or
> am I misreading them and they are actually different?
>
> SELECT person_name
> FROM test_people p
> JOIN test_attributes a
> ON ((a.people_id = p.people_id)
> JOIN test_attributes b
> ON ((b."people_id" = p."people_id")
> WHERE
> (a."attribute" = @firstAttr))
> AND (b."attribute" = @secondAttr));
>
> Also, any suggestions about how to figure out this on my own without
> bugging the list in the future would be great. Thanks for any insight!
>
> Steve
>
> p.s. I posting in the same thread, but if you think I should have
> started a new thread let me know for the future.
>