It is mentioned in "Database System Implementation" , section 7.2.4 that when we push projections, it is quite usual for the projection to also remain where it is. Can there be situations where when we push projections, we need not retain the projection at the same place and yet obtain an equivalent logical query plan . For example if we have a relation A with attributes p,q,r and we have the following logical query plan :

Projection(p,q) (Selection(q=3)(A))

Would Selection(q=3) (Projection(p,q) (A)) be a equivalent logical query plan for the above logical query plan? i have pushed the projection down the tree without retaining the projection in its original place since ALL the attributes required by the selection are also included in the projection. Is this correct?

Yes, your example is correct. In other examples the projection might eliminate some attribute required by another operator - I guess that's what your textbook is referring to.

In SQL systems (which are not closed under the relational algebra) the picture is also complicated because SQL has a "SELECT ALL" version of the "projection" operator that preserves duplicate rows. Query rewrite is bound to be a harder problem in SQL systems than in truly relational ones because of its multi-set rather than set-based model. So that may also be a consideration with projections in SQL systems.

That was helpful. Thank you for your response. How would the equivalence of above 2 logical query plans be affected if we were dealing with multi-set rather than the set model? In other words, would the above 2 logical query plans NOT be equivalent if we were dealing with the multiset model? I am thinking they would still be equivalent.

In your example the two plans are the same under the multiset model.