4 ms·
a) Clustered and covering indexes. b) Non-transactional tables Both allow you optimize the memory usage of some particularly problematic cases, specifically v
by fauigerzigerk 15y ago
a) Clustered and covering indexes.
b) Non-transactional tables
Both allow you optimize the memory usage of some particularly problematic cases, specifically very large, simple tables. Postgres cannot return data directly from indexes (covering indexes). It always has to go back to the table itself to fetch the actual data. If the table is large, that can be inefficient for some types of queries.
Non-transactional tables use a lot less memory as well. For instance, if you have a large table that represents a N:M relationship (id1 int, id2 int), the two ints use 8 bytes of memory. A postgres table adds about 24 bytes per record, three times the actual data, plus some overhead per page.
Don't take this to mean that MySQL is faster than Postgres. That's not generally the case. The Postgres query optimizer is vastly better than MySQL's. So for complex queries and data models, Postgres is way superior. The big differences are always related to very specific data model and query combinations, so general benchmarks are utterly useless.
- caf 15y agoUnlogged tables are now in Postgres as of release 9.1: http://www.postgresql.org/docs/9.1/static/release-9-1 http://www.postgresql.org/docs/9.1/static/release-9-1
- fauigerzigerk 15y agoThat's very good news! I have to look at the physical data structures and what it means for memory usage though. "Unlogged" as such only means they don't use WAL.
- masklinn 15y ago> b) Non-transactional tables 9.1 added unlogged tables. They are completely unsafe, and quite fast.
- sheff 15y agoThey are not available in Postgres at the moment, but I believe that covering indexes / index only scans will be coming some time next year, as theres a patch that should hopefully go into the next release. http://rhaas.blogspot.com/2011/08/index-only-scans-now-theres-patch.html#more http://rhaas.blogspot.com/2011/08/index-only-scans-now-there... http://archives.postgresql.org/pgsql-hackers/2011-08/msg00736.php http://archives.postgresql.org/pgsql-hackers/2011-08/msg0073...