Jean-Michel POURE wrote: > > Le Lundi 18 Mars 2002 13:23, mlw a écrit : > > Lets face it, MySQL wins a lot of people because they put in features that > > people want. > > MySQL is very interested in benchmarks. > It does not really care for data consistency.
In no way am I suggesting we avoid ACID compliance. In no way am I suggesting that PostgreSQL change. All I am suggesting is that tables which change infrequently can and should be cached. select * from table where foo = 'bar' Need not be executed twice if the table has not changed. select * from table1, (select * from table2 where foo='bar' cacheable) as subset were subset.col1 = table1.col1; In the above query, if table two changes 4 times a day, and it queried a couple times a minute or second, the caching of the subset could save a huge amount of disk I/O. This sort of query could improve many catalog based implementations, from music, to movies, to books. A library could implement a SQL query for book lookups like this: select * from authors, (select * from books where genre = 'scifi' cacheable) as subset where authors.id = subset.auhorid and authors.id in (....) Yes it is arguable that index scans may work better, and obviously, summary tables may help, etc. but imagine a more complex join which produces fewer records, but is executed frequently. Caching could help the performance of PostgreSQL in some very real applications. MySQL's quest for benchmarking numbers, I agree, is shameful because they create numbers which are not really applicable in the real world. This time, however, I think they may be on to something. (1) PostgreSQL use a "cacheable" or "iscacheable" keyword. (2) If the query uses functions which are not marked as "iscacheable," then it is not cached. (3) If any table contained within the cacheable portion of the query is modified, the cache is marked as dirty. (4) No provisions are made to recreate the cache after an insert/update/delete. (5) The first query marked as "iscacheable" that encounters a "dirty" flag in a table, does an exhaustive search on the cache and removes all entries that are affected. As far as I can see, if the above parameters are used to define caching, it could improve performance on sites where a high number of transactions are made, where there is also a large amount of static data, i.e. a ecommerce site, library, etc. If the "iscacheable" keyword is not used, PostgreSQL will not incur any performance degradation. However, if he "iscacheable" keyword is used, the performance loss could very well be made up by the benefits of caching. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
