Consider a simple query:
Select username,usertype from Table1 Where usertype='Manager'
Most probably the DBMS will be performing selection first, and then projection i.e. first pulling out rows form Table1 having usertype='Manager'
Is there a case where performing projects first, and then selection would be more optimal or better in any means?
Not with row-oriented storage, where the column values are stored together per rows, which is not ideal for starting with projection.
Lets say Table1 has 30 columns, but you need data only from 2 (username, usertype). If username and usertype columns are not indexed, the DBMS has to read the full table with all rows (thus read all columns). In this case the only order to do this, is selection, then projection.
If username and usertype are part of a composite index, the DBMS will decide to get the data from this index instead of reading the whole table itself. The index will have most likely less columns than the table, but it is still not the projection you are looking for. Indexes are still organized for row-oriented storage, first selection happens based on usertype='Manager', then the projection.
However, there are some other DBMSs, that use column-oriented storage. Column-oriented DBMS. In these systems, whole columns are stored after each other, which makes it really easy to start with the projection. If you need only 2 columns from 30, the database can just simply read that 2 columns, and do the selection.This kind of system performs better, when running low selectivity queries, that want to access most, or all the rows from a table. Overall performance depends on the kind of workload.
