2009/10/11 Scott Marlowe <scott.marlowe@xxxxxxxxx>: >> The postgresql database uses the table full scan.but in oracle, the similar SQL uses the index full scanning,speed quickly many than postgresql. > > Yep, PostgreSQL isn't Oracle. It's a trade off. In pgsql indexes > don't contain visibility info, so all index lookups have to eventually > hit the table itself. So you either do indexlookup -> table lookup, > repeat as many times as you have index lookups or you just hit the > table since you gotta go there anyway. > > On the bright side, this makes updates faster since you don't have to > lock both table and index and write to both at the same time anymore. > >> postgresql's optimizer whether to have the necessity to make the adjustment? > > Sorry, it's an architectural difference. Are you testing in a > realistic scenario including both reads and writes to the database to > see if postgresql is faster overall and identify problem areas that > pop up there? > This is interesting, I just ran a similar issue the other day. Clearly there is a wide range of read / write scenarios that Postgres should be able to cover. These days, I have a lot of designs leaning more toward the data warehouse side of the operational spectrum as opposed to the high transaction scenario and I specifically design DB management strategies with the knowledge that writes will happen far less than reads in our applications. Is this an area where optimizations are considered hard in Postrgres or hopefully, just something that is on the todo list but just no one has gotten around to yet? Similarly, are accurate table summary stats possible someday or are they considered close to impossible in order to eliminate race conditions and lock contention scenarios? -- Peter Hunsberger -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
