I have heard that having loops in the database relational model should be avoided.
To me, a table structure (>1 table) where the foreign keys form circular dependencies (traversing foreign keys starting from one table, it's possible to end up back at the same table) is a red flag indicating a likely problem with the table/system design.
I'm not going to say this is never correct, but I have yet to see an example where this kind of thing was used correctly, or could not be reasonably redesigned to avoid the use of a circular dependency.
(It's the same type of issue in other areas too, as I pointed out here.)
Table 'Answers' is linked to 'QuestionaireResultsets',but 'QuestionaireResultsets' is also linked to 'Questionnaires', which forms a loop.
Those tables are related, yes, but the relationships probably should be of the form:
Questionnaires 1..* QuestionaireResultsets
QuestionaireResultsets *..* Answers, with a join table in between
These changes result in a structure that does not contain a circular dependency.
Remember that foreign key relationships have a direction. This is very important. Tables can be related such that a schema diagram looks like a circle; it's only a problem when all the segments of the circle point in the same direction.
I'm wondering if it affects the query plans and SQL Server performance.
If you ask SQL Server to perform a task using recursion instead of using sets or iteration, it's possible that performance could suffer; however, the reverse may be true as well. I'm not sure we can have a meaningful discussion about performance in this case without a specific example.
The problem appears to be in this part of the code:
JOIN category_link l on l.sku_id IN (SELECT value FROM #Ids) AND
(
l.category_id = c4.category_id OR
l.category_id = c5.category_id
)
or in join conditions is always suspicious. One suggestion is to split this into two joins:
JOIN category_link l1 on l1.sku_id in (SELECT value FROM #Ids) and l1.category_id = cr.category_id
left outer join
category_link l1 on l2.sku_id in (SELECT value FROM #Ids) and l2.category_id = cr.category_id
You then have to modify the rest of the query to handle this . . . coalesce(l1.sku_id, l2.sku_id) for instance in the select clause.
Best Answer
Using debugging parameters like
enable_nestloopshould not change the results of a query. If it does that'd generally be a bug.However, you should not use
enable_nestloop = offor similar in production. It's a very big hammer, and while it might appear to fix your immediate problem, it'll create performance problems elsewhere. A nested loop is often the correct plan node for best performance in a part of a query, and forcing PostgreSQL to prefer other options might make queries significantly slower.Instead, you should generally adjust the cost parameters like
random_page_costto encourge PostgreSQL to choose a plan that performs better on your hardware and workload. For more information see the tuning advice on the PostgreSQl wiki and in the main documentation.