Database performance: filtering on column vs. separate table
database, database-design, performance, postgresql
Solution
Or should I create another table, e.g. 'Orders_Archive', so that the Orders table would only contain open orders that I use for the calculations?
Yes. They call that data warehousing. Folks do this because it speeds up the transaction system to eliminate the hardly-used history. First, tables are physically smaller and process faster. Second, a long-running history report doesn't interfere with transactional processing.
Is there any (clear) performance difference in these approaches?
Yes. Bonus. You can restructure your history so that it's no longer in 3NF (for updating) but in a Star Schema (for reporting). The advantages are huge.
Buy Kimball's The Data Warehouse Toolkit book to learn more about star schema design and migrating history out of active tables into warehouse tables.
Problem
I was wondering what the best approach would be for the following situation: I have an Orders table in a database that obviously contains all orders. But these are literally ALL orders, so including the complete/finished ones that are just flagged as 'complete'. From all the open orders I want to calculate some stuff (like open amount, open items, etc). What would be better performance wise: Keep 1 Orders table with ALL orders, including the complete/archived ones, and do calculations by filtering the 'complete' flag? Or should I create another table, e.g. 'Orders_Archive', so that the Orders table would only contain open orders that I use for the calculations? Is there any (clear) performance difference in these approaches? (B.T.W. I'm on a PostgreSQL db.)