Dave Tolls wrote:Creating indexes, and getting the correct indexes on a table are how you handle large data sets.
By "registries" do you mean "rows"?
If so, then 27 million, especially if most of them are in tables that only have primitives (eg numbers) that were previously in your ARRAY columns, is not a great deal.
Those tables will be quick look ups, with the correct indexing.
Yes, I meant "rows". I usually use the term "registry" in my
Java EE applications to be as much generic as possible about the storage back-end that I use. Even though I always use a database. But yes, in terms of relational databases, the right term is "row". Sorry ;) .
I agree about the importance of using the appropriate indexes. But back in that time, when I made the switch from an
ARRAY column to actual rows, I had not much idea about indexes and the query planner. So it was horrible to see such a decrease of performance :S . Nowadays I always create the indexes that I need. But I admit that, from time to time, I wonder whether people use
ARRAY columns, and whether my decision was the right one. But by your words, it seems that that type of column isn't so common
.
I also take the opportunity to say that sometimes, at least in PostgreSQL, indexes are not enough. Because the query planner also takes into account some configuration parameters, and it may decide not to use your index if it considers that it will be more expensive that a sequence scan. Indeed, yesterday I experienced one of these situations. I almost become crazy, he he he. It turned out to be because of the value of the "random_page_cost" parameter, which was set to 4. The expected time cost calculated by the planner was not accurate at all. I did some tests using the
EXPLAIN command and forcing the use of index scans, and the planner happened to be completely wrong. By a width margin. So I decreased that parameter to 2 and the the situation improved a lot. Obviously, all these matters depend on the hardware and OS of each machine. It's an art... From which I still have to learn a lot.