When you think of a database you very naturally think in terms of rows and columns, so a column store is just a different way of saying the same thing… right?… well kind-of… you still access the data in the same ways by asking the same kinds of questions, but the physical layout is quite different. When thinking about a column store think more about the physical layout of the data than the question being asked.
To over simply consider my example of a table that contains 3 columns. If you wanted to find out if there is an entry with a name of “date” (lets assume there are no index’s etc… clearly there are ways to optimize row-scans… particularly if there are only 3 columns)
The scan would start by reading the first row into memory (including the columns you are not interested in) to compare the value for table… it is not a match, so the next row is read, followed by the third row, and then the fourth where there is a match. Considering a wide row, you have moved quite a bit of data into memory reading a considerable number of blocks, just to compare on a single value.
Consider the alternative with a column store, the data is physically stored by column, so when you want to ask the same question to see if there is a table entry with the name of “date”
The scan starts with the table column ONLY, and checks row 1, 2, 3 and 4 until the value “date” is found. Again, with a wide table, you now have the data you are looking for, and have ONLY moved into memory the values that were needed in the comparison. Considering how much data can be read with a single read (per block) particularly on a wide table with many rows a, columns store can be very effective.